Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Chester
Starting Member
27 Posts |
 Posted - 2004-03-01 : 15:53:44
|
| When doing a join between two tables is it best to have the BIGGER table first or second? |
|
|
saglamtimur
Yak Posting Veteran
91 Posts |
Posted - 2004-03-01 : 16:06:15
|
| second |
 |
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-01 : 19:31:02
|
| The sql 2000 optimizer will (hopefully) determine the optimimum path for performance based on a whole lot of variables including table sizes and indexes. The order of the tables in the join clause doesn't really make any difference for much beyond readability. |
|
|
|
grrr223
Starting Member
30 Posts |
Posted - 2004-03-01 : 20:53:34
|
| While there may not be a clear cut answer to your question 100% of the time, just keep this in mind.Learn how to use the Query Analyzer!!!Use it to tweak the order in which things get evaluated. When performing joins or most anything for that matter, try to eliminate the largest number of rows first. This way all subsequent joins (or calculations or what have you) will take place on the fewest number of rows.Trust me on this, it can make a HUGE difference. I am working on the record set for my company's monthly customer statements. Well, to handle the mess that our accounting software and our ways of using it have created for me, the query is quite complicated with many joins on many tables and sums and derived tables and Unions and all that fun stuff.I moved the Where clause in my query from evaluating after my joins to evaluating before (using a derived table) and my query now runs in 7 seconds instead of 30. Doing something as simple as changing the order of your Case statements so that the expression that is most often true evaluates first can save tons of time. Or converting your input parameters before comparing them to an expression, thus making the conversion once instead of on each row, these things make tremendous differences.It can actually be kinda fun first getting your query to work (we all know how ugly a "working" query can look) and then making it pretty or seeing how few lines you can get it to run in, or how fast and efficient you can get it. A little experience, and the query analyzers help can go a Loooong way. Enjoy! |
|
|
|
|
|
|
Basic join question