SQL Query Optimization

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 ...
....
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dany BalianCTOCommented:
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'))
0
Anthony PerkinsCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
What is dynamic SQL? The @reportType variable is being passed to the stored procedure from the report (ActiveReports - C#).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
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
0
pzozulkaAuthor Commented:
No, have not had a chance to read that yet.
0
Dany BalianCTOCommented:
@anthony thanks for correcting me, i did not know that indexes are not used in %T% like situations..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.