SQL Query Optimization

Posted on 2014-08-24
Last Modified: 2014-08-26
Query is running against millions of records, so performance is most important. Two questions:

* Can you think of another way that is more optimal than the below, to exclude all records that have a 'T' in them?
WHERE CHARINDEX('T', column) = 0

* Can you think of another way that is more optimal than the below, to do:
WHERE CASE WHEN @reportType = 'Agent' then e.EntityTypeId = 3 END

another way of writing that is:
WHERE CASE WHEN @reportType = 'Agent' then e.EntityTypeId = 3
                       WHEN @reportType = 'Submitted By' then e.EntityTypeId = ALL VALUES -- in other words, this line of code is not needed. In other words, e.EntityTypeId in (1,2,3,4).

My solution was:
WHERE ((@reportType = 'Agent' AND e.EntityTypeId = 3) OR (@reportType = 'Submitted By' AND e.EntityTypeId in (1,2,3,4))
AND ...
Question by:pzozulka
    LVL 11

    Assisted Solution

    by:Dany Balian
    for the first question: you can use "like"
    if u use: WHERE column not like '%T%'
    instead of: WHERE CHARINDEX('T', column) = 0

    it would be better, because a LIKE expression -can- make use of an index.

    for the second question: maybe just ommit the entitytypeid query
    WHERE ((@reportType = 'Agent' AND e.EntityTypeId = 3) OR (@reportType = 'Submitted By'))
    LVL 75

    Accepted Solution

    Can you think of another way that is more optimal than the below, to exclude all records that have a 'T' in them?
    Sorry to differ with the previous comment, but if the equivalent of CHARINDEX('T', column) = 0 is column LIKE '%T%', then there is no chance of any index being used.  The only way an index may be used is if it has this form LIKE 'T%'

    Can you think of another way that is more optimal than the below,
    unfortunately, this is a case where Dynamic SQL would be best.  Just make sure you are allowed in your shop to use Dynamic SQL before you make the change.
    LVL 8

    Author Comment

    What is dynamic SQL? The @reportType variable is being passed to the stored procedure from the report (ActiveReports - C#).
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    What is dynamic SQL?
    Did you not read the link Randy posted in your other similar question?  That link covered sp_executesql, but you can also create Dynamic SQL with EXECUTE.  However, sp_executesql is preferred.

    In essence Dynamic SQL is when the SQL statement is created on the fly.  This means that there are certain security risks involved (read SQL Injection), but these can be mitigated for the most part using parameterized Dynamic SQL using sp_executesql.

    For an extensive read on the subject see Erland's blog:
    The Curse and Blessings of Dynamic SQL
    LVL 8

    Author Comment

    No, have not had a chance to read that yet.
    LVL 11

    Expert Comment

    by:Dany Balian
    @anthony thanks for correcting me, i did not know that indexes are not used in %T% like situations..

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    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…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    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

    20 Experts available now in Live!

    Get 1:1 Help Now