February 17, 2009 at 12:21 am
Hi All,
I have the below senario.
Table Name:
create table fund
(
sec_id int,
symbol int
)
insert into fund
select 100,5555
union
select 100,6666
union
select 200,1111
union
select 200,2222
union
select 300,3333
union
select 300,4444
create table exception
(
symbol int,
err_cd int -- Note it should come between 1 and 1000
)
insert into exception
select 5555,20
union
select 5555,2
union
select 5555,30
union
select 4444,5
union
select 6666,20
My reqirement is, i want to get the symbol from fund table where err_cd not in (20,30).
Expected output:
200,1111
200,2222
300,3333
300,4444
Becuase 5555 & 6666 meet the above condition, so it will be excluded from the output.
Now coming to my issue, i used NOT IN & NOT EXISTS to do this,but it leads to performance isssue.
Execution Details:-
1) NOT IN : 50 seconds
2) NOT EXISTS : 14 seconds
so i used LEFT OUTER JOIN as below,
select sec_id,symbol
(select fund.sec_id,fund.symbol,'err_cd' = case when err_cd in (20,30) then err_cd*10000
when err_cd is null then 0
else err_cd
end
from fund left outer join exception
on fund.symbol = exception.symbol)a
where err_cd < 1000
output:
3003333
3004444
1005555 -- it should be excluded from the result set,because it has err_cd 20 & 30.
2001111
2002222
Note, I dont want to use any temporary table. I want to achieve this by using a single query.
We can use derived tables.
Can anybody help me to get the expected result? Also if my approach is wrong, please suggest me.
Inputs are highly appreciable!
karthik
February 17, 2009 at 2:03 am
In most of the cases, NOT EXISTS performs better than NOT IN & LEFT JOIN. Can you post the NOT EXISTS query, currently you are using?
It should look similar to...
SELECT*
FROMfund f
WHERENOT EXISTS( SELECT * FROM exception e WHERE f.symbol = e.symbol AND e.err_cd IN( 20, 30 ) )
--Ramesh
February 17, 2009 at 2:25 am
Ramesh,
i used exactly the same query.
Due to security reason,Here i posted sample table and records.
But the scenario which i posted is my requirement.
karthik
February 17, 2009 at 4:02 am
Karthik, please can you post your queries for
NOT IN & NOT EXISTS to do this,but it leads to performance isssue.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 17, 2009 at 4:05 am
I think your WHERE clause should look something like:
wherecasewhen err_cd in ( 20, 30 ) then err_cd * 10000
when err_cd is null then 0
else err_cd
end < 1000
Edit:
Or even better:
WHERECASEWHEN err_cd IN( 20, 30 ) THEN err_cd * 10000
ELSE COALESCE( err_cd, 0 )
END < 1000
Max
February 17, 2009 at 4:13 am
NOT IN:
Select sec_id, symbol
from fund
where symbol not in ( select symbol from exception where err_cd in (20,30))
NOT EXISTS:
Select sec_id,symbol
from fund f
where not exists ( select * from exception e where f.symbol = e.symbol and err_cd in(20,30))
karthik
February 17, 2009 at 4:19 am
LEFT JOIN:
SELECT f.sec_id, f.symbol
FROM fund f
LEFT JOIN exception e ON e.symbol = f.symbol AND err_cd IN (20,30)
WHERE e.symbol IS NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 17, 2009 at 4:56 am
SELECT Fund.* FROM Fund
LEFT JOIN exception ex ON fund.symbol = ex.symbol AND err_cd >= 20
WHERE ex.symbol is null
i hope it solve some how
February 17, 2009 at 10:10 pm
Thanks Chris and sqlmaster!
I am facing a starnge problem while executing the below query. if i execute it seperate it took just 1 second to complete the execution. If i put it in a procedure, it is taking 6 seconds. How ?
i tested it by using set statistic time on.
karthik
February 18, 2009 at 2:27 am
karthikeyan (2/17/2009)
Thanks Chris and sqlmaster!I am facing a starnge problem while executing the below query. if i execute it seperate it took just 1 second to complete the execution. If i put it in a procedure, it is taking 6 seconds. How ?
i tested it by using set statistic time on.
Where?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 18, 2009 at 3:35 am
I have executed the scirpt seperately.
It took 1 second only.
I put that statement in a procedure, it took 6 seconds.
karthik
February 18, 2009 at 3:51 am
If you post the sproc, Karthik, there's a chance that someone might figure out why it takes so long to run...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 18, 2009 at 4:23 am
create proc p1_t
as
begin
select a.sec_id, b.symbol, b.prtf_id, c.p_id,a.flag into #symb_map
from funds a, Mapping b, p_identifier c
where a.sec_id = b.symbol
and symbol_type = c.id_typ_cd
and a.sec_id = c.symbol
and c.id_typ_cd = 'MS'
and b.prtf_id = c.prtf_id
select count(distinct f.sec_id)
from #symb_map f
left join exception e on e.symbol = f.symbol
and err_cd in (9,22,23,24,34)
and feed_num = 23
where e.symbol is null
end
1st batch took only 1 second to complete the execution.
2nd batch only tool 6-7 seconds to complete the execution.
if i execute the above statements seperately like
select a.sec_id, b.symbol, b.prtf_id, c.p_id,a.flag into #symb_map
from funds a, Map b, identifier c
where a.sec_id = b.symbol
and symbol_type = c.typ_cd
and a.sec_id = c.symbol
and c.typ_cd = 'SR'
and b.prtf_id = c.prtf_id
1 second
Now i am executing the 2nd batch,
it also took 1 second only. so the total execution time is 2 seconds. But the procedure took 8 seconds.
Why and How? Please let me know.
karthik
February 18, 2009 at 4:45 am
Chris,
After creating the below index to the temp table, the query took 2 seconds only.
create nonclustered index id1 on #symb_map(sec_id,symbol)
Thanks for your help!
Thanks to all!
karthik
February 23, 2009 at 7:55 am
karthikeyan (2/17/2009)
Thanks Chris and sqlmaster!I am facing a starnge problem while executing the below query. if i execute it seperate it took just 1 second to complete the execution. If i put it in a procedure, it is taking 6 seconds. How ?
i tested it by using set statistic time on.
Kartik,
Try to recompile your SP and execute it again. I am sure it will solve your problem.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply