Efficient way than IN statement

I have two tables JDECurrencyRates and JDE Currency Conversion

I want to insert all the records all the records from JDECurrencyRates to JDECurrencyConversion that does not exists in JDECurrencyConversion table. For matching I am to use three keys i.e. FromCurrency, TO Currency and Effdate

To achieve this task i wrote the following query

INSERT INTO PresentationEurope.dbo.JDECurrencyConversion(Date,FromCurrency,FromCurrencyDesc,

ToCurrency, ToCurrencyDesc, EffDate, FromExchRate, ToExchRate,CreationDatetime

,ChangeDatetime)

(SELECT effdate as date, FromCurrency, FromCurrencyDesc, ToCurrency, ToCurrencyDesc, EffDate,

FromExchRate, ToExchRate, GETDATE(),GETDATE() FROM MAINTENANCE.DBO.JDECURRENCYRATES

WHERE FROMCURRENCY NOT IN (SELECT FromCurrency FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION)

OR TOCURRENCY NOT IN (SELECT TOCURRENCY FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION)

OR EFFDATE NOT IN (SELECT EFFDATE FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION))

Can any one suggest me the better way to accomplish this task or this query is OK (or efficient enough)

Hi

I think a more efficient way would be to use LEFT OUTER JOIN on your 3 keys and checking for missing values

INSERT INTO ....
SELECT .....
FROM JDECurrencyRates cr
LEFT OUTER JOIN JDECurrencyConversion cc
ON cr.FromCurrency = cc.FromCurrency
AND cr.TOCURRENCY = cc.TOCURRENCY
AND cr.EFFDATE = cc.EFFDATE
WHERE cc.FromCurrency IS NULL

Any field should do in the WHERE clause

NB.
|||

thanx very much for providing efficient way

yes! i have tried and this works fine

|||

Using LEFT JOIN with IS NULL is not the efficient way. NOT EXISTS is the fastest way to perform these type of checks (absence or existence of rows). SQL Server 2000 & 2005 will typically generate the same plan for NOT IN / NOT EXISTS and IN/EXISTS queries. But using EXISTS/NOT EXISTS is always safer because you may not get into situation where you will get wrong results (in case of NOT IN/IN) due to NULL values. The use of LEFT JOIN with NULL check uses more operators than a NOT EXISTS query.

Compare the estimated query plan costs of the queries below:

-- Get list of authors with no titles:

select *
from pubs.dbo.authors as a
where not exists(select * from pubs.dbo.titleauthor as ta
where ta.au_id = a.au_id)

select *
from pubs.dbo.authors as a
left join pubs.dbo.titleauthor as ta
on ta.au_id = a.au_id
where ta.au_id is null

go

-- Get list of tables with no indexes:

select t.object_id
from sys.tables as t
where not exists(select *
from sys.indexes as i
where i.object_id = t.object_id)

select t.object_id
from sys.tables as t
left join sys.indexes as i
on i.object_id = t.object_id
where i.object_id is null

go

And depending on your indexes, data and columns referenced in your queries the performance could be even worse. At best, the LEFT JOIN with IS NULL check approach will be as close to the NOT EXISTS query. So you should write your INSERT...SELECT like:

INSERT INTO PresentationEurope.dbo.JDECurrencyConversion

(Date,FromCurrency,FromCurrencyDesc,

ToCurrency, ToCurrencyDesc, EffDate, FromExchRate, ToExchRate,

CreationDatetime ,ChangeDatetime)

SELECT effdate as date, FromCurrency, FromCurrencyDesc

, ToCurrency, ToCurrencyDesc, EffDate, FromExchRate

, ToExchRate, GETDATE(),GETDATE()

FROM MAINTENANCE.DBO.JDECURRENCYRATES as j

WHERE NOT EXISTS(

SELECT *

FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION as j1

WHERE j1.FromCurrency = j.FROMCURRENCY

AND j1.TOCURRENCY = j.TOCURRENCY

AND j1.EFFDATE = j.EFFDATE

))

|||

(excuse bad english)

I think that the problem resides in the absent of association between "from..." and "toex" tables. Maybe the day (date) would be the same. Did the number os records inserted exceeded ?

Marcelo