December 29, 2009 at 7:19 pm
I have a union all statement inside a nested query. As you can see, the below query doesn't need to scan the first table "TABLE_1" (as the type value in the where clause is applicable only for TABLE_2). But when i execute this query in SQL 2000 (Build 8.0.2239), it scans both the tables. I have attached the execution plan (screenshot) for your reference.
The same query works as expected, in SQL 2005 (i.e, The SQL engine scans only the second table "TABLE_2" to get the result).
SELECT TOP 1000
[Extent1].[OrderID] AS [OrderID],
[Extent1].[Status] AS [Status]
FROM (
SELECT
HDR.OrderID AS OrderID,
'TYPE_1' AS Type,
HDR.Status AS Status
FROM
TABLE_1 HDR (NOLOCK)
UNION ALL
SELECT
HDR.OrderID AS OrderID,
'TYPE_2' AS Type,
HDR.STATUS AS Status
FROM
TABLE_2 HDR (NOLOCK)
) AS [Extent1]
WHERE
[Extent1].[Type] = 'TYPE_2'
and [Extent1].status = 'OKAY'
I have seen a KB article (http://support.microsoft.com/kb/812798) that i believe is close to my issue. But this issue had already been fixed with SQL 2000 SP4.
Do you guys have any idea?
December 29, 2009 at 7:30 pm
Interesting though.....:cool:
This might explain it
Due to the fact that the sub-query in a correlated sub-query can be executed for every row returned in the outer query, performance can be degraded. With a sub-query, performance is totally dependent upon the query and the data involved. However, if written efficiently, a correlated sub-query will outperform applications that use several joins and temporary tables.
SOURCE: http://articles.techrepublic.com.com/5100-10878_11-6100447.html
| If in Doubt...don't do it!! |
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply