SQL: Is there a better way?

[Goksly]

Hi,
I have a situation where some data is stored in a comma separated format. One cell in this table can mean many rows in another table (i.e. answers 'top', 'middle' and 'bottom' can all be represented by '1,1,1', where as 'top' and 'bottom' would be represented as '1,0,1').

Now, to count which options have been selected, I use a cursor to select one data cell (comma separated value) at a time and then process this; loop through the string checking for a '1' and then look up the full string answer in a table variable.

Now, looking at SQL profiling tool, this does seem to have a bit of a performance impact. I will be caching the results of this query, but still want to optimise queries at every possible opportunity. Unfortunatly I am unable to change how this data is stored...

So, is there a better way to take a comma separated representation of data and get the underlying data in a less expencive way?

Thanks for reading.

 

[sist_si]

would it be feasible to convert the comma separated list to rows in a temp table/table var then just use sql to select the answers with a join?
Have you got a table structure?

Here's a function that converts a comma sep'd list to a table:

CREATE FUNCTION [dbo].[fn_listToTable](@list nvarchar(MAX))
RETURNS @tbl TABLE (number intNOTNULL)AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos =charindex(',', @list, @pos + 1)
SELECT @valuelen =CASEWHEN @nextpos > 0
THEN @nextpos
ELSElen(@list)+ 1
END- @pos - 1
ifnotsubstring(@list, @pos + 1, @valuelen)isnull
begin
INSERT @tbl(number)
VALUES (convert(int,substring(@list, @pos + 1, @valuelen)))
end
SELECT @pos = @nextpos
END
RETURN
END

 

[cjmUK]

Are you really stuck with the CSV multi-value field or can you change the DB? Sticking with CSV severely restricts your options.

What DB are you using? If you are using SQL Server 2005/2008 you can use the CLR to leverage Regular Expressions. If you are using SQL Server 2000, you can use VBScript.RegEx instead.

If you are using some other SQL DB, then we'll need to see your code to suggest improvements - I'm pretty sure you ought to be able to be rid of the cursor, which is a significant performance limiter.

 

[Goksly]

Thanks for the feedback guys. At the moment I am unable to change a database structure that I have inherited - however, when time allows we will change it, which is another reason I want to use a stored proc / user defined function so we can change the underlying structure without affecting the web app.

cjm: I can't use regex as each value is a 1 or 0 and its location is the index of the look up table. In future, the CLR might be the way to go however!

sist_si: A look up table might be the answer, then (I assume) I can just do a join (on index) and filter on where value != 0. Hopefully that should get rid of the cursor, which is causing a fair bit of overhead atm.