December 28, 2006 at 8:53 am
December 28, 2006 at 9:08 am
Are you taling about a derived table??
Select dta.* from (select * from dbo.SysObjects Where Status >=0) dta
If not I'd strongly suggest you let the optimiser figure out the fastest access path for you!!
What are you trying to accomplish exactly?
December 28, 2006 at 9:24 am
trying to improve performance of query
several multi million row tables joing together want to experiment with join order structure.
i thought you could pre join certain chunks of the from clause
somethig like
select *
from table 1
join table 2
joni ( table3
join table4
join table5
)
join table6
so the 3,4 and 5 are joined together first before they are joined to the rest of the query.
December 28, 2006 at 9:29 am
Select a.*, b.*, c.* from
(a inner join b on a.key = b.key) inner join c on c.key = a.ckey
Russel Loski, MCSE Business Intelligence, Data Platform
December 28, 2006 at 9:43 am
December 28, 2006 at 9:56 am
Just a minor correction to your query:
select *
from table1
join (table2 join table3 on table2.col = table3.col)
on tbale2.col = table1.col
Russel Loski, MCSE Business Intelligence, Data Platform
December 28, 2006 at 11:34 am
Why purchase a SQL RDBMS with a query optimizer that does all this for you, if all you're going to do is try to force the joins yourself ?
Wrapping certain tables in parentheses does not change what the optimizer does.
If you think you can do a better job, take 2 tables at a time, and select from them into a temp table or table variable, then join that to the next table. Etc etc.
Alternatively, try to figure out why the optimizer isn't doing things the "right" way. Investigate indexes. Update statistics.
December 28, 2006 at 4:54 pm
SQL 2000 does not have a perfect query optimizer.
My query is against a tera byte plus of data and the joins in question contain millions of rows. I have already created the relavent indexes and have tried bunging results in table variables and tables in temp db. This was one last long shot and i couldnt remeber the syntax.
One interesting thing i dicovered was that SQL seems to prefer Index scans over index seeks once the tables concerned get into millions of rows. Any ideas why this is or if subverting this behavior is possible/desirable?
December 28, 2006 at 5:36 pm
While I don't pretend to know what are the exact limits of the optimiser to switch from seek to scan, I can say with some certitude that it will always choose a seek when the seek is less costly than the scan (not an automatic choice here...).
Could we see the query in question with the tables DDL?
December 28, 2006 at 7:03 pm
Why nobody mentioned query hints yet?
And if you use more than 10% of rows in table (either for join or to return in resultset) there is no point to use index seek. Because if index structure it will take more time to seek than to scan.
The only case when index seek will be used is when you seek for a continuous range and corresponding index is clustered.
_____________
Code for TallyGenerator
December 28, 2006 at 7:26 pm
Why, because this is really the last ditch effort for me. And I'm not out of other solutions yet.
December 29, 2006 at 9:01 am
Sergiy is right, both about the index scans and the possibility of using hints. There is a FORCE ORDER option you can use which tells SQL server to evaluate your joins in the order specified in the query.
I have heard, anecdotially, that you can also effectly force SQL server to evaluate a subquery seperatly by using SELECT TOP 100 PERCENT in your subquery. In this way, perhaps you can effect a more desirable query plan. I suggest you play with both of these ideas and see hwo they work out for you (and let the rest of us know)
I'm curious, when was the last time you defragged these large tables? Is this a Normalized schema, a Star schema?
SQL guy and Houston Magician
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply