ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Quick question though, I think should be fairly trivial ( I am new to MSSQL and databases in general)
2 queries, 1 is fast, the other is slow. MS SQL database.
Slow:
Code:
WHERE field1='%' OR field2='abcdef'
--or--
Code:
WHERE field1 LIKE '%' AND field2='abcdef'
Why is the second one slower? I always thought using LIKE was slower. Would the data type matter (if the field is an integer field?) The field is not indexed, the database has over 3 millions rows. field2 is indexed. Thoughts on this are appreciated. I am having a hard time finding speed differences concerning AND and OR. This is a pretty simple query, no joins or unions. All things are equal except that portion of the query.
definitely,
LIKE '%' ==> it search and return eveything to your
field1='%'==> not very sure, probably it search '%' only.
I think the same thing applied to all kind of database...
Ks
Yea you know what, I posted it backwards. The one with LIKE is faster. That is what I cannot understand.
See , i am under the impression (possible incorrectly) that it matters what type of field it is, say integer or varchar. Also, trying to find out if there is differences between AND and OR.
I am making the database bigger, and searching around various SQL sites to try to find an answer.
EDIT** i posted correct, the one with LIKE is quicker, still cannot figure why
one would think if field2 is indexed, you would use that field first.
(* i know, the server should optimize that and do that on its own, but why not write it correctly just incase..*)
as for speed difference..
case one
the server is look at each row, checking the first expression
if it matches
DONE
if not
looking at the second expression
if it matches
done
endifs
Case 2
looking at first expression
if it matches
does it match second expression
if it does
DONE
endifs
the server is looking at each row and evaluating the first expression and then then second.
as i stated at the top, the server is probably rearraning your query so that it is looking at the indexed field first.
however in case one, your have an OR, so it needs to look at field one for every single row...
in case two... so, it is only looking at the rows that matter (*i.e. field2 already matches*), AND THEN checking the to see if it matches the first expression.
SO, I would venture to guess that the LIKE statement is not the cause of the speed difference, but rather the OR statement.
Finding every single row probably isn't ideal. If it is optimizing itself, which it should be as i understand it, using the indexed field and the AND statement is pretty quick.
The OR statement requires checking every field every time, which is dumb.
Does mssql do explain? That's always useful when trying to figure out performance.
Another thing, it's possible that the query optimiser recognises that LIKE '%' matches any non-null value, and doesn't have to do any work to check it...
I thought about explain as well.
quick google didnt lead me to the tool that M$ sells for that function...
cant be built in... that would be too handy...
I thought about explain as well.
quick google didnt lead me to the tool that M$ sells for that function...
cant be built in... that would be too handy...
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.