| Author |
 Topic  |
|
|
lmendoza
Starting Member
12 Posts |
 Posted - 07/18/2001 : 13:07:52
|
Hey all -
I'm started using SQL Server about 6 months ago, and I was wondering if you could give me some help making this stored proc. The data is designed like so
new_sku: (about 4000 rows)
sku int
longdesc: (about 600,000 rows)
ace_sku int
sequence_number int
bullet_text nvarchar(64)
it's a 1:N relationship for sku:longdesc.
I need to create one table containing the skus and the bullet_text, in the correct sequence (why we're creating one table is beyond me, since this schema is better, but I digress).
so ex:
sku: longdesc
1 1 1 bullet1
1 2 bullet2
1 3 bullet3
will make the table
1 bullet1*bullet2*bullet3
My coworker designed a explicit cursor based solution, which took about 4 hours to process 160 rows, so I took it upon myself to design another solution. The only one I came up with uses an implicit cursor. I'm convinced that there is a way to do this without using any cursors, though...Am I right?
thanks in advance-
leo
in case you're wondering, here's my implicit cursor based sp:
CREATE PROCEDURE usp_newsku
AS
CREATE TABLE #t
(
pk INT
IDENTITY(1,1),
sku INT,
descr NVARCHAR(2048)
)
INSERT INTO #t (sku)
(SELECT sku FROM new_sku)
DECLARE @cnt INT, @max INT, @sku NVARCHAR(20)
DECLARE @desc nvarchar(1048)
SET @cnt = 0
SELECT @max = MAX(pk) FROM #t
--start the loop
WHILE (@cnt <= @max)
BEGIN
SET @cnt = @cnt + 1
SELECT @sku = sku FROM #t WHERE pk = @cnt --get the sku
--infuse the descriptions
SELECT @desc = COALESCE(@desc + ' ',' ') + LTRIM(RTRIM(bullet_text))
FROM longdesc WHERE ace_sku = @sku
--update the temp table
UPDATE #t SET descr = @desc WHERE pk = @cnt
SET @desc = ' '
END
SELECT * FROM #t
DROP TABLE #t
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 07/18/2001 : 14:03:27
|
You need to go back to your parenthetical comment
quote:
(why we're creating one table is beyond me, since this schema is better, but I digress).
Why ARE you doing it? If you don't know why you are doing it, it probably shouldn't be done.
|
 |
|
|
lmendoza
Starting Member
12 Posts |
Posted - 07/18/2001 : 14:41:24
|
Well - it needs to be done this way because it works better with the legacy code that references just one table, instead of the better, normalized two. I'm currently in the process of trying to get a redesign done, and this will help my case.
Anyhow - If anyone could offer some advice on optimizing this SP, i would really appreciate it. It's been running for about a hour now, and I still haven't gotten any debug printouts, let alone results. (my print statements are within the while loop)
|
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 07/18/2001 : 14:52:38
|
Why not create a view of the data instead of redoing the tables, and have the legacy code reference the view instead?
(I don't actually think it's possible, but it's worth a shot)
I don't think you'll do better with the SP than what you've got now. You might want to email Garth (Wells), he wrote the COALESCE article on CSV values, he might have a trick or two to fix it up.
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
|
|
izaltsman
A custom title
USA
1139 Posts |
Posted - 07/18/2001 : 19:15:48
|
Don't know if this will be any faster than a cursor, but here is a set-based solution...
Test it out (I didn't have much test data to go on, but I think it should work).
SELECT sku, sequence_number, bullet INTO #temp FROM
(
SELECT ace_sku as sku, sequence_number, bullet_text as bullet FROM longdesc
UNION ALL
SELECT sku, -1 as sequence_number, CAST(NULL AS NVARCHAR(4000)) as bullet
FROM new_sku
)as x
ORDER BY sku, sequence_number
DECLARE @result NVARCHAR(4000)
UPDATE #temp
SET @result=bullet = COALESCE (@result+bullet+' ', bullet+' ', '')
SELECT IDENTITY(int) as pk, t.sku, t.bullet as descr INTO #temp2
FROM
#temp AS t
INNER JOIN
(SELECT sku, MAX(sequence_number) AS sequence_number FROM #temp GROUP BY sku) as keepers
ON t.sku = keepers.sku AND t.sequence_number = keepers.sequence_number
ORDER BY t.sequence_number
DROP TABLE #temp
SELECT * FROM #temp2
DROP TABLE #temp2
I assumed that skus are unique within a table and that sequence numbers are unique within a sku. Also assumed that there are no negative sequence numbers.
Ilya
Edited by - izaltsman on 07/18/2001 19:39:36 |
|
|
| |
Topic |
|
|
|
Cursor-less SP, best solution?
