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

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

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 ...
....
0
pzozulka
Asked:
pzozulka
  • 2
  • 2
  • 2
3 Solutions
 
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
 
pzozulkaAuthor Commented:
What is dynamic SQL? The @reportType variable is being passed to the stored procedure from the report (ActiveReports - C#).
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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