View performance

Question

View performance

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Hoogendoorn SSC Eights! Points: 843 More actions · Answer 2

Hi There,

Changing the query to explicit INNER JOINs with ON statements might prevent improper execution plan.

In your current query, their might be a possibility that SQL first joins all tables and then applies a filter.

By explicitely using INNER JOIn with ON, you will be sure that the tables are pre-filtered before the are joined. As a result less I/O will be needed.

Hope this will help you out.

Kind regards,

Dave

Dave Hoogendoorn SSC Eights! Points: 843 More actions · Answer 3

By the way,

Also I should get rid of the "TOP 100 percent" if you don't use it.

Kind regards,

Dave

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

Dave Hoogendoorn (8/15/2010)
Hi There,
Changing the query to explicit INNER JOINs with ON statements might prevent improper execution plan.
In your current query, their might be a possibility that SQL first joins all tables and then applies a filter.
By explicitely using INNER JOIn with ON, you will be sure that the tables are pre-filtered before the are joined. As a result less I/O will be needed.
Hope this will help you out.
Kind regards,
Dave

Do you have a coded example that shows such a thing?

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

Richard Williams-416852 Old Hand Points: 374 More actions · Answer 5

All depends on how many rows you have in each table, which columns are indexed etc - i agree with previous post - not using INNER JOIN won't be helping

Sometimes SQL Server doesn't go the right way on a join i.e. pick the smallest dataset to start with. Using sub-selects in the SELECT and FROM sections can help it to make the correct decision, but the answer lies in knowing the data, indexes etc.

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 6

Please follow Gail's recommendations and then we dont have to guess...

with the above requested info we can give you an answer very quickly