Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sub-select performance
Message
From
01/03/2005 15:23:50
John Deupree
Long Term Care Authority
Tulsa, Oklahoma, United States
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Title:
Sub-select performance
Miscellaneous
Thread ID:
00991746
Message ID:
00991746
Views:
57
I have a stored proc that uses a UDF. The UDF calculates the number of business days between 2 dates. The result set is dependent on if the UDF returns > 14 days. I know that using the UDF in a where clause would cause unacceptable performance, so I did the following:
select *
from 
(
select some data
from some tables
to get about 300 rows
) x
where dbo.udf_countBusinessDays(x.date,getdate()) > 14
I figured that the sub-select would return about 300 rows and then the UDF would take no time at all. However, it takes the same amount of time as using the UDF in the where clause of a query without the sub-query (1 min. +). The query plans for both are virtually identical.

Can someone explain to me how the sub-select used as a table is working here?

TIA
John
Next
Reply
Map
View

Click here to load this message in the networking platform