cursors

[pendarric]

I am having some trouble with an SQL query

the data would look something like thie
id attribute
1 blue
2 yellow
1 black
3 white
4 green
2 blue
5 green
2 brown
1 yellow
4 white

the output needs to be like this.
id attribute
1 blue,black,yellow
2 yellow, blue, brown
3 white
4 green
5 green,white

This is easy to do with cursors but it is slow. The number of record is up around 100,000 and with cursors it takes 15mins. There has to be a more efficient way of doing it.

Any suggestions

 

[MikeLacey]

hi, you have an index on 'id' I assume?

Could you post your cursor solution? (<grin> if it's not twenty pages of course!)
Mike
michael.j.lacey@ntlworld.com

 

[grahamnewlands]

First of all I agree that you require an index on id...The way I would tackle it is this :

1. Do a select distinct of id into a temporary table which has no index...without the index will speed up the insert and you will be doing a sequential loop through this temp table. columns in the temp table should be : id, colours

2. Do a cursor select for each record in the temp table
For each record do a cursor select to get the
colours based on the id in the original table
for each colour retrieved update the current row in
the temp table i.e. update temp_table set colour =
colour + ',' + :new_colour
commit
end loop
end loop

Another thing to check is to make sure that you are selecting no-lock. In ingres II for example if you specify no-lock then no log file is created hence less of an overhead...Notice I have also kept the transaction small by having a commit for each insert...You can do error handling here also to check if there have been any errors in which case you may want to simply drop the temp table or flag something...

hope this helps...I would need to think about a non-cursor select if you wanted one but I think cursors is easiest..
The above should be fairly fast because it utilises indexes and the size of the transactions should be small apart from the initial outer loop/cursor select

 

[pendarric]

yes the cursor is easiest but it take too long ~ 15min fr 80000+ records

 

[MalcolmW]

what is your database? Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[pendarric]

MS SQL 2000

 

[MalcolmW]

faq183-207 has a SQL Server specific trick in that you can use to avoid cursors. You would have to modify it in your case to use another variable to track the id, and create a temp table with distinct id fields to populate with values - actually I'm not convinced it would work (don't have time to look into it), but worth having a look at. Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.