Querying a View vs. Calling a UDF that does the Same Thing

Question

Querying a View vs. Calling a UDF that does the Same Thing

danedickey

Newbie

Points: 4
More actions
November 14, 2005 at 4:29 pm

#88492

I am running SQL Server 2k SP4, and I have a linked server to a DB2 database. In my SQL Server database, I have a view (dbo.ActivityM1_vw) that selects a few important columns from a table in DB2 that has approximately 11.5 million rows. When I run the following query against the view in QA:
Select Top 1
OperatorID
From dbo.ActivityM1_vw
Where ItemNo = 'P7232842'
And Lower(ActivityType) = 'closed'
Order By ActivityID
I get the value I expect, and it runs in less than 1 second. My problem is when I use a variable for the ItemNo, the run time becomes > 30 MINUTES.
Declare @ItemNo varchar(12)
Set @ItemNo = 'P7232842'
Select Top 1
OperatorID
From dbo.ActivityM1_vw
Where ItemNo = @ItemNo
And lower(ActivityType) = 'closed'
Order By ActivityID
When I compare the execution plans, the first query (with the hard-coded item number) passes the item number to DB2 in the initial SELECT. In the second query, the exec plan shows the filter on ItemNo separate from the initial SELECT sent to DB2 (i.e. its trying to get all 11.5 mil rows, then filter).
I also have a UDF that does the same query, taking the ItemNo as an input parameter. It runs > 30 minutes as well.
I am not sure what I need to do to get the UDF, etc. to pass the ItemNo to DB2 for the initial query? Any help would be appreciated.
Dane Dickey

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

danedickey Newbie Points: 4 More actions · Answer 1

I think I found the solution. I had to change the "provider options" on my linked server to get it to use parameterized queries. The execution plan shows the input param, and the runtime is now sub-second going through the UDF.

Dane