Improve SQL Server Query

Posted on 2012-09-04
Last Modified: 2012-09-04
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]
Question by:koossa
    LVL 12

    Accepted Solution

    Do you only need Assets.Category to be returned in the result?
    if so, no need to group by, you could write:
    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.
    LVL 10

    Expert Comment

    ... and make the "Barcode" column an index.

    Author Closing Comment

    Thank you very much!!!

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now