SQL Server

همانند هر محیط برنامه سازی دیگر، هر کوئری که به SQL Server ارسال میشود باید ابتدا تحلیل شود و سپس کامپایل گردیده تا بتواند اجرا شود. اگر از بالا نگاه کنیم، این موارد شامل بررسی سینتکس و بررسی اشیاء داخل کوئری میشود. پس از انجام این مرحله SQL Server دارای ساختاری است قابل فهم و میتواند برای بازیابی داده های درخواستی مورد استفاده قرار گیرد. برای موثرتر بودن، SQL Server این ساختار را در حافظه نگهداری میکند تا بتواند از آن مجددا استفاده نماید. بنابراین دفعه بعدی که این کوئری ارسال گردید، SQL Server میتواند از مراحل تحلیل و کامپایل بگذرد.

بعنوان مثال به کد زیر دقت کنید:

SET STATISTICS TIME ON
SET STATISTICS IO OFF
USE AdventureWorks
go
DBCC FREEPROCCACHE
go
SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, C.Phone, E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = 'Miller'

نکته : استفاده از دستور DBCC FREEPROCCACHE در سستم های تولیدی توصیه نمیشود. این کار میتواند منجر به این گردد که SQL Server تا زمانی که کش رویه ها بتواند بازسازی شود به کندی عمل کند. دلیل استفاده از این دستور در اینجا این است که نشان دهیم چگونه SQL Server از کش رویه ها برای ذخیره سازی و استفاده مجدد از عبارات تک منظوره استفاده مینماید.

چنانچه شما قسمت پیغامها ( Messages ) را در پانل مدیریت SQL Server نگاه کنید، شما چنین چیزی را خواهید دید:

SQL Server parse and compile time:
CPU time = 78 ms, elapsed time = 106 ms.
(1 row(s) affected)

در این حالت قبل از اینکه هر اتفاق دیگری بیفتد شما زمان لازم برای تحلیل و کامپایل برنامه را مشاهده کرده اید. این زمان مسلما متغیر خواهد بود. کامپیوتری که من از آن برای این کار استفاده کرده ام پردازنده AMD Athlon 64 x2 4200+ و 2 گیگابایت حافظه داشته است. ضمن اینکه از SQL Server 2005 و windows XP استفاده کرده ام.

اگر شما فقط عبارت SELECT را اجرا نمایید شاهد چنین چیزی خواهید بود:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 9 ms.
(1 row(s) affected)

دلیل اینکه بار دوم برای تحلیل برنامه هیچ زمانی از پردازنده گرفته نشدن این است که بار دوم عبارت از کش بازیابی شده است. برای مشاهده عملی این مورد به این کد که به قصد ردیابی یا trace نوشته شده است دقت کنید:

/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 12/30/2006 10:01:02 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
-- extension will be appended to the filename automatically. If you are
-- writing from remote server to local drive, please use UNC path and make
-- sure server has write access to your network share
exec @rc = sp_trace_create @TraceID output,0,N'C:\ProcedureCache',
@maxfilesize,NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 21, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 35, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 35, @on
exec sp_trace_setevent @TraceID, 35, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

زمانی که شما این اسکریپت را اجرا میکنید، اطمینان حاصل کنید که نتیجه TraceID را در جایی ذخیره میکنید. چرا که بعدا شما باید توسط دستور زیر اجرای اسکریپت را متوقف کنید:

--stop trace job
exec sp_trace_setstatus {PUT TRACEID HERE} , 0

پس از این که این عمل ردیابی را ایجاد کردید، دو مرحله اول را بار دیگر انجام دهید. سپس فایل ردیابی خود را باز کنید(به طور پیش فرض C:\ProcedureCache.trc ) و شما رخدادهای ردیابی متعددی خواهید یافت.

در میان آنها شما میتوانید یک رخداد به نام SP:CacheInsert برای عبارت SELECT با یک ورودی برای رخداد SP:CacheHit بیابید.اگر دقت کنید متوجه خواهید شد که همه این چیزها با آنچه قبلا گفته شد مطابقت دارد.

SQL تک منظوره

