بهینه‌سازی کوئری SQL اکسس؛ افزایش سرعت با ایندکس و فیلتر

کوئری‌ای که روی بیست رکورد فوراً اجرا می‌شود، ممکن است بعد از رشد پایگاه داده به صدها هزار سطر چندین ثانیه طول بکشد. واکنش رایج، ساختن ایندکس برای همه فیلدهاست؛ اما این کار می‌تواند عملیات ثبت و ویرایش را کند کند و مشکل اصلی مانند JOIN اضافی یا ستون‌های غیرضروری را حل نکند.

این درس پیشرفته مباحث INDEX و SELECT و WHERE و JOIN و ORDER BY را در قالب یک روش آزمون‌پذیر کنار هم می‌گذارد. دنبال تنظیم جادویی یا درصد افزایش سرعت تضمین‌شده نیستیم. هدف آن است که ابتدا نتیجه درست را ثابت کنیم، هزینه اضافی را پیدا کنیم، هر بار یک عامل را تغییر دهیم و بهبود را با داده نزدیک به محیط واقعی بسنجیم.

مفهوم و محدوده کاربرد

بهینه‌سازی با تعریف خروجی مورد انتظار آغاز می‌شود. باید مشخص باشد هر سطر نماینده یک سفارش است یا یک ردیف سفارش، چه ستون‌هایی لازم‌اند و کدام فیلتر و ترتیب اجباری است. بدون این قرارداد ممکن است کوئری سریع‌تر رکوردهای معتبر را حذف یا جمع را چند برابر کند. سریع‌تر شدن فقط زمانی مفید است که معنی نتیجه حفظ شود.

ایندکس می‌تواند یافتن یا مرتب‌کردن سطرهای بعضی فیلدها را آسان کند، اما سود آن به میزان انتخابی‌بودن شرط و ساختار کوئری وابسته است. فیلدی که تقریباً همه سطرها مقدار یکسانی دارند، همیشه بهترین گزینه برای ایندکس مستقل نیست. ایندکس هنگام INSERT و UPDATE نیز هزینه نگهداری دارد و باید بر اساس بار کاری واقعی انتخاب شود.

موتور عبارت‌های SQL می‌تواند روی فیلدها تابع اجرا کند؛ برای نمونه Year(OrderDate)=2026 خواناست، ولی شرط بازه مستقیم روی OrderDate ممکن است با ایندکس سازگارتر باشد. همچنین انتخاب همه ستون‌ها یا پیوند دادن جدولی که گزارش نیازی به آن ندارد، کار اضافی ایجاد می‌کند. جایگزین ساده‌تر را بنویسید، سپس برابری نتیجه دو نسخه را اثبات کنید.

داده‌های نمونه و فرض‌ها

جدول‌های Customers با CustomerID و CustomerName، Orders با OrderID و CustomerID و OrderDate و TotalAmount و Status و OrderDetails با OrderDetailID و OrderID و ProductID و Quantity فرض می‌شوند. در نسخه آزمایشی تعداد مناسبی سفارش با تاریخ و وضعیت متنوع ایجاد کنید. برای تولید انبوه رکورد آزمایشی هرگز روی پایگاه عملیاتی واقعی اسکریپت اجرا نکنید.

ساختار دستور و تفسیر آن

از شرط انتخابی و فیلدهای لازم شروع کنید. اگر فیلد پرتکرار در فیلتر، مرتب‌سازی یا JOIN از ایندکس سود می‌برد، وجود ایندکس مناسب را بررسی کنید. حذف WHERE یا JOIN یا ORDER BY فقط برای کاهش زمان اجرا، وقتی جزئی از قرارداد گزارش هستند، بهینه‌سازی محسوب نمی‌شود.

برای محدودکردن تاریخ، فیلد اصلی را مستقیم با دو مرز مقایسه کنید؛ لازم نیست روی تک‌تک سطرها DateValue یا Year اجرا شود. اگر فرم فقط شناسه، نام و مبلغ می‌خواهد، SELECT * ننویسید. این تغییرها درک کوئری را نیز آسان‌تر می‌کنند و از حدس‌زدن درباره جزئیات داخلی موتور مطمئن‌ترند.

SELECT OrderID, CustomerID, TotalAmount
FROM Orders
WHERE OrderDate >= DateSerial(2026,1,1)
  AND OrderDate < DateSerial(2026,2,1)
ORDER BY OrderDate, OrderID;

مثال‌های گام‌به‌گام

مثال‌ها عبارت تابعی روی تاریخ را با شرط بازه مستقیم مقایسه می‌کنند، ضرورت انتخاب ستون‌های محدود را نشان می‌دهند و سطح سطر در JOIN را روشن می‌کنند. هیچ مثال وعده افزایش سرعت قطعی نمی‌دهد؛ باید اثر آن در محیط واقعی سنجیده شود.

فیلتر با بازه مستقیم تاریخ

این شرط بدون اجرای دو تابع استخراج سال و ماه روی فیلد، بازه ژانویه را مشخص می‌کند. نخست بررسی کنید با شرط Year(OrderDate)=2026 و Month(OrderDate)=1 نتیجه یکسانی دارد. سپس روی حجم واقعی بسنجید آیا ایندکس و توزیع داده بهبود سرعت ایجاد کرده‌اند یا خیر.

SELECT OrderID, OrderDate FROM Orders WHERE OrderDate >= DateSerial(2026,1,1) AND OrderDate < DateSerial(2026,2,1);

بازگرداندن ستون‌های موردنیاز

گزارشی که همین سه ستون را می‌خواهد نیازی به دریافت فیلدهای متن طولانی یا اطلاعات کمکی ندارد. در جدول عریض این انتخاب می‌تواند هزینه انتقال داده را کم کند و مهم‌تر از آن ساختار خروجی را پایدار می‌سازد. با افزودن ستون جدید به Orders شکل خروجی ناخواسته عوض نخواهد شد.

SELECT OrderID, CustomerID, TotalAmount FROM Orders WHERE Status = "Completed";

کنترل سطح رکورد پس از JOIN

این خروجی در سطح ردیف سفارش است و نه یک سطر برای هر سفارش. سفارشی با سه ردیف جزئیات، سه بار ظاهر می‌شود. نباید بدون اصلاح سطح گزارش، TotalAmount سربرگ سفارش را در چنین خروجی جمع زد؛ زیرا احتمال چندبرابر شدن مبلغ وجود دارد. قبل از تجمیع، سطح هر سطر را صریح بنویسید.

SELECT O.OrderID, D.OrderDetailID, D.Quantity FROM Orders AS O INNER JOIN OrderDetails AS D ON O.OrderID = D.OrderID;

اجرای کوئری با VBA در اکسس

روال DAO زمان لازم برای خواندن یک snapshot را می‌سنجد و تعداد سطرهای خروجی را می‌شمارد. این روش برای مقایسه دو SELECT معادل روی یک دیتابیس و سخت‌افزار کاربرد دارد. یک بار اندازه‌گیری کافی نیست؛ چند بار اجرا کنید، نمونه غیرعادی را مشخص و میانه یا بازه نتایج را گزارش دهید.

تابع Timer در نیمه‌شب بازنشانی می‌شود، بنابراین برای آزمایش عبوری از نیمه‌شب باید منطق مکمل افزود. نمونه حاضر برای اجرای کوتاه در ساعات عادی مناسب است، نتیجه را در Immediate چاپ می‌کند و هیچ داده‌ای تغییر نمی‌دهد. عدد هدف برای سرعت تعریف نکرده‌ایم، چون سخت‌افزار، شبکه و حجم واقعی داده متفاوت‌اند.

Option Explicit
Public Sub MeasureCompletedOrders()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim started As Single
    Dim rowCount As Long
    On Error GoTo Fail
    Set db = CurrentDb
    started = Timer
    Set rs = db.OpenRecordset( _
        "SELECT OrderID, TotalAmount FROM Orders " & _
        "WHERE Status = 'Completed' ORDER BY OrderID;", dbOpenSnapshot)
    Do Until rs.EOF
        rowCount = rowCount + 1
        rs.MoveNext
    Loop
    Debug.Print "Rows:", rowCount, "Seconds:", Timer - started
CleanUp:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
Fail:
    Debug.Print Err.Number, Err.Description
    Resume CleanUp
End Sub

کنترل نتیجه و شرایط مرزی

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

خطاهای رایج و راه‌حل

بهینه‌سازی گمراه‌کننده معمولاً از تغییر معنی نتیجه، آزمایش روی داده کم یا نسبت دادن هر کندی به نبود ایندکس ناشی می‌شود. این علائم را بررسی کنید:

بهترین روش‌ها برای کوئری قابل اعتماد

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

ایندکس را برای فیلدهای پرتکرار و انتخابی در WHERE و ORDER BY و JOIN انتخاب کنید. پیش از ساخت، وجود ایندکس کلید اصلی و خارجی را بررسی کنید تا نسخه تکراری ایجاد نشود. هزینه درج و به‌روزرسانی رکورد را هم بسنجید، به‌خصوص اگر بانک اطلاعاتی مرتباً فایل‌های حجیم را وارد می‌کند.

متن SQL اولیه، شمار رکورد، زمان اجرا، اندازه داده و مشخصات محیط را ثبت کنید. در هر مرحله فقط یک شرط را تغییر دهید. اگر نسخه جدید سریع‌تر است ولی مجموعه OrderID متفاوتی دارد، تا زمانی که تغییر معنی نتیجه تأیید نشده آن را بهبود ننامید.

ابزارهای تشخیص بهینه‌ساز Access با SQL Server یکسان نیستند. نباید ادعا کنیم یک شرط حتماً برنامه اجرای مشخصی را تحمیل می‌کند؛ رفتار Jet/ACE با داده و نسخه و تنظیمات فرق دارد. به جای حدس درباره برنامه داخلی، از زمان‌سنجی تکرارپذیر و ایندکس‌های مستند استفاده کنید.

کندی گزارش ممکن است از فرم، رندر خروجی، دیسک، جدول پیوندی، آنتی‌ویروس، تأخیر شبکه یا حلقه VBA باشد، نه صرف SQL. زمان بازشدن Recordset را از زمان قالب‌بندی یا نمایش جدا کنید. بخشی را بهینه کنید که اندازه‌گیری نشان می‌دهد واقعاً گلوگاه است، نه صرفاً بخشی که کد طولانی‌تری دارد.

آزمایشگاه تمرین

دو کوئری معادل برای سفارش‌های ژانویه بسازید: یکی با Year(OrderDate)=2026 و Month(OrderDate)=1 و دیگری با بازه نیمه‌باز DateSerial. ابتدا OrderIDها و جمع مبالغ را برابر کنید. سپس چند بار روی داده مناسب زمان بگیرید و بررسی کنید وجود ایندکس OrderDate چه تغییری ایجاد می‌کند.

گزارشی از مشتری و سفارش بسازید که با اتصال نادرست به OrderDetails باعث تکثیر سطر شود. سطح خروجی را روشن کنید: یک سطر برای سفارش لازم است یا برای ردیف سفارش؟ پس از اصلاح، شمارش را مقایسه کنید. در پایان ستون‌های بلااستفاده را حذف و ORDER BY قطعی اضافه و اثر هر تغییر را مستند کنید.

درس‌های مرتبط

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

• مرتب‌سازی ORDER BY در اکسس: آموزش مرتب‌سازی نتایج کوئری با ORDER BY در SQL اکسس
• کوئری پارامتری با QueryDef: کوئری پارامتری در SQL اکسس با QueryDef و VBA
• کاربرد WHERE در SQL اکسس: آموزش SQL در Microsoft Access: کاربرد جزء WHERE در SQL

جمع‌بندی

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

کوئری سریع‌تر فقط زمانی ارزشمند است که رکوردها، رابطه‌ها، جمع‌ها، سیاست Null و ترتیب خروجی همچنان برای گزارش مناسب باشند. مستندسازی این شروط تفاوت بهینه‌سازی پایدار با اشتباه ظاهراً جذاب را مشخص می‌کند.