Joins - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joins

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-25 : 05:07:06

Hi All,

I had a query like

Select * from Fact1
UNION
Select * from Fact2
JOIN

(Select top(col1),... from Fact3
INNER JOIN Table1
group by Col2
) ON....

The above one was executing in 5 minutes

I changed the inner query to say

Select Quarter,top(col1),... from Fact3
INNER JOIN View1
group by Quarter,Col2

I have made sure that the inner query is returning the same set of rows before and after modification. But still the modified query runs for ever. I am refering 2 databases(both SQL Server 2000 databases, on the same server- including the current). Please also note that I had a inner join to a table which I have changed to a view in the inner query.

I suppose this is due to the change in the execution plan. What should I do to make this work?

Any ideas?

Prakash.P
The secret to creativity is knowing how to hide your sources!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-04-25 : 06:27:54

What's the definition of View1?
What indexes are there on Fact3? On the tables referenced in the view?
What kind of row count are we looking at?

--
Gail Shaw

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-04-25 : 06:28:45

Also, can ou post the entire query please?

Select Quarter,top(col1),... from Fact3
INNER JOIN View1
group by Quarter,Col2

will not run

--
Gail Shaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources