x

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/

more ▼

asked Feb 01, 2010 at 08:09 AM in Default

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Feb 01, 2010 at 10:08 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.8k 19 21 74

you're not wrong. Have you written a book on it? I need to do some research to understand this more.
Feb 02, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 10:34 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 01, 2010 at 08:54 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

+1 - lookups in scalar UDFs do really suck - badly.
Feb 01, 2010 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, 2010 at 09:18 AM Fatherjack ♦♦
Under which Class event in profiler are they being shown ?
Feb 01, 2010 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, 2010 at 11:46 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x57
x10

asked: Feb 01, 2010 at 08:09 AM

Seen: 2247 times

Last Updated: Feb 01, 2010 at 11:49 AM