|
|
Posted by David Portas on 07/13/06 20:48
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
--
[Back to original message]
|