SQL تک منظوره برای بسیاری از برنامه نویسان آشنا و انعطاف پذیرتر است تا نوشتن رویه های ذخیره شده. چرا که به برنامه نویسان امکان میدهد به سادگی یک رشته حاوی دستور مورد نظر خود بسازند و آن را به SQL Server ارسال کنند. یک نمونه از این برنامه را در زیر میبینید:

SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, C.Phone, E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = 'Smith'

چنانچه شما این کد را اجرا کنید و به زمان تحلیل و کامپایل دقت کنید، خواهید دید که عبارت مجددا کامپایل میشود.همچنین چنانچه همچنان در حال اجرای ردیابی باشید، خواهید دید که این عبارت به جای آنکه مجددا مورد استفاده قرار گیرد، هر بار به کش رویه ها اضافه می‌شود. این بدان معناست که تنها زمانی که کوئری دقیقا با قبلی یکسان نباشد، SQL Server کوئری را مجددا تحلیل و کامپایل میکند.

در برنامه های کوچک با پایگاه داده های کوچک معمولا به این مورد چندان توجهی نمیشود اما در یک سیستم بزرگ با ترافیک بالا،این کار سبب میشود کش رویه‌های شما سربار زیادی را تحمل کند.یکی از عوارض جانبی این کار میتواند این باشد که داده های درون کش بافر بیرون رانده شود و در نتیجه به جای آنکه حافظه بازیابی شوند از روی حافظه جانبی خوانده شوند و این کار درنهایت به گلوگاهی برای IO و ناکارمدی سیستم شما منجر میگردد.

چندین سال پیش ما یک برنامه asp.net برای مدیریت تبلیغات خریداری کردیم. در آن لحظه اندازه پایگاه داده ها کمتر از 20 مگابایت بود. اما به دلیل ترافیک بالا ( 100 تا 200 دستور در ثانیه) توانست یک سرور Xeon با 2 گیگابایت حافظه به زانو درآورد. زمانی که مساله را دقیق تر بررسی کردیم متوجه شدیم که SQL Server مجبور شده است همه 2 گیگابایت حافظه این سرور را به همراه 100 درصد زمان پردازنده را اشغال کند.

در زیر یک مثال از این که کوئری ما چگونه ممکن است از دید یک کلاینت .net به نظر برسد ارائه داده ايم:

string eName = "Smith";
DataSet data = new DataSet();
using(SQLConnection conn = new SQLConnection(
"Application Name=AdHocClient;Server=(local);" +
"Database=AdventureWorks;Trusted_Connection=Yes;"))
{
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress,");
SQL.Append("C.Phone, E.*");
SQL.Append(" FROM Person.Contact C");
SQL.Append(" INNER JOIN HumanResources.Employee E ");
SQL.Append(" ON E.ContactID = C.ContactID");
SQL.AppendFormat(" WHERE C.LastName = '{0}'", eName);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = SQL.ToString();
cmd.CommandType = CommandType.Text;
try
{
using(SQLDataAdapter da = new SQLDataAdapter(cmd))
{
da.Fill(data);
}
}
catch(SQLException ex)
{
//.... execption code .....
}
}

متغیرeName بیانگر مقدار گرفته شده و از ورودی کاربر است. هر بار که عبارتی از کاربر دریافت میشود، کوئری تغییر کرده و بنابراین مجددا کامپایل میشود. اما ببینیم چگونه متیوانیم این مشکل را برطرف کنیم؟

SQL پارامتری

بدلایل امنیتی و بازدهی، همواره استفاده از رویه های ذخیره شده (Stored Procedure) برای دسترسی به داده ها پیشنهاد میشوند. اما چنانچه برنامه خود را نوشته اید و زمان لازم برای انتقال کلیه دستورات به این رویه ها را ندارید، استفاده از پارامترها در SQL توصیه میشود. زمانی که شما از این پارامترها استفاده میکند، کوئری شما همواره یکسان خواهد بود، بدون توجه به اینکه مقادیر آن پارامترها چه هستند. بنابراین اگر بخواهیم دستورات بالا را بازنویسی کنیم چنین کدی خواهیم داشت:

string eName = "Smith";
DataSet data = new DataSet();
using(SQLConnection conn = new SQLConnection(
"Application Name=ParameterizedClient;Server=(local);" +
"Database=AdventureWorks;Trusted_Connection=Yes;"))
{
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, ");
SQL.Append("C.Phone, E.*");
SQL.Append(" FROM Person.Contact C");
SQL.Append(" INNER JOIN HumanResources.Employee E ");
SQL.Append(" ON E.ContactID = C.ContactID");
//replace contatenation of variable with parameter name
SQL.Append(" WHERE C.LastName = @LastName");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = SQL.ToString();
cmd.CommandType = CommandType.Text;
//Define SQLParameter object and assign value to the parameter
cmd.Parameters.Add("@LastName", SQLDbType.VarChar, 50);
cmd.Parameters["@LastName"].Value = eName;
try
{
using(SQLDataAdapter da = new SQLDataAdapter(cmd))
{
da.Fill(data);
}
}
catch(SQLException ex)
{
//.... execption code .....
}
}

اکنون زمانی که کلاینت شما این برنامه را اجرا میکند، SQL Server همواره از نسخه کامپایل شده که در کش رویه ها قرار دارد استفاده میکند و همین باعث میشود که معایبی را که قبلا گفتیم برای این برنامه نداشته باشیم و در نتیجه بازدهی و سرعت بالاتری داشته باشیم.

SqlCommand.Prepare()

ممکن است این سوال مطرح شود که استفاده از متد Prepare() از شیء SqlCommand تاثیری خواهد داشت؟ در این پروژه و با توجه به موضوع این مقاله پاسخ منفی است. متد Prepare() باعث فراخوانی شدن رویه ذخیره شده sp_prepexec میشود. این کار باعث میشود تا یک رویه ذخیره شده موقتی برای جلسه ( session) فعلی ایجاد شود و در زمان بسته شدن ارتباط فعلی از بین برود. این به کوئری اجازه خواهد داد تا زمانی که ارتباط باز است بعنوان یک رویه ذخیره شده مورد استفاده قرار گیرد.

استفاده از این متد، کاهش ترافیک شبکه است چراکه تمام متن دستور که در برنامه کلاینت تعریف شده است تنها یکبار از طریق شبکه پخش میشود. اولین باری که دستور پس از فراخوانی متد Prepare() اجرا شود، چیزی شبیه به این به نظر خواهد رسید:

declare @p1 int
set @p1=10001
exec sp_prepexec @p1 output,N'SELECT C.Title, C.FirstName, C.LastName,
C.EmailAddress, C.Phone,E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = @LastName
',@EmployeeID=15073
select @p1

توجه داشته باشید که چگونه @p1 تعریف شده بعنوان یک مقدار اسکالر اعلان شده است.پس از آن شیء SqlCommand میتواند دستور زیر را در درخواستهای بعدی بفرستد:

پروژه نمونه

پروژه نمونه ضمیمه شده این مقاله، یک رویه ذخیره شده از پایگاه داده AdventureWorks را فراخوانی کرده و تمامي متن این رویه را به عنوان متن به یک شیء دستور یا Commad ارسال میکند تا یک کوئری پیچیده تر را شبیه سازی کند. دو نسخه از این کوئری موجود است.یک نسخه تک منظوره و دیگری نسخه پارامتری. هر کدام 10000 بار با پارامترهای اتفاقی اجرا شده اند . نتایج بدست آمده چنین بوده است:

همانگونه که میبینید، بدون پارامترها زمان فرآیند 10 برابر طولانی تر خواهد بود، زمان پردازنده 2 برابر بيشتر اشغال خواهد شد و صفحه های مورد اسفاده توسط کش رویه ها 200 برابر بیشتر خواهد بود که این تعداد با گذشت زمان بسیار بدتر نیز خواهد شد. در نهایت اینگونه بگوییم که کوئری های تک منطوره ها باعث میشوند هربار که پارامتر جدیدی داشتیم، كوئري مورد نظر ما یک بار دیگر به کش رویه ها اضافه شود و همین کار مشکلات فراوانی ایجاد میکند.
منبع : codeproject.com
مترجم: علیرضا شیرازی