Cross database functions

Question

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/

Answer 1

0

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.

answered Feb 01 '10 at 10:08 PM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

you're not wrong. Have you written a book on it? I need to do some research to understand this more.

Feb 02 '10 at 09:07 AM Fatherjack ♦♦

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.

Feb 02 '10 at 09:30 AM Grant Fritchey ♦♦

Cool, I'll go and stalk those guys...! Back to the question though, do I just ignore the CREATE bit in the query and accept that the function is just rubbish and needs fixing ..?

Feb 03 '10 at 07:33 AM Fatherjack ♦♦

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.

Feb 03 '10 at 09:27 AM Grant Fritchey ♦♦

@GrantFritchey OK, that's explained what I am seeing and resolved the concern over what is going on. Off to re-work the job now! Thanks

Feb 06 '10 at 10:34 AM Fatherjack ♦♦

(comments are locked)

Answer 2

0

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.

answered Feb 01 '10 at 08:54 AM

dave ballantyne
918 ● 1 ● 1 ● 4

+1 - lookups in scalar UDFs do really suck - badly.

Feb 01 '10 at 08:59 AM Matt Whitfield ♦♦

Yup, suckage noted and agreed but why is there a CREATE statement showing up in my execution of the optimiser script?

Feb 01 '10 at 09:18 AM Fatherjack ♦♦

Under which Class event in profiler are they being shown ?

Feb 01 '10 at 10:02 AM dave ballantyne

I'm not using profiler I'm using Grant Fritchey's script. I'll find it and update the question

Feb 01 '10 at 11:46 AM Fatherjack ♦♦

(comments are locked)

Cross database functions

Follow this question

Related Questions