SQL Server Performance Forum – Threads Archive

Performance problem with CAST

Hi All I’ve inherited a pretty badly written stored procedure that’s used for a report on a ASP web application. I’ve isolated the particular lines of code that make this query go for about 30 seconds. Any help in providing an alternative to this code would be great. The code sample below is abbreviated but it should contain all the data you need to help me out. OK here we go:
SELECT Period.Name AS Interval, Period.PeriodID AS PeriodID, (SELECT ISNULL(SUM(CASE WHEN InitiativeFinancial.CategoryID = 1 THEN Amount
WHEN InitiativeFinancial.CategoryID IN (2, 3, 4, 5, 11, 12, 13, 14) THEN -Amount
ELSE 0 END), 0)
FROM vwReportingInitiatives
JOIN Initiative ON Initiative.InitiativeID = vwReportingInitiatives.InitiativeID
JOIN InitiativeFinancial ON InitiativeFinancial.InitiativeID = vwReportingInitiatives.InitiativeID
JOIN InitiativeValue ON InitiativeValue.ReferenceID = InitiativeFinancial.InitiativeFinancialID
JOIN Period ValuePeriod ON ValuePeriod.PeriodID = InitiativeValue.PeriodID
JOIN @CommitteesSelected cs ON Initiative.IGApprovalCommitteeID = cs.CommitteeID
JOIN (SELECT * FROM @SpendCategoriesSelected UNION SELECT * FROM @BenefitsSelected) scs on InitiativeFinancial.Type = scs.Type COLLATE Latin1_General_CI_AS
JOIN @FinancialCategoriesSelected fc ON InitiativeFinancial.CategoryID = fc.CategoryID
WHERE –InitiativeValue.PeriodID<=Period.PeriodID AND
(
— Added Value Period filter – Production Defect #15 – Jason Kretchmar
ValuePeriod.PeriodYear = @YearOfApproval
and
— NOT THESE LINES BELOW ARE THE PROBLEM LINES.
(
(LEFT(Period.Name,3)=’PRE’ AND ValuePeriod.Name = Period.Name )
OR
(LEFT(Period.Name,3)<>’PRE’ AND CAST(Right(ValuePeriod.Name,4) AS INT) <= CAST(Right(Period.Name,4) AS INT) ) )
)
AND
–vwReportingInitiatives.InitiativeYear = @YearOfApproval AND
vwReportingInitiatives.LatestApprovedPerYear = 1 ) AS BenefitCost FROM Period
WHERE Period.PeriodYear = @CurrentYear
The problem is that this period table stores years as Varchars (because of this bogus year called Pre-2005, Pre-2006 etc). I’m not sure how to get around this without modifying the schema which would mean potential regression testing of the application. I’d rather not do that so if there is a fix that I’m not aware of please help! I’m a C# developer so my tuning skills just aren’t there. There are indexes set on these columns but I don’t think it makes much of a difference given that the Period table has about 25 rows. Thanks in advance. jason

---

Perhaps replacing these two: LEFT(Period.Name,3)=’PRE’
LEFT(Period.Name,3)<>’PRE’ with Period.Name LIKE ‘PRE%’
Period.Name NOT LIKE ‘PRE%’ already helps. You could also try adding a formula column on the Period table (ValuePeriod is an alias, correct?). ALTER TABLE Period
ADD YearAsInt AS CAST(Right(Name,4) AS INT)
GO And add an Index on the formula column:
CREATE INDEX ixPeriodYear ON Period (YearAsInt)
GO If you’re not allowed to alter the table, then see if you can create a view, including the formula as a column, and then add an index on the view.

---

Thanks Adriaan Yes I’ve tried the LIKE/NOT LIKE and I didn’t seem to notice much but I’ll put it back and retest. Yes ValuePeriod is an alias for the Period table.
I could make a view or extend the table but I’d have to have two columns, one being the numeric value of the period (e.g. 2007) and another to indicate if its a "PRE" column, maybe a BIT. The definition of the Period table is Period ID – INT (PK)
PeriodName – VARCHAR(50) — the one that contains junk like "PRE 2007"
PeriodYear – INT — this is the year that the PeriodName is associated with. You can have many PeriodNames against a given year (e.g. PeriodYear = 2007, PeriodNames = Pre2007, 2008, 2009, 2010 etc)
HasQuarters – BIT — just to see if the PeriodName has quarters or not.
Anyway, I’ll have a think about it. thanks again for your help

---

For the leading characters ("PRE" or different) a regular index is already as good for searching as an extra BIT column would ever be. But it depends on the data … If not more than one-third of the rows have "PRE" as the leading characters, then an index will be better than a BIT column. If you can split the junk data (like "PRE 2007") into its constituents – IOW, normalization – and add a composite index on the split colums, that would be the ideal solution. But of course we don’t know how much control you have over the table design.

---

I’ve tried some things and I’ve noticed no change at all in the performance… I created a view with a BIT columns and INT column to store the "PRE" flag and year respectively. No difference. I then altered the table and added two new columns (based on the same as the view except they weren’t calculated, I manually entered the data) and created a composite index and I had no change in performance at all. I find it baffling that no solution makes any difference to the performance.. but then again, that’s why I’m not a development DBA! The real line in all of this is that is causing the problem is: (LEFT(Period.Name,3)<>’PRE’ AND CAST(Right(ValuePeriod.Name,4) AS INT) <= CAST(Right(Period.Name,4) AS INT) ) After all the alternatives its obvious that the CASTING AND THE LEFTING have nothing to do with it performing badly. It must be something to do with the <= which makes it compare the current rows ‘Year’ value to every other ‘Year’ value in the period table. I don’t know how I’ll get around that though.. anyway, thanks for your help. Just thought I’d let you know what I’ve done since last week.

---

Performance should depend on two things here: (1) the order of the columns in the index, which should be correct for the way that you are filtering – run your query through the Index Tuning Wizard, (2) the distribution of values on a column may be skewed – like 90% having the same value on the first column of the index, … and all in all, you need a clustered index on which to base the nonclustered ones. Can you give us the PK and indexes for this table? There are also some table variables, for which you may also need to set the PK.

---

Hey Adriaan Thanks for your help. Half way through today I decided to pull that part of the procedure out completely and calculate it manually with a UDF. Essentially the query was pulling back 3 things: TotalBenefit, TotalSpend and TotalBenefitCost. TotalBenefitCost was basically TotalBenefit-TotalSpend for the currentperiod and any previous periods(hence the nasty code). I decided to just pull back TotalBenefit and TotalSpend and put it into a temp table and then calculate the TotalBenefitCost through some code. I got the procedure down from around 30 seconds to about 4. Either way you gave me some good ideas and taught me a few things about SQL Server. Thanks so much for your help. I made my deadline and I doubt I would have got there if you didn’t help. cheers jason

---

Jason, Glad to have been of help. Be sure to come back and share here!

---

]]>