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 2 2027 ad*******@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/m...S,SQL.90).aspx
--
(ad*******@gmail.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(ratingID, ' ', '') + ','
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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: blrmaani |
last post by:
Here is what I want:
string s1 = "This is a list of string";
list<string> s2 = s1.some_method();
Now, I should be able to traverse list s2 and get each
member ( which is of type 'string' ).
...
|
by: Thilo-Alexander Ginkel |
last post by:
Hello,
I am currently trying to assign some string to a TEXT output parameter
of a stored procedure.
The basic structure of the stored procedure looks like this:
-- 8< --
CREATE PROCEDURE...
|
by: ethoemmes |
last post by:
I have used the import class module from
http://www.mvps.org/access/modules/mdl0057.htm. This reads my text file
into a string called myString. I am trying to append this string into
table...
|
by: Jazper |
last post by:
hi
can anybody explain that to me:
i did the following:
- 1. input of char 244 ("ô") into string
- 2. convert to getbytes
- 3. convert back to string
char 244 changed to char 63 ("?")...
|
by: Rico |
last post by:
If there are consecutive occurrences of characters from the given
delimiter, String.Split() and Regex.Split() produce an empty string as the
token that's between such consecutive occurrences. It...
|
by: Andrew Robinson |
last post by:
Any easy answer what is wrong here?
private List<string> BodyWords = new List<string>();
string word = "Andrew";
the following causes a compilation error:
|
by: James Coleman |
last post by:
I am using the TreeView control in ASP.NET 2.0 and like it. However, our
sitebuilders aren't too pleased with the idea of the table that gets
rendered. They are big into avoiding <table> tags...
|
by: YarrOfDoom |
last post by:
Hi I'm trying to get my input from a RichTextBox into a string, but the tabs in my input are left away. Is this a limitation of string, or am I doing something wrong? I'm trying to do this like this:...
|
by: since |
last post by:
How do I get IE and Mozilla to show the right border for an input tag.
for the following table
Everything looks great until I add the doctype tag.
I can get IE to work by adding the following.
...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |