EverSQL Database Performance Knowledge Base

Question 1

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:

RecordIdentifier (int, not null)
GlnCode (PK, nvarchar(100), not null)
Description (nvarchar(MAX), not null)
VendorId (nvarchar(100), not null)
VendorName (nvarchar(100), not null)
ItemNumber (PK, nvarchar(100), not null)
ItemUOM (PK, nvarchar(128), not null)

My table is indexed on the following fields:

NonClustered - GlnCode, Ascending
NonClustered - ItemNumber, Ascending
NonClustered - ItemUOM, Ascending
NonClustered - VendorID, Ascending
Clustered - Unique (The above 4 columns together).

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?

Answer

How to optimize this SQL query?

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

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

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

Question 2

EverSQL Database Performance Knowledge Base

Slow Performance when ORDER BY in SQL Server

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles