MS SQL - speed question.
 

I know, I know.

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:
--or--

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.

Good point, I was thinking of it like that.

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.


Thanks!

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...

Bummer. Switch to postgres, it kicks butt.