Slow Performance when ORDER BY in SQL Server -

i'm working on project (microsoft sql server 2012) in need store quite data. table contains 1441352 records in total.

the structure of table 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 indexed on 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 api return records in table. api exposes methods , it's executing query:

select top (51)      [glncode] [glncode],      [vendorid] [vendorid],      [itemnumber] [itemnumber],      [itemuom] [itemuom],      [recordidentitifer] [recordidentitifer],      [description] [description],      [vendorname] [vendorname] [dbo].[t_generic_article] 

if @ performance, good. but, doesn't guarantee me return same set, need apply order by clause, meaning query being executed looks this:

select top (51)      [glncode] [glncode],      [vendorid] [vendorid],      [itemnumber] [itemnumber],      [itemuom] [itemuom],      [recordidentitifer] [recordidentitifer],      [description] [description],      [vendorname] [vendorname]     [dbo].[t_generic_article]     order [glncode] asc, [itemnumber] asc, [itemuom] asc, [vendorid] asc 

now, query takes few seconds return, can't afford.

anyone has idea on how solve issue?

your table index definitions not optimal. don't have created additional individual indexes because covered non clustered index. have better performance when structuring indexes 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] [recordidentitifer],     [glncode] [glncode],      [vendorid] [vendorid],      [itemnumber] [itemnumber],      [itemuom] [itemuom],           [description] [description],      [vendorname] [vendorname]     [dbo].[t_generic_article]     order [glncode], [itemnumber], [itemuom], [vendorid]  

first key lookup performed on primary key , non clustered index scan. want majority of work done.

reference: indexes in sql server

hope helps