Select Query - need optimization

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 2008 Forums

Transact-SQL (2008)

Select Query - need optimization

Author

Topic

gudipadi
Starting Member

1 Post

Posted - 2012-01-14 : 09:01:23

Table_1(custid,store,amont)
Insert into Table_1 values (1,store1,1000)
Insert into Table_1 values (2,store2,2000)
Insert into Table_1 values (3,store3,3000)
Insert into Table_1 values (4,store4,4000)

Table_2 (store,product,col3,col4...)
Insert into Table_2 values(store1,prod1,value)
Insert into Table_2 values(store1,prod2,value)
Insert into Table_2 values(store1,prod3,value)
Insert into Table_2 values(store2,prod1,value)
Insert into Table_2 values(store2,prod5,value)
Insert into Table_2 values(store3,prod4,value)
Insert into Table_2 values(store3,prod2,value)
Insert into Table_2 values(store3,prod6,value)
Insert into Table_2 values(store4,prod6,value)
Insert into Table_2 values(store4,prod1,value)
Insert into Table_2 values(store4,prod2,value)

select custid,store,Amount
from Table_1 where store not in (select store from table_2 where product != 'prod6')
custid, store, amount
---------------------
1,store1,1000
2,store2,2000

I need a query to get the same output in a more optimized way....because i have 500 millions in Table_1 and 700 millions of rows in Table_2

Please help me out it is a production issue, need to fix this asap

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 09:41:05

[code]
select custid,store,Amount
from Table_1 t1
where not exists(select 1 from table_2 where product != 'prod6' and custid= t1.custid)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources