c53643f7   
   XPost: microsoft.public.sqlserver.server   
   From: samalex@gmail.com   
      
   On Wed, 10 Jun 2009 03:55:56 -0500,  wrote:   
      
   > Our new clients have a mature database but a new requirement to obtain   
   > some summary data.  Frankly, I don't think it's terribly well   
   > designed, but we're stuck with it.   
   >   
   > They want us to script a collection of views, and we've done the first   
   > tranche of work.  They are now quibbling about the cost of the second   
   > batch, and their DBA has come back with some suggestions for keeping   
   > down costs, specifically that we "could use user defined functions to   
   > replace any replace, convert or case statements and indeed the look   
   > ups from the lookup tables".   
   >   
   > Isn't there a performance implication with UDFs as compared, say, to   
   > in-line case/convert etc.?   
   >   
   > What do you mavens think?   
   >   
   > Edward   
   >   
      
      
   Hi Edward,   
      
   I run into this from time to time where someone adds a UDF to a query that   
   returns many rows.  UDF's run in a seperate process, so each time you call   
   it you're creating a new Process for SQL to finish before it continues.   
   Due to this it really depends on how many records will be returned on how   
   the UDF will impact the query.   
      
   For example if you have a UDF that gets a customer's Zip Code you might   
   use something like this:   
      
   select CustomerName,   
           dbo.ufnGetZip(CustomerID.CustomerTable) as Zip  --> Gets   
   AddressTable.AddressZip   
    from   CustomerTable   
   where  CustomerID = 123   
      
   If you run this for one customer it'll be speedy but if you run it for   
   50,000 customers it has to run the dbo.ufnGetZip() UDF 50,000 times which   
   will greatly increase the run time of the query.  A simpler way would be   
   this:   
      
   select CustomerTable.CustomerName,   
           AddressTable.AddressZip   
    from   CustomerTable   
           left outer join AddressTable on   
                CustomerTable.CustomerID = AddressTable.CustomerID   
      
   ... which given your indexing is setup somewhat normal should come back   
   quickly whether you're hitting 1 or 100,000 records.   
      
   So back to your question, if you can void UDF's in such queries I would do   
   so, whether it's using a case, join, or whatever has to be done.  Just   
   remember that everytime SQL has to spawn a new process to do something,   
   whether it be a UDF or whatever, your query speed will suffer.   
      
   Others can chime in with other suggestions, but this is based on my   
   experience.   
      
   Sam Alex   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)