Stored proc for simple (ish) scorecard model

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored proc for simple (ish) scorecard model

Author

Topic

mk1matt
Starting Member

7 Posts

Posted - 2010-03-19 : 06:44:18

I'm after some pointers as to the best way to go about doing this.

I'm building a stored proc which needs to process a single record (the PK of this record is passed as a parameter) and return a series of scores based on the data.

It's a relatively simple scorecard model which applies weighted scores to certain fields and sums the result. In total there are about 40 calculations to be done. Some are simple scores, some are matrix calculations.

My problem is with working out the most efficient way of doing it. Currently I'm running each question as a subquery e.g.

SET @Question1 =
CASE (SELECT field1 FROM table1 WHERE pk = @pk)
WHEN 1 THEN 50
WHEN 2 THEN 60
WHEN 3 THEN 70
END

I'd do this for each question and then sum/weight the resulting variables.

This seems relatively inefficient. I was thinking about using a cursor to do the processing, as it would only involve one process on the database. But then again, cursors are generally bad so I'm told.

Has anyone got any suggestions about how to go about this?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-19 : 09:11:36

Creating your own scorecard engine is a huge undertaking, there are fairly large companies that do this as their core business. I don't think it'll be possible to increase the performance of gathering 40 pieces of information, other than the usual performance tuning tricks of making sure that each query is properly indexed, etc. Using a cursor is probably not gonna do you any good either...

So unless you can figure out a way to join some of the queries together I'd say your'e stuck with the 40 calculations. That is at least my understanding of it...maybe someone else has some nice magic tricks up their sleeve

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-19 : 09:14:06

There are of course data warehouse techniques available...are you aware of how that works? Nothing quick'n'fancy though...it all takes time.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

mk1matt
Starting Member

7 Posts

Posted - 2010-03-19 : 10:27:33

Thanks for the reply. I do appreciate that it's a large undertaking, but in reality it's a pretty small scorecard so it's not too bad.

I've had a play and I've discovered a muc more efficient way now anyway. Instead of 40 or so separate queries, I'm using a single one like below.

SELECT
@Result1 = CASE field1
WHEN 1 THEN 50
WHEN 2 THEN 60
END
FROM
table1
WHERE pk = @pk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources