[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1106
  • Last Modified:

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?
0
koossa
Asked:
koossa
  • 7
  • 5
  • 2
2 Solutions
 
ThomasianCommented:
Are you only sorting it by a single field (name)? Is the name field the only column on the index?
0
 
koossaAuthor Commented:
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

0
 
ThomasianCommented:
Did you try checking the execution plan on SSMS?
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
koossaAuthor Commented:
No, will give it a try
0
 
DcpKingCommented:
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.
0
 
koossaAuthor Commented:
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
0
 
DcpKingCommented:
What is the definition of your index on Name? Are there other indices that include Name?
0
 
koossaAuthor Commented:
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

0
 
koossaAuthor Commented:
Name is indexed as follow
CREATE NONCLUSTERED INDEX IX_Assets_Name ON Assets ([Name])

Open in new window


See attached screenshot.
index.PNG
0
 
DcpKingCommented:
I worked through the query (was it originally created in Access, by any chance? ) and, as you noticed by time it, and as the query plan shows, it's spending almost all its time sorting on your name field.

Could you explicitly add the tablename to the order by, so it reads "order by assets.name", please, and also take out the "top 100" part and see what happens. If that speeds things up, then wrap this as a sub-query from which you get the top 100 in the outside query. I suspect that the Top 100 part is forcing the sort.

hth

Mike
0
 
DcpKingCommented:
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

0
 
koossaAuthor Commented:
Thank you.
0
 
DcpKingCommented:
So did you manage to improve it satisfactorarily?
0
 
koossaAuthor Commented:
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!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now