Query runs too slow - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query runs too slow

Author

Topic

mgerbi
Starting Member

2 Posts

Posted - 2008-02-08 : 16:44:39

I'm not a SQL expert, so please take that into conisderation when answering. First here is the query that does work but takes 6 min to run.
================================================================
Use Ortho
Go
Declare @TempTable Table
(
cmp_name varchar(50),
cmp_code varchar(15),
textfield9 varchar(5),
inv_dt datetime,
upszone varchar(3)
)
Insert into @TempTable (cmp_name, cmp_code, textfield9, history_Transactions.inv_dt, upszone)
Select cmp_name, cmp_code, textfield9, history_Transactions.inv_dt, upszone From cicmpy as C
Left Outer Join history_Transactions On history_Transactions.CusNo = C.cmp_code
Where history_Transactions.inv_dt between '08/1/2007' and '02/06/2008'
Group By cmp_name, cmp_code, Textfield9, history_Transactions.inv_dt, upszone
;

Select cmp_name, cmp_code, textfield9, upszone From cicmpy
Where cicmpy.cmp_code Not In (Select cmp_code From @TempTable)

==============================================================

Is there a better way of doing this? First I get all transactions for the last 6 months in the history_transaction table and join that with the customers table. I then want to find all customers (cicmpy table) that have NOT made an order in that timeframe meaning they should not be in the previous join.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 17:01:53

This should be more efficient:

Select  cmp_name,
        cmp_code,
        textfield9,
        upszone
From    cicmpy
        LEFT OUTER JOIN history_Transactions On history_Transactions.CusNo = C.cmp_code
                                                AND history_Transactions.inv_dt between '08/1/2007'
                                                                                and     '02/06/2008'
Where   history_Transactions.CusNo IS NULL

mgerbi
Starting Member

2 Posts

Posted - 2008-02-08 : 17:26:56

quote:

Originally posted by jdaman

This should be more efficient:

Select  cmp_name,
        cmp_code,
        textfield9,
        upszone
From    cicmpy
        LEFT OUTER JOIN history_Transactions On history_Transactions.CusNo = C.cmp_code
                                                AND history_Transactions.inv_dt between '08/1/2007'
                                                                                and     '02/06/2008'
Where   history_Transactions.CusNo IS NULL

Sweet!!
That worked great and only took 3 seconds! Your awesome..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources