Query help!!!
-
I'm trying to track down some high usage queries that are causing issues on a busy db (2500+ SQL Batch requests/sec)
I'm seeing lots of queries with the following code and I'm not familiar with what the '% 2' or '% 10' actually does:
and User_1.objValue_ID % 2 = @P6)
or for example
FileStorage_1.objTemplated_ID % 10 = @P6)
Obviously with this in the queries in the optimizer will use a scan and because the tables in the various queries using this logic have millions of rows it's taking a long time to return the results.
Can anyone explain what the logic is here? or if there is a better way of doing it? I can provide the entire query if necessary.
Many thanks
-
% outside of a like clause is the MODULO operator and returns the remainder of the division of the calculation
-
column_value % modulo_value --> is a modulo operator
% 2 [modulo 2] is generally used to check if a given number is even or odd
% 10 [modulo 10] is generally used to get the last digit of any given number
Hope this clears ur doubt.
Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
better way optimize for modulo 10 would be to use RIGHT function.
RIGHT(value,1) -- check if this helps
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
lokeshvij (7/18/2012)
better way optimize for modulo 10 would be to use RIGHT function.RIGHT(value,1) -- check if this helps
Hmm... Nope. SELECT .09 % 10
Jared
CE - Microsoft -
🙂
works for x % 10 where x >= 10
Thanks
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply