question

Fatherjack avatar image
Fatherjack asked

Cross database functions

While checking for long running queries I noticed a lot of entries where the query was "CREATE FUNCTION xyz ....".

After some investigation it appears that we have a function in databaseA that is taking a contactID and looking it up to get a staffNo in a table in a table in a different database. like this:

USE DatabaseA
GO      
CREATE funcGetStaffNo(@ContactID int)
returns INT
as
BEGIN
    DECLARE @StaffNo INT
    SELECT
        @StaffNo = [Staff_Number]
    FROM
        DatabaseB.dbo.[Employees] AS ea
    WHERE
        [ea].[EmployeeID] = @ContactID
    RETURN @StaffNo
END
GO

select c.ContactID,
dbo.funcGetStaffNo(c.ContactID),
c.ContactAddress 
FROM Contacts as c

Is there something in the background that actually creates the function in DatabaseB for the query to run?

I'm going to work on getting the function(s) (i expect there isnt just one case of this!) moved to the databases where the data they work on is stored in order to speed things up a bit but am interested in what is actually going on.

Thanks

[EDIT:] The script I'm using that shows this up is from here: http://scarydba.wordpress.com/2009/02/18/refining-the-missing-index-data/

t-sqlfunctionscross-database
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

You're calling the DMV's. Within them you can see individual statement calls. When you see them, it's usually based on CREATE PROCEDURE or CREATE FUNCTION. Confusing at first, but you'll get used to it. It's because the stuff in cache doesn't store the call itself, but stores the object called.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
No, I haven't written a book on this one. Although, there will be quite a bit about it when I update my execution plans book. Three other people that I read all the time that might help, Adam Machanic, Jonathan Kehayias and Tim Ford (who is writing a book on DMVs). These guys post lots of stuff on their blogs on just this sort of thing.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
yeah, don't sweat the CREATE statement. It's just referring to the object that way because it can't refer to how it was called. Whether or not the statement is rubbish... if that's the slowest operations, yep, it's a problem.
1 Like 1 ·
dave ballantyne avatar image
dave ballantyne answered

There is no function created in DataBase B , nor do i see how creating the functions within database B will improve performance. Scalar UDF's are notorious in their poor performance, as a matter of best practice you should remove all Scalar UDF's and replace with inline UDF's or Views. There is quite a large overhead in simply calling the UDF plus you have also stopped the optimizer providing a best query plan for the statement as a while.

4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - lookups in scalar UDFs do really suck - badly.
1 Like 1 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Yup, suckage noted and agreed but why is there a CREATE statement showing up in my execution of the optimiser script?
0 Likes 0 ·
dave ballantyne avatar image dave ballantyne commented ·
Under which Class event in profiler are they being shown ?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
I'm not using profiler I'm using Grant Fritchey's script. I'll find it and update the question
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.