asked on

SQL Server, Sort Order on indexed field Slow

Good day

I've got a database with 500k records linked to other tables that have 2 million records etc.
When I do a query for top 100 including a where and sort it by my primary key it took 0.80 seconds to return the data, but when I do the same Query and sort it by [Name] it took 22 seconds.
My Name field is also indexed.

Am I doing something wrong?

Thomasian

Are you only sorting it by a single field (name)? Is the name field the only column on the index?

koossa

ASKER

Yes, I only sort it by Name and Name is in its own index, but other fields are indexed as well.

To give you a better idea..

CREATE NONCLUSTERED INDEX IX_Assets_Name ON Assets ([Name])
CREATE NONCLUSTERED INDEX IX_Assets_Description ON Assets ([Description])
CREATE NONCLUSTERED INDEX IX_Assets_Category ON Assets ([Category])

Open in new window

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

koossa

ASKER

No, will give it a try

DcpKing

A primary key may often be faster because it is usually a clustered key. However, one might expect an index on an int field to return results faster than an index on a varchar(150) field, for example, simply because working with and comparing ints is intrinsically somewhat faster.

Try rebuilding the index to see if it can be improved.

koossa

ASKER

I have run the Execution plan analyser (see attached)
When I move my mouse over the sort, that is highlighted in the screenshot it says :
"Order by Assets.Name Ascending, Expr1054 Ascending"
I have no idea what Expr1054 is?
untitled.PNG

DcpKing

What is the definition of your index on Name? Are there other indices that include Name?

koossa

ASKER

Here is my query that take the longest to execute, that I have used to analyse

SELECT top 100 Division.Name as DivName, Locations.Name AS LocName, Case When  [Assets].[Select]=0 or [Assets].[Select] is null then 0 Else 1 End as [Select], [Asset Barcode] AS [Asset Barcode], Assets.Name , Assets.Description, Assets.Category, Assets.[Division ID], [Status History].Status, [Status History].[Status Date], [Status History].[Location Barcode], Assets.[Purchase Date], Assets.[Purchase Price], Assets.[Expiry Date], Assets.[Expiry value], [Status History].[History ID], Assets.[Notes],[Custom1],[Custom2],[Custom3],[Custom4],[Custom5],Case When Photo_Thumb Is Null Then 0 Else 1 End as PhotoExists FROM AssetZure.dbo.Assets LEFT OUTER JOIN [AssetZure].[dbo].Division ON Assets.[Division ID] = Division.ID LEFT JOIN ([AssetZure].[dbo].Locations RIGHT JOIN [AssetZure].[dbo].[Status History] ON Locations.[Location Barcode] = [Status History].[Location Barcode]) ON Assets.[Asset Barcode] = [Status History].[Asset ID] Where ( (Division.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or ([Asset Barcode] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or (AssetZure.dbo.Assets.[Name] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Description] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Notes] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Category] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or ([Status] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode]))) or ([Locations].[Name] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [AssetZure].[dbo].[Status History] Where [Asset ID] = [Asset Barcode])))) or ( (Division.[Name] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Asset Barcode] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Name] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Description] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Notes] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Category] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Status] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Locations].[Name] like '%test%' and  ISNULL(AssetZure.dbo.[Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)))ORDER BY [Name] ASC;

Open in new window

koossa

ASKER

Name is indexed as follow

CREATE NONCLUSTERED INDEX IX_Assets_Name ON Assets ([Name])

Open in new window

See attached screenshot.
index.PNG

SOLUTION

DcpKing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DcpKing

BTW, I think your Where clause can simplify like this (I substituted aliases here and there to make it easier to read for me, but they should be obvious):

Where (
			(	
				D.[Name] like '%test%' or 
				A.[Asset Barcode] like '%test%' or 
				A.[Name] like '%test%' or 
				A.[Description] like '%test%' or 
				A.[Notes] like '%test%'  or 
				A.[Category] like '%test%'  or 
				[Status] like '%test%' or 
				[Locations].[Name] like '%test%' 
			)
		and	(
					ISNULL(A.[Select], 0)>=0 
				and	ISNULL([Locations].[Select], 0)>=0 
				And	(SH.[Status Date] =	(	Select Max([Status Date]) 
															from [AssetZure].[dbo].[Status History] 
															Where A.[Asset ID] = [Asset Barcode]
														)
					)
			)
		or 
			(
					ISNULL(A.[Select], 0) >= 0 
				and ISNULL([Locations].[Select], 0) >= 0 
				And	(SH.[History ID] Is Null) 
			)
	)

Open in new window

koossa

ASKER

Thank you.

DcpKing

So did you manage to improve it satisfactorarily?

koossa

ASKER

There seems to be an improvement when I change my sort for example from "Order by [Name]" to "Order by Assets.[Name]"

But I decided to re-plan my database layout, that will drastically improve its performance.
Thank you very much for your answers!!