May 1, 2011 at 9:53 pm
Please forgive my ignorance i bet there is an easy way to this but I cant figure it out. I am running a quiery that has loads of OR statements and then I need to do an AND statement the AND statement must apply to any match on any or the OR statements.
I did this
A = 'value' and
X = a
OR
X = b
OR
X = c
But what happnes is it only applies the AND to the first OR statement. I need to go through all my OR statements and then do the AND criteria on all the matches to the OR statements.
Am i making sense? Please help.
May 1, 2011 at 10:06 pm
Try this..
Where A= 'Value' And X in ('A','B','C')
Regards..
May 2, 2011 at 8:20 am
Above solution is probably the best if it fits your real script. Otherwise, remember your basic algebra. Parentheses are your friend.
So you can do things like...
SELECT *
from My Table
WHERE
(colA = 1 or colB = 1 or ColA = 1+@var)
AND
ColC = 'test'
Which means give me all rows where at least one of the checks inside the parentheses are true and where all rows have ColC = "test'
May 2, 2011 at 5:46 pm
sachnam (5/1/2011)
Try this..Where A= 'Value' And X in ('A','B','C')
Regards..
I was hoping this would work but didnt 🙁
However I dont think I gave all the info. When I said X = A it really is X is like A as in X like '%A%
How to i do this multiple OR but using wildcards?
Thanks.
May 2, 2011 at 8:49 pm
Keeping in mind that the search values will use wildcard . i personally don't think we would be able to use in operator with like operator while searching for values .
So, in that case you have to use something like that..
where (name like '%A%' or name like '%B%' or name like '%c%' ) And col1 =10
use of parenthesis is the best way you can combine expressions(Search predicates) within your query
it is working fine for me.
pls. check if it works for you .
May 2, 2011 at 10:20 pm
Hallelujah. You're my savior, man. My own personal Jesus Christ.
May 4, 2011 at 5:31 am
Keep in mind though, that LIKEs with leading wildcards are a recipe for poor performance - so careful if your table gets large.
May 4, 2011 at 5:47 pm
Doh! knew thre would be a catch things cant be that easy. Unfortunately it is a big table. If I use = and put the exact wording will that be faster? I will have to put about 7 or 8 more conditions in the query so will that cause it to take longer? Would I be better of sticking with unsing LIKE?
May 5, 2011 at 3:53 am
lance.kentwell (5/4/2011)
Doh! knew thre would be a catch things cant be that easy. Unfortunately it is a big table. If I use = and put the exact wording will that be faster? I will have to put about 7 or 8 more conditions in the query so will that cause it to take longer? Would I be better of sticking with unsing LIKE?
LIKE is fine if you don't use a leading wildcard. If you do, then it means the query optimizer is forced into a table or index scan, instead of a seek. In order to make use of indexes, a query has to be SARG-able - and the queries above aren't for the reason I mentioned.
Ultimately, the only way to tell what works best is to try it. Look at the query plans, run the two versions and see what takes longer and so on.
May 5, 2011 at 9:04 pm
Duncan Pryde (5/5/2011)
lance.kentwell (5/4/2011)
Doh! knew thre would be a catch things cant be that easy. Unfortunately it is a big table. If I use = and put the exact wording will that be faster? I will have to put about 7 or 8 more conditions in the query so will that cause it to take longer? Would I be better of sticking with unsing LIKE?LIKE is fine if you don't use a leading wildcard. If you do, then it means the query optimizer is forced into a table or index scan, instead of a seek. In order to make use of indexes, a query has to be SARG-able - and the queries above aren't for the reason I mentioned.
Ultimately, the only way to tell what works best is to try it. Look at the query plans, run the two versions and see what takes longer and so on.
I understand, luckily I dont need the leading wildcard so will take them out. Thanks again.
May 9, 2011 at 11:55 pm
OK I think i have it as streamlined as possible. One question however. Is the order of your conidtions important? e.g. there is one condition I could search on first that will eliminate half of the entries in the DB that would then need to be searched with all the LIKE statements. Does putting them in particular order improve performance?
May 10, 2011 at 1:34 am
lance.kentwell (5/9/2011)
OK I think i have it as streamlined as possible. One question however. Is the order of your conidtions important? e.g. there is one condition I could search on first that will eliminate half of the entries in the DB that would then need to be searched with all the LIKE statements. Does putting them in particular order improve performance?
No, the order shouldn't matter as the query optimizer should be clever enough to use the whole WHERE clause for deciding which indexes to use. Again, you can prove it to yourself by changing the order and confirming that the query plan doesn't change.
May 10, 2011 at 2:36 am
Unfortunately it is a big table. If I use = and put the exact wording will that be faster? I will have to put about 7 or 8 more conditions in the query so will that cause it to take longer? Would I be better of sticking with unsing LIKE?
I have a query that searches for calls for a certain phone number that takes about 5 minutes to run if I use LIKE '%123456789'. If I edit the numbers first and search using a list such as IN ('0044123456789', '0944123456789', '2144123456789', '123456789') it runs for about 1min30 even if I have to search for 50 different numbers.
I would definitely recommend using the exact value if you have it.
May 10, 2011 at 3:42 am
terrykzncs (5/10/2011)
Unfortunately it is a big table. If I use = and put the exact wording will that be faster? I will have to put about 7 or 8 more conditions in the query so will that cause it to take longer? Would I be better of sticking with unsing LIKE?
I have a query that searches for calls for a certain phone number that takes about 5 minutes to run if I use LIKE '%123456789'. If I edit the numbers first and search using a list such as IN ('0044123456789', '0944123456789', '2144123456789', '123456789') it runs for about 1min30 even if I have to search for 50 different numbers.
I would definitely recommend using the exact value if you have it.
How many records are in your table? If it's using an index I'd expect it to come back quicker than that if phone number is the only thing in your search criteria. I have a 60 million record table where using a LIKE query with a leading % would take hours (or at least tens of minutes), but with an IN clause and actual values takes less than a second. Of course there are all sorts of factors in play, but I'd expect a decent query to give speed increases of several orders of magnitude over the leading wildcard version.
May 10, 2011 at 8:56 am
That was my example to show that the IN statement would be quicker. No, there isnt an index on the called number, and the table is huge, but I am only allowed to run SELECT statements and create reports; altering the database is left to the senior staff. The script that my colleague had, would sometimes run for over 10mins. 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply