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.


[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

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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 ♦♦

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 ♦♦
(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

avatar image

dave ballantyne
928 1 3 6

  • 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Feb 01, 2010 at 08:09 AM

Seen: 2803 times

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

Copyright 2017 Redgate Software. Privacy Policy