Improve SQL Server Query

Good day

How can I improve the performance of the following query, it's taking too long if I have a lot of data in the database.

SELECT Assets.Category FROM Assets Where Assets.[Asset Barcode] <>'-99' And  ISNULL([Select], 0)>=0 GROUP BY Assets.Category ORDER BY Assets.Category;

I've got a NONCLUSTERED INDEX on [Select] and [Category] and a Primary key / CLUSTERED INDEX on [Asset Barcode]
koossaAsked:
Who is Participating?
 
Habib PourfardSoftware DeveloperCommented:
Do you only need Assets.Category to be returned in the result?
if so, no need to group by, you could write:
SELECT  DISTINCT
        Assets.Category
FROM    Assets
WHERE   Assets.[Asset Barcode] <> '-99' AND ( [Select] IS NULL OR [Select] >= 0 )

Open in new window


no need to write "ORDER BY Assets.Category" because it is clustered index and it will be ordered by Assets.Category by default.

check [Asset Barcode] datatype. if it is fixed length string, set the datatype to CHAR(size) else if it is variable length string set datatype to VARCHAR(size).  to find the best size you can write a query like this: SELECT MAX(LEN(Assets.[Asset Barcode])) FROM Assets

The indexes you created are efficient and if it is slow, it is not as a result of the query you've wrote.
0
 
ienaxxxCommented:
... and make the "Barcode" column an index.
0
 
koossaAuthor Commented:
Thank you very much!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.