orig. post bounced with "body too long", sending again, will probably
duplicate....
Im not a query analyzer expert, and this wasnt a very scientific experiment,
but I thought people might find these server trace results interesting.
Test1: approx 10,000 row table
Testing: Single Sub Select with IN clause
SELECT TOP 20 *
FROM objproduct
WHERE product_ID not in (SELECT TOP 10020 product_id
FROM objproduct
ORDER BY product_id)
ORDER BY product_id
Results:
Duration: 78
CPU: 78
Reads: 249
Testing: Triple Sub Select
SELECT *
FROM (SELECT TOP 20 *
FROM (SELECT TOP 10020 *
FROM objproduct C
ORDER BY C.product_id ASC) B
ORDER BY B.product_id DESC) A
ORDER BY product_id
Results:
Duration: 175
CPU: 0
Reads: 67
So it would seem that at around 10,000 rows, the IN clause is quicker, but
harder work, whereas the triple select is slower, but less work?
Test 2: approx 650,000 row table
The results seemed to vary more here, so I have put them in as ranges.
Testing: Single Sub Select with IN clause
SELECT TOP 50 *
FROM logs
WHERE logid not in (SELECT TOP 600000 logid
FROM logs
ORDER BY logid
ORDER BY logid
Duration: 7-253
CPU: 7-253
Reads: 206
Testing: Triple Sub Select
SELECT *
FROM (SELECT TOP 50 *
FROM (SELECT TOP 600000 *
FROM logs C
ORDER BY C.logid ASC) B
ORDER BY B.logid DESC) A
ORDER BY logid
Duration: 11 - 251
CPU: 0-16
Reads: 103
As the durations seemed to even out at around these numbers, while the CPU
usage of the triple select was consistently very low, and the reads were
consistently half that of the IN clause, the only conclusion that I can draw
is that with simple sql at least, the triple select is better?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219568
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
