CODEWITHSUNDEEP
sql-server
Complexity
Complexity

Reputation: 5820

Slow Performance when ORDER BY in SQL Server

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?

Upvotes: 2

Views: 4063

Answers (1)

Geewers
Geewers

Reputation: 206

Your table index definitions are not optimal. You also don't have to created the additional individual indexes because they are covered by the Non Clustered Index. You will have better performance when structuring your indexes as follows:

Table definition:

CREATE TABLE [dbo].[T_GENERIC_ARTICLE]
(
    RecordIdentifier int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    GlnCode nvarchar(100) NOT NULL,
    Description nvarchar(MAX) NOT NULL,
    VendorId nvarchar(100) NOT NULL,
    VendorName nvarchar(100) NOT NULL,
    ItemNumber nvarchar(100) NOT NULL,
    ItemUOM nvarchar(128) NOT NULL
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [UniqueNonClusteredIndex-Composite2]
    ON [dbo].[T_GENERIC_ARTICLE](GlnCode, ItemNumber,ItemUOM,VendorId ASC);

GO

Revised Query

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

First a key lookup will be performed on the Primary Key and then a Non Clustered Index Scan. This is where you want the majority of the work to be done.

Reference: Indexes in SQL Server

Hope This helps

Upvotes: 2

Related Questions