حالت موضوعی | حالت خطی

***ali***

سلام. در این مطلب میخوام یک تجربه شخصی در مورد نحوه نوشتن پس و جو (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]

لینک دانلود فایل اکسل