June 2, 2008 at 10:52 pm
Dear all,
I really don't know if any body meet the same problem like me or not which I have never met before.
These two query is the same except I select one more code than the first one.
I have try testing the same thing many time , it still shows the same result.
-- 30 code selected
-- This query take less than 10 seconds
SELECT DISTINCT p.code, a.classification_id, a.area_group, information, sort
FROM v_print_out_classification_area_information a INNER JOIN v_print_directory_listing p
ON a.classification_id = p.classification_id
WHERE a.area_group collate SQL_Latin1_General_CP1_CI_AS = p.area_group
AND p.code in (Select top 30 code from listing)
-- just 31 code selected
-- This query take nearly 13 minutes
SELECT DISTINCT p.code, a.classification_id, a.area_group, information, sort
FROM v_print_out_classification_area_information a INNER JOIN v_print_directory_listing p
ON a.classification_id = p.classification_id
WHERE a.area_group collate SQL_Latin1_General_CP1_CI_AS = p.area_group
AND p.code in (Select top 31 code from listing)
* how on earth this problem happen? is it SQL server Problem?
Waiting for kind reply.
Regards,
June 2, 2008 at 11:37 pm
Post the different query plans (attach as xml-based *.sqlplan files).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 3, 2008 at 5:41 am
Definately post the execution plans.
Even without them, changing the collation on the fly is likely to prevent the use of an index on the column. IN clauses act as cursors, comparing each row, one at a time, to the result sets offered. It's possible that TOP 30 (without an order by, what exactly do you expect to get back from that query) was able to resolve itself as a JOIN in the execution plan or take advantage of an index and TOP 31 was just that much too much and it resulted in a SCAN of all the data.
What exactly are you trying to do with the TOP 30? You should be able to make that a derived table and perform an inner join against it. It should give you much better performance, assuming you've got good indexes in place.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 3, 2008 at 5:51 am
How many rows do the 2 queries return?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply