Input string -> table -> output string?

[adambossy@gmail.com]

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

[David Portas]

Re: Input string -&gt; table -&gt; output string?

adambossy@gmail .com wrote:

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

Both a) and d):

a) Create a junction table...
d) Remember always that delimited lists don't belong in the database.

:-)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: Input string -&gt; table -&gt; output string?

(adambossy@gmai l.com) writes:

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:
>...
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,

I believe you. That design is a complete disaster, for the very
reasons you mention. It cannot be handled effeciently in a relational
database. The proper design is to put the repeating group in a
sub-table.

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.

A better alternative, using the current table design, would be get
the select data into a temp table in this way:

INSERT #temp (uniqueid, ratingID)
SELECT uniqueid, ',' + replace(ratingI D, ' ', '') + ','
FROM tbl

Note that you add trailing and leading commas, and remove all spaces.
Then loop over Ratings, and say;

UPDATE #temp
SET ratings = replace(ratings , ',' + @rating + ',',
',' + @descr + ',')


But in the long run, a table redesign is what you need.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx