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

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

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]
0
koossa
Asked:
koossa
1 Solution
 
Habib PourfardCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now