January 31, 2006 at 5:38 pm
Hi guys,
I know this is an issue being discussed over and over again. But when i tried to do a search, the solutions I get aren't quite what I want... or maybe I didn't do the search correctly? anyhow, I appreciate any guides to point me to the correct direction!
Here's my problem:
I have a table that has a primary key and a series of repetitive columns that look something like:
Table tblYr ( yrID INT, yr1 INT, yr2 INT, yr3 INT ... yr20 INT)
The yrN columns I agree is quite lengthy and inflexible and I'll probably prefer a design like:
Table tblYr ( yrID INT, yrN INT, yrValue INT) and have yrID and yrN as my PK.
I took over this project only after the implementation is already in production. To change the design will require more changes and testing to the existing projects. My boss probably won't be happy about my suggestions.
I'm trying to create a sp to update a particular yrN column based on the input. So say when the inputs are @yrID = 3, @yrN = 5 and @yrValue = 10, the update should be
update tblYr
set yr5 = 10
where yrID = 3.
My current solution is to use dynamic sql to generate the above sql query having variables N and yrValue to be replaced with @yrN and @yrValue everytime it runs. For 1 or 2 updates this works. But when I have more than a thousands rows that need attention, I'd expect a significant drop on the performance.
I also thought to have a nested if for each column:
IF @yrN = 1
update tblYr set yr1 = @yrValue where yrID = @yrID
ELSE IF @yrN = 2
update tblYr set yr2 = @yrValue where yrID = @yrID
Else IF...
.
.
till @yrN = N
This works fine, except when we have multiple tables that have similar designs, and some are having month instead of yr, then for a 10 years table I'll need to create a sp with 120 nested if update. that's very intimidating, and hard to maintain later on if someone else needs to update the sp.
Is there any solution to this problem with the flexibility of dynamic query and yet the performance of static query?
many thanks in advance!
Kiat
January 31, 2006 at 6:07 pm
If it is as you described then it's very simple update query and you probably will not get any noticable loss in performance using dynamic SQL.
This will be bad and slow in any way.
_____________
Code for TallyGenerator
January 31, 2006 at 9:11 pm
thanks for the reply! do you mean if it's a simple update query as what I mentioned, it won't suffer a performance issue, even for a mass update?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply