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 |
|
abuhassan
105 Posts |
 Posted - 2008-10-29 : 07:18:13
|
| HiI have a few questions with regards to JOINs does having alot of joins in your query slow down the quuery?Is it better to reduce the number of join statements in the query? If yes how would one go about doing that? would that affect the way we normalise teh database?thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-29 : 07:27:40
|
| yup. a nu,ber of joins can slow down a query. especially if you're joining to large tables. Also column on which you join a table also depends. |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-29 : 07:41:44
|
| It really depends. Denormalisation can be very effective for large, predominately read-only databases. For OLTP though it usually makes it much worse as your updates have much more work to do causing contention on more rows.Generally though I would advocate a proper normalised data model. Databases are designed to join and if you have a typical mix of read/write you gain more than you lose by normalising. For OLTP only denormalise when you have identified a specific performance problem and discounted other methods like materialised views, partitioning etc.Interestingly, the practice of using surrogate versus natural primary keys can also compound the problem of excessive joins by not letting useful data appear in multiple tables. A very simple example might be if you have an account number as a natural PK for accounts as opposed to a surrogate. The account number will need to appear in the related transaction table. This means you can get a list of transactions for an account directly with no join. Using a surrogate means you have to join to the main account table.Like all things, there are different techniques you need to apply depending on your situation and only experience and understanding will help. If there was 'one true way' it would be incorporated into the database engine and we would not need to have these discussions.Sorry there is no definitive answer, but I hope this helps. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-30 : 07:13:57
|
| In most situations you'll probably find that there are other performance enhancements that can be made in your queries that have a bigger impact than eliminating the number of Joins.Saying that, don't join uselessly of course.-------------Charlie |
|
|
|
|
|
|
JOINS and databse query Performance