SQL Optimization

Question

Post reply

SQL Optimization

Chuck Rivel

SSCrazy

Points: 2991
More actions
January 30, 2007 at 9:09 am

#194119

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Both methods are identical in execution plan, performance, and resource usage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Just curious... why don't YOU just give it a try?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

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.