February 12, 2009 at 11:53 am
I have been asked to improve performance in a process of over 200 stored procedures and functions. I put in an audit trail and have been able to narrow it down to a UDF that looked more than a little funny:
selecttop 1 @serviceIncentiveType = ServiceIncentive.Type,
@serviceIncentiveRate = ServiceIncentive.Rate
FROM dbo.AccountEntry
INNER JOIN dbo.EntryAccountSection
ONdbo.AccountEntry.SectionID = dbo.EntryAccountSection.AccountSectionID
RIGHT OUTER JOIN dbo.ServiceIncentive
INNER JOIN dbo.ServiceIncentiveEntry
ONdbo.ServiceIncentive.EntryID = dbo.ServiceIncentiveEntry.EntryID
INNER JOIN dbo.Entry
ONdbo.ServiceIncentiveEntry.EntryID = dbo.Entry.ID
ONdbo.EntryAccountSection.EntryID = dbo.Entry.ID
where ServiceIncentive.ZoneID = @zoneID
and ServiceIncentiveEntry.IncentiveServiceID = @incentiveServiceID
andEntry.AgreementID = @agreementID
andServiceIncentive.MinWeight <= ceiling( dbo.GetLargerDecimal( @Weight, dbo.GetMinWeightForService( @incentiveServiceID ) ) )
andAccountEntry.Account = @CarrierAccount
order by ServiceIncentive.MinWeight desc
Yes, this is part of a UDF, that calls another UDF that calls another UDF in the WHERE clause. What had me concerned were the INNER JOINs after the RIGHT OUTER JOINs and the multiple ON statements.
My first thought was to change later INNER JOINs to RIGHT OUTER JOINs and change the second ON to an AND. Are there any risks to my approach? The original author said it was created with a SQL 2005 Query Wizard.
TIA for any help.
Andre
February 12, 2009 at 12:03 pm
You might be able to get some performance improvement out of something like this:
declare @MinWeight float
select @MinWeight =
ceiling(
dbo.GetLargerDecimal(
@Weight,
dbo.GetMinWeightForService(
@incentiveServiceID ) ) )
select top 1
@serviceIncentiveType = ServiceIncentive.Type,
@serviceIncentiveRate = ServiceIncentive.Rate
FROM dbo.AccountEntry
INNER JOIN dbo.EntryAccountSection
ON dbo.AccountEntry.SectionID = dbo.EntryAccountSection.AccountSectionID
INNER JOIN dbo.Entry
ON dbo.EntryAccountSection.EntryID = dbo.Entry.ID
INNER JOIN dbo.ServiceIncentiveEntry
ON dbo.ServiceIncentiveEntry.EntryID = dbo.Entry.ID
RIGHT OUTER JOIN dbo.ServiceIncentive
ON dbo.ServiceIncentive.EntryID = dbo.ServiceIncentiveEntry.EntryID
where ServiceIncentive.ZoneID = @zoneID
and ServiceIncentiveEntry.IncentiveServiceID = @incentiveServiceID
and Entry.AgreementID = @agreementID
and ServiceIncentive.MinWeight <= @MinWeight
and AccountEntry.Account = @CarrierAccount
order by ServiceIncentive.MinWeight desc
And the From clause is definitely more readable that way. Try it, see if I've broken or improved anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 12:27 pm
I've used the separate call to calculate @MinWeight immediately. That was a "well, duh" moment for me, that I should have seen earlier. Thanks.
I'm not certain about where you changed the RIGHT OUTER JOINs to INNER JOINs. The order of the tables appeared to be important.
Is there any issue with the multiple ONs?
Again, thanks for this.
Andre
February 12, 2009 at 12:29 pm
I didn't actually change any of the joins, I just moved them around so they each table joins to the table immediately before it in the list. Doesn't affect functionality at all, just makes it a little easier to follow what's going on.
The multiple ONs were just a layout problem, not an actual logic problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 2:20 pm
Thanks for this. I'll move it into production.
February 13, 2009 at 7:44 am
Whoa! I'm assuming you've tested it thoroughly first, right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2009 at 2:22 pm
I had a long discussion with the client, that helped clarify the relationship on the tables, and it worked out fine.
Part of my original question was the appearance of multiple ONs in the JOIN condition. It wasn't flagged as an error when executing. What are the consequences of this? Isn't this just the same thing as an AND after an ON?
My concern is I've found many cases of this in view definitions and SELECTs within stored procs.
TIA
Andre
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply