October 25, 2018 at 7:49 am
Hi,
We are facing a strange problem in our stp. The query is as below
IF EXISTS (SELECT 1 FROM tbl_A a WHERE NOT EXISTS (SELECT 1 FROM #tmpB b where b.Code = b.ParentCode and b.Category in (1,2,3) and a.MainParentId = b.MainParentId) )
If we execute the query without IF EXISTS then it gets executed immediately but with IF EXISTS it takes long time.
Also do note that statistics are ON on tbl_A but no primary key or index. and index is defined on #tmpB on all the columns mentioned in the query.
Also please note, similar checks of IF EXISTS are existing in the queries and they are getting executed immediately
Please guide.
Saumik Vora
October 25, 2018 at 8:39 am
Just to confirm what you are saying: Effectively your question is why does below query run quickly:SELECT 1 FROM tbl_A a WHERE NOT EXISTS (SELECT 1 FROM #tmpB b where b.Code = b.ParentCode and b.Category in (1,2,3) and a.MainParentId = b.MainParentId)
But a query like the below does not?IF EXISTS (SELECT 1 FROM tbl_A a WHERE NOT EXISTS (SELECT 1 FROM #tmpB b where b.Code = b.ParentCode and b.Category in (1,2,3) and a.MainParentId = b.MainParentId) )
SELECT 1;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2018 at 9:20 am
I would use a join on the two tables instead to simplify the logic and it would probably improve performance too.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
October 25, 2018 at 9:21 am
Hi,
Yes. thats correct
October 25, 2018 at 9:26 am
My previous answer was for Thom A.
@mark-3 Tillman yes i did change the query, but we would like to know the cause why its getting slow. also i saw irregular behaviour. Sometime it works fine but most of the time it is bad.
By the way tbl_A contains 600000 record at a time and #tmpB contains 20000.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply