Optimization needed - Unnecessary delay in SQL

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

SQL Server 2005 Forums

Transact-SQL (2005)

Optimization needed - Unnecessary delay in SQL

Author

Topic

vijayrawatsan
Starting Member

18 Posts

Posted - 2011-07-11 : 06:06:09

I have around 50,000 records in my tables A and B(each)
Now when I run this query it takes around 10 seconds.

SELECT A.id,A.Value
FROM 
(SELECT id,Value FROM db.Reference where Snapshot_id = 3) A
INNER JOIN 
(select id,Value from db.Reference where Snapshot_id = 2) B
USING (id,Value)

But when I run the same query with a count it takes around 10 seconds

SELECT count(*) from (SELECT A.id,A.Value
FROM 
(SELECT id,Value FROM db.Reference where Snapshot_id = 3) A
INNER JOIN 
(select id,Value from db.Reference where Snapshot_id = 2) B
USING (id,Value)) C

Is there any way to minimize time and optimize query???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-11 : 06:22:43

Are you using ORACLE? If so, post at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail