افزایش سرعت کوئری‌های SELECT در SQL Server

افزایش سرعت کوئری‌های SELECT در SQL Server: راهنمای جامع بهینه‌سازی

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

۱. همیشه نام ستون‌ها را مشخص کنید؛ از SELECT * خودداری کنید

یکی از رایج‌ترین اشتباهات که باعث کاهش سرعت و افزایش بار روی شبکه می‌شود، استفاده از SELECT * است. این دستور، تمامی ستون‌های جدول را، حتی آن‌هایی که نیاز ندارید، بازیابی می‌کند. به جای آن، همیشه ستون‌های مورد نیاز خود را به وضوح ذکر کنید. این کار حجم داده‌های منتقل شده را کاهش داده و به بهینه‌سازی ایندکس‌ها کمک می‌کند.

SELECT [Column1], [Column2], [Column3] FROM [TableName];

۲. از wildcard در ابتدای عبارت LIKE پرهیز کنید

استفاده از LIKE '%keyword%' در عبارت WHERE، مانع از استفاده بهینه از ایندکس‌ها می‌شود، زیرا SQL Server مجبور به اسکن کامل جدول است. برای جستجوهای سریع‌تر، سعی کنید از wildcard در ابتدای عبارت خودداری کنید، یا در صورت لزوم، از Full-Text Search استفاده کنید.

-- کند: Full Table Scan
SELECT [Column1] FROM [TableName] WHERE [Column2] LIKE '%keyword%';

-- سریع‌تر (اگر ایندکس روی Column2 باشد): Index Seek
SELECT [Column1] FROM [TableName] WHERE [Column2] LIKE 'keyword%';

۳. از UNION ALL به جای UNION استفاده کنید

وقتی نیاز به حذف ردیف‌های تکراری ندارید، همیشه از UNION ALL استفاده کنید. UNION علاوه بر ترکیب نتایج، یک عملیات DISTINCT (حذف تکراری) را نیز انجام می‌دهد که بسیار زمان‌بر است. UNION ALL فقط نتایج را به هم پیوند می‌دهد و سریع‌تر عمل می‌کند.

SELECT [Column1] FROM [Table1]
UNION ALL
SELECT [Column1] FROM [Table2];

۴. از استفاده از OR در عبارت WHERE خودداری کنید

استفاده از OR در WHERE می‌تواند باعث ایجاد مشکلات عملکردی شود، به خصوص اگر ایندکس‌های مناسب وجود نداشته باشند یا شرایط OR شامل ستون‌های مختلفی باشد. در برخی موارد، می‌توانید آن را با UNION ALL یا IN جایگزین کنید.

-- کمتر بهینه
SELECT [Column1] FROM [TableName] WHERE [Column2] = 'Value1' OR [Column3] = 'Value2';

-- جایگزین بهینه (در صورت نیاز به ایندکس‌های مجزا یا عملکرد بهتر)
SELECT [Column1] FROM [TableName] WHERE [Column2] = 'Value1'
UNION ALL
SELECT [Column1] FROM [TableName] WHERE [Column3] = 'Value2' AND [Column2] <> 'Value1'; -- شرط برای جلوگیری از تکرار اگر مقادیر Column2 و Column3 می‌توانند همزمان درست باشند.

۵. EXISTS را به جای IN در زیرکوئری‌ها به کار ببرید

برای بررسی وجود یک مقدار در یک زیرکوئری، EXISTS معمولاً عملکرد بهتری نسبت به IN دارد، به خصوص زمانی که زیرکوئری تعداد زیادی ردیف برمی‌گرداند. EXISTS به محض یافتن اولین ردیف متوقف می‌شود، در حالی که IN ممکن است نیاز به پردازش کامل زیرکوئری داشته باشد.

-- استفاده از IN
SELECT [Column1] FROM [Table1] WHERE [Column2] IN (SELECT [Column3] FROM [Table2] WHERE [Condition]);

-- استفاده از EXISTS (معمولا سریع‌تر)
SELECT [Column1] FROM [Table1] WHERE EXISTS (SELECT 1 FROM [Table2] WHERE [Table1].[Column2] = [Table2].[Column3] AND [Condition]);

۶. از HAVING و GROUP BY بی‌رویه اجتناب کنید

GROUP BY و HAVING عملیات‌های سنگینی هستند. اگر می‌توانید فیلتر کردن را قبل از GROUP BY با استفاده از WHERE انجام دهید، این کار را بکنید. این کار باعث کاهش تعداد ردیف‌هایی می‌شود که باید گروه‌بندی شوند.

-- کندتر
SELECT [Column1], COUNT(*) FROM [TableName] GROUP BY [Column1] HAVING COUNT(*) > 100;

-- بهینه‌تر (فیلتر قبل از GROUP BY)
SELECT [Column1], COUNT(*) FROM [TableName] WHERE [SomeColumn] > 'SomeValue' GROUP BY [Column1] HAVING COUNT(*) > 100;

۷. از TOP برای صفحه‌بندی (Pagination) استفاده کنید

برای بازیابی تعداد محدودی از ردیف‌ها یا پیاده‌سازی صفحه‌بندی، از TOP به همراه ORDER BY استفاده کنید. این روش بسیار کارآمدتر از بازیابی تمام ردیف‌ها و سپس فیلتر کردن آن‌ها در برنامه است.

SELECT TOP 10 [Column1], [Column2] FROM [TableName] ORDER BY [PrimaryKeyColumn];

۸. استفاده محتاطانه از NOLOCK

دستور WITH (NOLOCK) به SQL Server می‌گوید که از قفل‌های خواندن (read locks) صرف‌نظر کند. این کار می‌تواند سرعت کوئری‌های SELECT را در محیط‌های با حجم بالای تراکنش افزایش دهد، اما با ریسک خواندن داده‌های ناتمام یا “کثیف” (dirty reads) همراه است. فقط در جایی که خواندن داده‌های کاملاً دقیق حیاتی نیست، از آن استفاده کنید.

SELECT [Column1], [Column2] FROM [TableName] WITH (NOLOCK) WHERE [Condition];

۹. از WITH (RECOMPILE) برای رویه‌های ذخیره شده استفاده کنید

پارامتر sniffing مشکلی است که در آن SQL Server یک برنامه اجرایی (execution plan) را بر اساس اولین باری که یک رویه ذخیره شده (stored procedure) با پارامترهای خاص اجرا می‌شود، کامپایل می‌کند. این برنامه ممکن است برای فراخوانی‌های بعدی با پارامترهای متفاوت، بهینه نباشد. استفاده از WITH (RECOMPILE) باعث می‌شود که رویه ذخیره شده هر بار که فراخوانی می‌شود، مجدداً کامپایل شود و برنامه اجرایی بهینه‌تری تولید شود. این کار در برخی موارد می‌تواند مفید باشد، اما برای رویه‌هایی که مکرراً اجرا می‌شوند، می‌تواند سربار ایجاد کند.

EXEC [MyStoredProcedure] @Param1 = 'Value1' WITH (RECOMPILE);

-- یا در تعریف رویه ذخیره شده
CREATE PROCEDURE [MyStoredProcedure]
    @Param1 VARCHAR(50)
AS
BEGIN
    SELECT [Column1] FROM [TableName] WHERE [Column2] = @Param1
END
WITH RECOMPILE;

۱۰. از ORDER BY با GROUP BY تنها در صورت لزوم استفاده کنید

اگر نیازی به مرتب‌سازی نتایج گروه‌بندی شده ندارید، از ORDER BY در کوئری GROUP BY خودداری کنید. ORDER BY یک عملیات اضافی و زمان‌بر است.

-- کندتر (مرتب‌سازی اضافی)
SELECT [Column1], COUNT(*) FROM [TableName] GROUP BY [Column1] ORDER BY [Column1];

-- بهینه‌تر
SELECT [Column1], COUNT(*) FROM [TableName] GROUP BY [Column1];

۱۱. از زیرکوئری‌ها در لیست SELECT اجتناب کنید

زیرکوئری‌ها در لیست SELECT (به عنوان ستون‌های محاسبه شده) برای هر ردیف از کوئری اصلی اجرا می‌شوند و می‌توانند به شدت عملکرد را کاهش دهند. در صورت امکان، از JOIN برای به دست آوردن داده‌های مرتبط استفاده کنید.

-- کندتر
SELECT [Column1], (SELECT [Column3] FROM [Table2] WHERE [Table2].[ID] = [Table1].[ID]) AS [RelatedValue]
FROM [Table1];

-- بهینه‌تر (با JOIN)
SELECT T1.[Column1], T2.[Column3]
FROM [Table1] T1
LEFT JOIN [Table2] T2 ON T1.[ID] = T2.[ID];

۱۲. استفاده بهینه از دستور CASE

دستور CASE یک ابزار قدرتمند است، اما استفاده نادرست از آن می‌تواند به عملکرد آسیب برساند. سعی کنید منطق پیچیده CASE را در صورت امکان به لایه برنامه منتقل کنید یا مطمئن شوید که شرایط CASE به سادگی قابل ارزیابی هستند و از عملیات‌های سنگین مانند زیرکوئری‌ها یا توابع پیچیده در شرایط WHEN خودداری کنید.

SELECT [Column1],
       CASE
           WHEN [Column2] > 100 THEN 'High'
           WHEN [Column2] > 50 THEN 'Medium'
           ELSE 'Low'
       END AS [Category]
FROM [TableName];

۱۳. بهینه‌سازی کوئری‌های XML

کوئری‌های روی ستون‌های از نوع XML می‌توانند بسیار کند باشند. از ایندکس‌های XML استفاده کنید و کوئری‌های XQuery خود را به گونه‌ای بنویسید که تنها بخش‌های مورد نیاز از سند XML را تحلیل کنند. استفاده از exist() به جای value() در WHERE می‌تواند سرعت را افزایش دهد.

-- مثال: جستجو در ستون XML
SELECT [ID], [DataColumn].value('(/Root/Item/@Attribute)[1]', 'VARCHAR(50)') AS [AttributeValue]
FROM [XMLTable]
WHERE [DataColumn].exist('/Root/Item[@Name="SpecificItem"]') = 1;

۱۴. در صورت لزوم، JOIN را جایگزین زیرکوئری‌های WHERE کنید

در بسیاری از موارد، می‌توان یک زیرکوئری در عبارت WHERE را با یک INNER JOIN یا EXISTS جایگزین کرد که می‌تواند منجر به برنامه اجرایی کارآمدتر شود، به خصوص اگر SQL Server بتواند از ایندکس‌ها بهتر استفاده کند.

-- با زیرکوئری
SELECT T1.[Column1] FROM [Table1] T1 WHERE T1.[Column2] IN (SELECT T2.[Column3] FROM [Table2] T2 WHERE [Condition]);

-- با JOIN (معمولا سریع‌تر)
SELECT T1.[Column1] FROM [Table1] T1 INNER JOIN [Table2] T2 ON T1.[Column2] = T2.[Column3] WHERE [Condition];

۱۵. ایندکس‌ها را به درستی به کار ببرید

ایندکس‌ها حیاتی‌ترین عامل در افزایش سرعت کوئری‌های SELECT هستند. مطمئن شوید که ستون‌هایی که در عبارت WHERE، JOIN، ORDER BY و GROUP BY استفاده می‌شوند، ایندکس‌گذاری شده‌اند. از ایندکس‌های مناسب (Clustered, Non-Clustered, Covering, Columnstore) برای سناریوهای خاص خود استفاده کنید و آن‌ها را به صورت منظم بازسازی و یکپارچه (rebuild/reorganize) کنید.

۱۶. مراقب Parameter Sniffing باشید

پارامتر sniffing می‌تواند باعث شود یک رویه ذخیره شده با یک برنامه اجرایی غیربهینه برای پارامترهای بعدی اجرا شود. برای مقابله با این مشکل، می‌توانید از OPTION (RECOMPILE) (در سطح کوئری)، WITH RECOMPILE (در سطح رویه) استفاده کنید یا پارامترها را در یک متغیر محلی کپی کنید تا SQL Server نتواند از مقدار اولیه آن‌ها برای بهینه‌سازی استفاده کند.

CREATE PROCEDURE GetOrdersByStatus
    @Status INT
AS
BEGIN
    -- کپی کردن پارامتر در متغیر محلی برای دور زدن پارامتر sniffing
    DECLARE @LocalStatus INT = @Status;
    SELECT * FROM Orders WHERE OrderStatus = @LocalStatus;
END;

۱۷. INNER JOIN را به جای فیلتر در WHERE استفاده کنید (برای پیوند جداول)

هنگامی که چندین جدول را پیوند می‌دهید، استفاده از INNER JOIN برای شرایط پیوند، معمولاً واضح‌تر و در بسیاری موارد کارآمدتر از قرار دادن شرایط پیوند در عبارت WHERE است. اگرچه بهینه‌ساز SQL Server اغلب این دو را به یک شکل پردازش می‌کند، اما INNER JOIN قصد شما را شفاف‌تر نشان می‌دهد.

-- با JOIN
SELECT T1.[Column1], T2.[Column2]
FROM [Table1] T1
INNER JOIN [Table2] T2 ON T1.[ID] = T2.[ForeignKeyID];

-- با WHERE (کمتر رایج برای پیوند)
SELECT T1.[Column1], T2.[Column2]
FROM [Table1] T1, [Table2] T2
WHERE T1.[ID] = T2.[ForeignKeyID];

۱۸. استفاده از ROW_NUMBER() برای صفحه‌بندی پیشرفته

برای سناریوهای صفحه‌بندی که نیاز به پرش به یک صفحه خاص دارید، تابع ROW_NUMBER() به همراه CTE (Common Table Expression) یا یک زیرکوئری، یک روش قدرتمند و انعطاف‌پذیر است. این روش به شما امکان می‌دهد تا ردیف‌های خاصی را بر اساس یک ترتیب مشخص انتخاب کنید.

WITH PagedResults AS
(
    SELECT [Column1], [Column2],
           ROW_NUMBER() OVER (ORDER BY [PrimaryKeyColumn]) AS RowNum
    FROM [TableName]
)
SELECT [Column1], [Column2]
FROM PagedResults
WHERE RowNum BETWEEN 11 AND 20; -- صفحه دوم (10 ردیف در هر صفحه)

۱۹. INSERT INTO … SELECT در مقابل INSERT INTO … VALUES

برای درج حجم زیادی از داده‌ها، INSERT INTO ... SELECT معمولاً بسیار سریع‌تر از چندین دستور INSERT INTO ... VALUES (یا یک دستور با چندین لیست VALUES برای حجم خیلی زیاد) است. INSERT INTO ... SELECT یک عملیات ست-بیس (set-based operation) است که توسط SQL Server بهینه شده است.

-- درج چندین ردیف با SELECT
INSERT INTO [TargetTable] ([Col1], [Col2])
SELECT [SourceCol1], [SourceCol2] FROM [SourceTable] WHERE [Condition];

-- درج یک ردیف با VALUES
INSERT INTO [TargetTable] ([Col1], [Col2])
VALUES ('Value1', 'Value2');

۲۰. از Cursorها اجتناب کنید؛ از رویکردهای ست-بیس استفاده کنید

Cursorها عملیات‌های ردیف به ردیف (row-by-row) هستند که اغلب به شدت کند و ناکارآمد هستند. در SQL Server، همیشه به دنبال راه‌حل‌های ست-بیس (set-based) باشید که از عملیات‌های دسته‌ای پشتیبانی می‌کنند. توابع پنجره‌ای (window functions)، CTEها، و دستورات UPDATE یا DELETE با JOIN معمولاً جایگزین‌های کارآمدتری برای Cursorها هستند.

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