April 15, 2008 at 8:49 am
Hi Folks,
I have a query like this:
select 1 from database where id in (select id from database2 ....)
I am having no result(null) from the query for some instances so I started to investigate the issue.
The thing I don't understand is: it takes me 4 minutes to run the inner query(select id from database2 ....), however, it immediately gives me the result (null) of the whole query.
How this happens? Can't I suppose that the whole query will take more time to finish than the inner query?
Thanks.
April 15, 2008 at 9:09 am
could you post the table structures that your queries relate to and some sample data?
Wouldn't using 'select 1 from' just return you a coulm of 1's ?
Also 4 mins is a long time for a select query to run, how big is the table?
April 15, 2008 at 9:23 am
Thanks for your reply.
The two tables I used in my query(no, actually it was written by others, I am just doing the cleanup :crazy:) have the following number of records:
3891154
88616040
The select 1 works fine because I just need to know if there is any match or not.
April 15, 2008 at 10:12 am
Try re-writing the query to use a join, rather than a sub-query as this will be more efficent.
Do you have the appropriate indexes setup on the tables?
April 15, 2008 at 11:07 am
I'd replace the whole thing with 2 "exists" queries.
where exists
(select 1
from table1
where exists
(select 1
from table2
where id = table1.id))
Of course, it'll need something in the outer query that ties table1 to it, otherwise it'll return positive if there are ANY rows in table1 and table2 that match, regardless of what's in the rest of the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 8:38 am
Thanks for all of you guys' reply.
I am just interested in why a query like
select * from asdf where id in (select id from fasd)
shouldn't the total running time be longer than the inner query? common sense, isn't it?
But like I said in my query, the inner query takes 4 minutes to run, once I run the whole query, it takes only 1 second to finish, isn't this odd?
Thanks.
April 17, 2008 at 8:43 am
I dont know if there is an easy answer, maybe some one else can help?
but these prooblems can at times be worked out by looking at your execution plan.
have you compared the plan between running the sub-query and the whole query?
April 17, 2008 at 9:56 am
the sub query's execution plan is as following:(takes 4 minutes to run the query)
Physical operation: Clustered Index Seek
Logical operation: Clustered Index Seek
Estimated row count: 90,670,440
Estimated row size: 66
Estimated I/O cost: 600
Estimated CPU cost: 99.7
estimated number of executes: 1
Estimated cost: 62%
Estimated subtree cost: 699
The whole query's execution plan is as following:(takes 1 second to run the query which include the 4 minutes sub query)
Physical operation: Clustered Index Seek
Logical operation: Clustered Index Seek
Estimated row count: 1
Estimated row size: 1165
Estimated I/O cost: 0.00312
Estimated CPU cost: 0.000158
estimated number of executes: 561.99243
Estimated cost: 62%
Estimated subtree cost: 0.649
How can I find more clue from them?
Thanks lots.
April 17, 2008 at 9:59 am
Look at the execution plan. SQL may have converted the IN to an inner join, and when there isn't a match, it returns quickly.
😎
April 17, 2008 at 10:04 am
halifaxdal (4/17/2008)
How can I find more clue from them?
Thanks lots.
Run them, and click on the 'Display Estimated Execution Plan' button on the tool-bar.
This will bring up some icons/graphics showing you how the query was executed and what exactly is taking all the time.
Post them up on here if you need any further help with them.
April 17, 2008 at 10:10 am
Look at the execution plan. SQL may have converted the IN to an inner join, and when there isn't a match, it returns quickly.
That is interesting. I thought SQL will create the result pool first for the IN clause, how do I know if the SQL has already convert IN into inner join?
April 17, 2008 at 10:11 am
Hi Steve,
Did you see that what I just posted is the execution plan's running result, I just manually typed it in here.
April 17, 2008 at 10:25 am
Sorry I skimmed over that was expecting some graphics or something, sorry about that.
As Lynn said It looks like it is not making a match at all and the query returns quickly.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply