January 30, 2007 at 9:09 am
hello
i am working with query optimization on table with large row counts that have high column count. we are currently running into issues when running queries against these tables. We are currently looking into indexing the appropriate fields to speed up the performance but a debate has taken place on which method of creating the sql to retrieve the records is more optimized and the debate revolves around the WHERE clause vs the FROM clause.
take the following example (disregard the columns in this discussion, hence the reason for the *). The key being that we need to load data from another table when a date value is checked.
declare @load_dt datetime
set @load_dt = (select max(load_dt) from tblA)
--which is going to produce a faster time?
select *
from
tblA a
inner join tblB b
on a.id = b.id
WHERE
b.load_dt > @load_dt
select *
from
tblA
inner join tblB b
on a.id = b.id
and b.load_dt > @load_dt
if you have any insight on how the optimizer reads the WHERE clause vs the FROM clause when parsing the query on obtaining the data set, I would be interested to hear what you have to say
Thanks in advance
Chuck
January 30, 2007 at 10:11 am
Try the 2 queries with SHOWPLAN turned on.
They'll probably resolve to exactly the same execution plan.
[Edit] If you're working in SQL2K5 Management Studio, paste the SQL into a query window, highlight it and right-click->Display Estimated Execution Plan
January 30, 2007 at 9:38 pm
Both methods are identical in execution plan, performance, and resource usage.
--Jeff Moden
January 31, 2007 at 6:40 am
let me post a twist to this problem revolving around ORs in the where clause.
declare @load_dt datetime
set @load_dt = (select max(load_dt) from tblA)
--which is going to produce a faster time?
select *
from
tblA a
left join tblB b
on a.id = b.id
left join tblC c
on a.id = c.id
left join tblD d
on a.id = d.id
on
WHERE
b.load_dt > @load_dt
OR c.load_dt > @load_dt
OR d.load_dt > @load_dt
select *
from
tblA
left join tblB b
on a.id = b.id
and b.load_dt > @load_dt
left join tblC c
on a.id = c.id
and c.load_dt > @load_dt
left join tblB D
on a.id = d.id
and d.load_dt > @load_dt
Thanks again for any help
January 31, 2007 at 8:26 am
Just curious... why don't YOU just give it a try?
--Jeff Moden
January 31, 2007 at 9:20 am
unfortunately, the database security at the company i am working at has restrictions on the tools that i can use which includes the ability to look at the showplan. (permission denied). i can go to the DBAs for assistance, and I am, but I wanted another voice in the conversation other then my opinion vs the DBA that I am working with (which is what I thought this forum was for)
with that being said, i was trying to get an intellectual response from people that i thought would be knowledgeable in this area. the response of why don't you try it is neither helpful nor creative on your part.
January 31, 2007 at 9:43 am
Very hard for me to believe you can't display an estimated execution plan... even a fully read only data base let's you do an estimated execution plan in Query Analyzer...
So far as the response not being helpful or creative on my part, I'm trying to help you not look like an idiot to your DBA. If your DBA is any good at all, you have to come up with something that's demonstrable because really good DBA's don't take anyone's word for anything, not even from this forum... they always try it out. Try something, Chuck. Setup a test and try... then show your DBA. Without proof positive, your DBA is just going to blow you off.
--Jeff Moden
January 31, 2007 at 11:55 am
When you post a vague question, without providing any DDL for the tables, without providing any details on indexes, without providing any details on data volumes, then the advice of "why don't YOU just give it a try?" is probably the best advice possible under the circumstances.
This technical forum is no different than any other technical forum on the internet - it's give and get. You're not going to get anything out of it if you don't give something.
Posting an indignant, knee-jerk response criticising someone who took time out of their day to try and help you is not exactly the best way to encourage anyone else who might be tempted to respond.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply