Need to optimize UDF 1

[fredodman]

Hi there!

I have a query that uses two UDF's. When I included the first one with help of George (thank you) it slowed down my query just a little bit. Now that I tried to write my own it takes close to two minutes instead of a few seconds so I gather I didn't do such a good job. Any help is of course greatly appreciated. Here is the query:



SELECT DISTINCT
ContactExtendedProperties.ActiveStatus,
Contacts.LastName,
Contacts.FirstName,
ContactExtendedProperties.WelcomeCard,
ContactExtendedProperties.BeforePic,
ContactExtendedProperties.AfterPic,
dbo.RO_GetCommaSeparatedNeedsTable(Contacts.ContactId) AS Description,
ContactExtendedProperties.CurrentStatus,
ContactExtendedProperties.SpacesCompleted,
ContactExtendedProperties.ClientLongevity,
MaxFollowupdate.Maxdate AS DateOfFollowup,
ContactExtendedProperties.ReceivedEval,
ContactExtendedProperties.OncancellationList,
dbo.RO_GetRemainingApps(Contacts.ContactId) AS RemainingApps
FROM Contacts
INNER JOIN ContactExtendedProperties ON Contacts.ContactId = ContactExtendedProperties.ContactId
INNER JOIN ClientStatus ON Contacts.ClientStatusId = ClientStatus.StatusId
INNER JOIN (
SELECT ContactId, MAX(DateOfFollowup) AS MaxDate
FROM ContactFollowup
GROUP BY ContactId) As MaxFollowupDate ON Contacts.ContactId = MaxFollowupDate.ContactId
INNER JOIN ContactFollowup ON Contacts.ContactId = ContactFollowup.ContactId
INNER JOIN RO_Users ON Contacts.PrimaryConsultantId = RO_Users.UserId
INNER JOIN ContactToNeeds ON Contacts.ContactId = ContactToNeeds.ContactId
INNER JOIN NeedsTable ON ContactToNeeds.NeedsId = NeedsTable.NeedId
WHERE Clientstatus.StatusDesc LIKE '%Client%'

Here is the first UDF, the one that worked fine.

CREATE FUNCTION dbo.RO_GetCommaSeparatedNeedsTable
(@ContactId int)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @Result nvarchar(100)
SELECT @Result = IsNull(@Result + ', ' + Description, Description)
FROM NeedsTable
INNER JOIN ContactToNeeds ON NeedsTable.NeedId = ContactToNeeds.NeedsId
WHERE ContactToNeeds.ContactId = @ContactId
RETURN @Result
END

It stores multiple values in one cell.

The second one, which slows the query something fierce is supposed to go to a table and look how many appointments each clients are booked for in the future. Here it is

CREATE FUNCTION dbo.RO_GetRemainingApps
(@ContactId int)
RETURNS int
AS
BEGINNDeclare @Result int
DECLARE @Date datetime
SET @Date = getdate()
SELECT @Result = Count(BeginDateTime)
FROM Calendar
WHERE ContactId = @ContactId AND Begindatetime > @Date
RETURN @Result
END

I'm tickled pink that my query is returning anything at all except "syntax error" but I would also like to use it which I can't in its current state. Thanks for looking and once again, any help is appreciated.

Fred

 

[RiverGuy]

That's a pretty simple UDF. So, you don't necessarily need a UDF. In addition to your existing joins, try using a derived table to see if it goes faster. For example:

SELECT ...
...
INNER JOIN 
  (SELECT ContactID, COUNT(BeginDateTime) AS Ct
   FROM Calendar
   WHERE BeginDateDate > GETDATE()
   GROUP BY ContactID) b
ON YourTable.ContactID = b.ContactID

 

[SQLSister]

River guy suggested what I was going to suggest. Derived tables are often much faster than user-defined functions.

WHERE Clientstatus.StatusDesc LIKE '%Client%'

This can be a real killer as well because without the first character, it will not use indexes to filter. Is there a way to redesign so that you don't have to serch this way? It would be better to have a column that you can search on without the wildcard search. Check out the execution plan and see if it is doing a table scan. Especially think about changing your structure if this a common way to find the information you want from that column. Better to separate it out on insert than continually have to search with a wildcard as the first character.

"NOTHING is more important in a database than integrity." ESquared

 

[fredodman]

Thanks for the quick reply guys!

I tried the derived table and it executed much faster but the problem is that the people that does not have any future appointments scheduled does not show up. The reason I created the UDF was to still include people who had previous appointments. I'd like these people to still be in the query with a 0 in the column for how many future appointments customers have. Any thoughts?

Also, I'll look into the wildcard issue. I'm very new to this (just a few weeks with SQL Manager) so don't laugh when I'll tell you that I have no clue on how to check the execution plan. I'd like to learn though so if you have a minute to ellaborate I'd be very grateful.

Thanks again guys.

Fred

 

[RiverGuy]

Use a LEFT OUTER JOIN instead of your INNER JOIN for the derived table, and you case use ISNULL on the column you are getting from it if needed.

 

[fredodman]

Have to buy some basic books on how to join tables I think. Took you all of what, ten seconds to figure that one out Riverguy? It works like a charm and I'm down from two minutes to ten seconds for my query to execute. I'm very grateful you should know, but a little emberrased too. Thank you so much for taking the time to help a new guy out.

Fred

 

[RiverGuy]

You're welcome. If you're looking for a beginner's sql book, I usually recommend SQL: A Beginner's Guide by Forrest Houlette.

 

[fredodman]

I'll look for it for sure. Thank you once again!

Fred