pzozulka
asked on
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 ...
....
* 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 ...
....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, have not had a chance to read that yet.
@anthony thanks for correcting me, i did not know that indexes are not used in %T% like situations..
ASKER