"Estimated Execution Plan" I just don't get it. 2

[JNeave]

Hi,

First off, here is how I generally write my stored procedures (sprocs)

I create a view, add and join all the tables and do as much configuration as is possible in a view.

Then copy/paste to Query Analyser and format it nicely, drop all the junk and write the complex stuff.

What I always do is re-arrange the FROM clause into something readable.

And I always check what the Estimated Execution Plan is before/after I re-write the FROM clause. And it ALWAYS seems to change astronomically...

Today, a query went from having a few hashes and a few nested loops to having lots of team hash joins (??) and lots of stuff about parallel execution (well, it sounds good, but ??? again).

All because I re-arranged the FROM clause!!

I always re-arrange my FROMs to..

FROM <table1> 
     INNER/OUTER JOIN <table2> *** ON <Join Criteria>
     <More and More Joins>

See the three stars? That's where Enteprise manager seems to like to put the other joins, in the middle of other joins. Which I find makes the query almost un-readable.

I time both queries, I've never seen any appreciable speed difference. The example for today took 49 seconds regardless, even though the estimated subtree cost was

40 before rearrange
58 after rearrange

Not that numbers mean anything except a ~50% difference to me.

Does anybody know where I can find a good reference guide to what's going on here?

I'd really like to know if any of this can really benefit performence. All my report queries take 1-3 mins on a dual PIII 1.2 Ghz Dell with 512Mb of RAM. I have no idea whether this is good/bad/awfull. Our only comparison is the access system it replaced and we kinda beat it's "go and have several cups of tea, we're not joking this will take until the morning" performance.

Cheers,

Jim.

 

[tlbroadbent]

The estimated query plan is a good tool to use. However, it is imperfect and I have found that the cost numbers don't always reflect what will really happen. Like you, I've seen two queries that differ slightly produce very different cost numbers but perform essentially the same. I've also seen queries with similar cost numbers differ greatly in actual run time.

In addition to SQL BOL, check the following resources for information about analyzing a query plan.

SQL Server Query Execution Plan Analysis

http://www.sql-server-performance.com/query_execution_plan_analysis.asp

SQL Server Performance Tuning: Pt. 1

http://databasejournal.com/features/mssql/article.php/10894_1435721_3

I never use Enterprise Manager to build view or queries. I prefer to use Query Analyzer. One reason is the difficult syntax produced by EM.

The order of the tables in the FROM clause can make a difference in the query. Placement of parentheses can also change the query plan. Query tuning requires a lot of trial and error. I use Show Estimated Query Plan extensively but don't rely on it totally. I also analyze the Statistics IO and Time becuae these show what actually happened. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[JNeave]

Hurray! Thanks! I shall read these forthwith.

You do all your queries in QA? Don't you find it time consumig typing table and field names? Not to mention remembering them all, I could never do that.

"Statistics IO and Time"
I don;t understand what you meant by that though. Just shout if it's in those docos you linked and I'm sure I'll read about them.

Ta.

Jim.

 

[tlbroadbent]

Query Analyzer has an Object Browser. Hit F8 or select Tools | Object Browser | Show/Hide to open it. You can drag table, view, procedure and column names from the Object Browser to the query window. If you click on the columns folder of a table and drag it to the query window, all the columns, separated by commas, will be placed in the window.

In addition, you can script objects, functions, indexes, etc. to a query window or to the clipboard. Simply right-click an object in the browser to see the options available for that object. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm