[Solved] Slow Performance when ORDER BY in SQL Server

EverSQL Database Performance Knowledge Base

Slow Performance when ORDER BY in SQL Server

Database type:

I'm working on a project (Microsoft SQL Server 2012) in which I do need to store quite some data. Currently my table does contains 1441352 records in total.

The structure of the table is as follows:

My table is indexed on the following fields:

Now, when I'm writing an API to return the records in the table. The API exposes methods and it's executing this query:

SELECT TOP (51) 
    [GlnCode] AS [GlnCode], 
    [VendorId] AS [VendorId], 
    [ItemNumber] AS [ItemNumber], 
    [ItemUOM] AS [ItemUOM], 
    [RecordIdentitifer] AS [RecordIdentitifer], 
    [Description] AS [Description], 
    [VendorName] AS [VendorName]
FROM [dbo].[T_GENERIC_ARTICLE]

If I look at the performance, this is good. But, this doesn't guarantee me to return always the same set, so I need to apply an ORDER BY clause, meaning the query being executed looks like this:

SELECT TOP (51) 
    [GlnCode] AS [GlnCode], 
    [VendorId] AS [VendorId], 
    [ItemNumber] AS [ItemNumber], 
    [ItemUOM] AS [ItemUOM], 
    [RecordIdentitifer] AS [RecordIdentitifer], 
    [Description] AS [Description], 
    [VendorName] AS [VendorName]
    FROM [dbo].[T_GENERIC_ARTICLE]
    ORDER BY [GlnCode] ASC, [ItemNumber] ASC, [ItemUOM] ASC, [VendorId] ASC

Now, the query takes a few seconds to return, which I can't afford.

Anyone has any idea on how to solve this issue?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX t_article_idx_glncode_itemnum_itemuom_vendori ON dbo.T_GENERIC_ARTICLE (GlnCode,ItemNumber,ItemUOM,VendorId);
The optimized query:
SELECT
        TOP (51) [dbo].[T_GENERIC_ARTICLE].[GlnCode] AS [GlnCode],
        [dbo].[T_GENERIC_ARTICLE].[VendorId] AS [VendorId],
        [dbo].[T_GENERIC_ARTICLE].[ItemNumber] AS [ItemNumber],
        [dbo].[T_GENERIC_ARTICLE].[ItemUOM] AS [ItemUOM],
        [dbo].[T_GENERIC_ARTICLE].[RecordIdentitifer] AS [RecordIdentitifer],
        [dbo].[T_GENERIC_ARTICLE].[Description] AS [Description],
        [dbo].[T_GENERIC_ARTICLE].[VendorName] AS [VendorName] 
    FROM
        [dbo].[T_GENERIC_ARTICLE] 
    ORDER BY
        [dbo].[T_GENERIC_ARTICLE].[GlnCode] ASC,
        [dbo].[T_GENERIC_ARTICLE].[ItemNumber] ASC,
        [dbo].[T_GENERIC_ARTICLE].[ItemUOM] ASC,
        [dbo].[T_GENERIC_ARTICLE].[VendorId] ASC

Related Articles



* original question posted on StackOverflow here.