Posted by adambossy on 07/13/06 18:43

I have a nasty situation in SQL Server 7.0. I have a table, in which
one column contains a string-delimited list of IDs pointing to another
table, called "Ratings" (Ratings is small, containing less than ten
values, but is subject to change.) For example:

[ratingID/descr]
1/Bronze
2/Silver
3/Gold
4/Platinum

When I record rows in my table, they look something like this:

[uniqueid/ratingIDs/etc...]
1/2, 4/...
2/null/...
3/1, 2, 3/...

My dilemma is that I can't efficiently read rows in my table, match the
string of ratingIDs with the values in the Ratings table, and return
that in a reasonable fashion to my jsp. My current stored procedure
does the following:

1) Query my table with the specified criteria, returning ratingIDs as a
column
2) Split the tokens in ratingIDs into a table
3) Join this small table with the Ratings table
4) Use a CURSOR to iterate through the rows and append it to a string
5) Return the string.

My query then returns...
1/"Silver, Platinum"
2/""
3/"Bronze, Silver, Gold"

And is easy to output.

This is super SLOW! Queries on ~100 rows that took <1 sec now take 12
secs. Should I:

a) Create a junction table to store the IDs initially (I didn't think
this would be necessary because the Ratings table has so few values)
b) Create a stored procedure that does a "SELECT * FROM Ratings," put
the ratings in a hashtable/map, and match the values up in Java, since
Java is better for string manipulation?
c) Search for alternate SQL syntax, although I don't believe there is
anything useful for this problem pre-SQL Server 2005.

Thanks!
Adam

[Back to original message]