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 |
|
markricard
Starting Member
2 Posts |
 Posted - 2007-04-20 : 16:56:56
|
| Hello,I am attempting to optimize the query below for a count based on a triple join of tables:select count(distinct a.id) from ATABLE a inner join BTABLE b on a.id=b.a_id inner join CTABLE c on b.c_id=c.id where (a.foo='8a5a5c4a1123cc36011123cc5eee0035') and ((c.faa='poNumber' and b.fee='1234'))I have setup indexes on the id relationships in the inner joins and I have also setup indexes on the a.foo, c.faa and b.fee columns.Does anyone have a suggestion on how to write a better query? (Cannot be a stored procedure) The current query takes about 8 seconds to complete on 35k records. I would have expected a second or less.Big thanks in advance,Mark |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-20 : 17:00:55
|
| If your index's have accuretly been setup and you need to incude information from all 3 of those tables, possibly look into Normalizing the data (Adding additional columns to hold information you are linking the table for in order to avoid joining additional tables). Other then this, you may want to look into amping up the hardward on the server.Unfortuantly there are very few options on that query. |
 |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-04-20 : 17:02:51
|
| Try this query and see if it makes any difference in the speed:select count(distinct a.id) from ATABLE a inner join BTABLE b on a.id=b.a_id and a.foo='8a5a5c4a1123cc36011123cc5eee0035' and b.fee='1234'inner join CTABLE c on b.c_id=c.id and c.faa='poNumber'I simply placed the conditions from the WHERE clause into the JOIN clause.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2007-04-20 : 17:53:41
|
| You didn't mention if the indexes on the relationship ID columns were the clustered indexes or not. Make sure each table has a clustered index - like those ID columns. If you don't currently have clustered indexes, after making them, update statistics.Does the database get many writes? If there are lots of writes happening, you may wanna rebuild the indexes more often (weekly?) to keep them defragmented./jeff |
|
|
|
|
|
|
Ideas for a faster count?