انجمن وب سایت مشاوره در زمینه پروژه های برنامه نویسی و طراحی وب سایتهای تجاری
تاثیر نحوه نوشتن پرس و جو بر سرعت اجرای آن در SQL Server - نسخه‌ی قابل چاپ

+- انجمن وب سایت مشاوره در زمینه پروژه های برنامه نویسی و طراحی وب سایتهای تجاری (http://forum.a00b.com)
+-- انجمن: سوالها و مقاله های آموزشی (/forumdisplay.php?fid=1)
+--- انجمن: مقاله های آموزشی (/forumdisplay.php?fid=3)
+--- موضوع: تاثیر نحوه نوشتن پرس و جو بر سرعت اجرای آن در SQL Server (/showthread.php?tid=21)

تاثیر نحوه نوشتن پرس و جو بر سرعت اجرای آن در SQL Server - ali - 06-20-2014 01:05 PM

سلام. در این مطلب میخوام یک تجربه شخصی در مورد نحوه نوشتن پس و جو (Query) و تاثیر آن بر سرعت اجرا رو براتون توضیح بدم. این نکته رو هم خدمتتون عرض کنم که بهترین روش توی هر کاری اینه که مقاله ها و دست آوردهای جدید رو بخونین تا بتونین به بهترین نحوی کارهاتون رو انجام بدین. این ترفند بیشتر در رکوردهای تعداد بالا به درد می خوره. چون توی رکوردهای زیر هزار تا سرعت زیاد تفاوتی نمی کنه ولی در رکوردهای بالای ده هزار تا معمولا اگر روش مناسبی برای پرس و جو نوشتن انتخاب نکنین حتما منابع سیستم شما (Server) احتمالا زیاد از حد مشغول میشه.
فرض می کنیم سه جدول با عنوانهای زیر داریم:
  • tblPrjDescriptions
  • tblProjTypes
  • tblReshteh
در ابتدا پرس و جویی می نویسیم که با استفاده از انواع Join ها ارتباط بین جدولها ایجاد شود:
کد:
DECLARE @start_time DATETIME
Declare @MCounter int
Declare @MTimeCount int
SET @MCounter = 0
SET @MTimeCount = 0
WHILE (@MCounter < 20)
BEGIN
SET @start_time = GETDATE()
SELECT     tblPrjDescriptions.ProjId, tblPrjDescriptions.ProjTitle, tblProjTypes.PrjName, tblReshteh.ReshteTahsili
FROM         tblPrjDescriptions INNER JOIN
                      tblProjTypes ON tblPrjDescriptions.ProjGroupID = tblProjTypes.RowID INNER JOIN
                      tblReshteh ON tblPrjDescriptions.ReshTeh = tblReshteh.RowID
SELECT RTRIM(CAST(DATEDIFF(MS, @start_time, GETDATE()) AS CHAR(10))) AS 'TimeTaken'
SET @MTimeCount = @MTimeCount + (SELECT CAST(DATEDIFF(MS, @start_time, GETDATE()) AS int))
SET @MCounter = @MCounter + 1
END
SELECT (@MTimeCount/20) as MainAvg
GO
با این حلقه 20 بار اسکریپت مورد نظر را اجرا می نمائیم. نتیجه میانگین به دست آمده عدد 96 میلی ثانیه برای 5200 رکورد می باشد.
در روش دوم بدون استفاده از join ها ارتباط بین جدول ها را ایجاد می نمائیم. در حقیقت در این روش بدون ارتباط فیزیکی بین جدولها ، رکوردهای مرتبط در سایر جدولها را فراخوانی می نمائیم. لازم به ذکر است که نوشتن پرس و جو در روش دوم کمی نیاز به مهارت دارد. روش دوم زیاد پیشنهاد نمی شود. ولی در بعضی مواقع می تواند نجات دهنده باشد. دقت در روش دوم بسیار بیشتر است.

کد:
DECLARE @start_time DATETIME
Declare @MCounter int
Declare @MTimeCount int
SET @MCounter = 0
SET @MTimeCount = 0
WHILE (@MCounter < 20)
BEGIN
SET @start_time = GETDATE()
select tt.ProjId , tt.ProjTitle ,
(select tr.ReshteTahsili from tblReshteh tr where tr.RowID = tt.ReshTeh) as Reshteh ,
(select tp.PrjName from tblProjTypes tp where tp.RowID = tt.ProjGroupID) as ProjGroup
from tblPrjDescriptions tt
SET @MTimeCount = @MTimeCount + (SELECT CAST(DATEDIFF(MS, @start_time, GETDATE()) AS int))
SET @MCounter = @MCounter + 1
END
SELECT (@MTimeCount/20) as MainAvg
GO
در این روش نتیجه بدست آمده برای 5200 رکورد میانگین 106 میلی ثانیه را نمایش می دهد. البته این در مورد تعداد دفعات تکرار 20 می باشد. نتیجه جالبی که پس از این آزمایش به آن رسیدم این بود که اگر تعداد دفعات تکرار را زیاد کنم روش دوم سریعتر از روش اول انجام می شود.
روش اول برای 5200 رکورد و 100 تکرار میانگین به دست آمده 183 میلی ثانیه
روش دوم برای 5200 رکورد و 100 تکرار میانگین به دست آمده 109 میلی ثانیه
نتیجه بدست آمده نشان می دهد که روش دوم منابع SQL Server را کمتر از روش اول (اسفاده از Join ها) درگیر می نماید. بنابراین در محلی که تعداد تراکنشها بیشتر است بهتر است از روش دوم استفاده شود ولی در تعداد تراکنشهای پایین روش اول مفیدتر است.
[تصویر: 6353890750882693500SQLScript.jpg]
برای اجرای اسکرسپتها هم می تونین پس از اجرای SQL Server Management Studio بر روی New Query کلیک بفرمائین و کوئریهای خودتون رو تست و اجرا بفرمائین.
در آزمایش دیگری که بر روی یک سیستم نسبتا قوی با پردازنده و RAM بالا انجام شد نتیجه کماکان به همان شرح بود. من نتایج رو درون یک فایل اکسل براتون گذاشتم. نمودار تراکنشها وقتی که تعداد رکوردها 100000 و تعداد واکشی حدودا 500 بار بود به شرح عکس می باشد. البته اعداد مربوط به آزمایشها داخل فایل اکسل موجود است و نمودار رو هم می توانید آنجا ملاحظه بفمرائین. هر چه تعداد رکوردها و تعداد واکشی ها بیشتر شود کارآیی Join ها کمتر می شود.

[تصویر: 6353896958722964641SQL_DataS.JPG]
لینک دانلود فایل اکسل