April 11, 2005 at 11:10 am
Hi,
Could anyone tell me why my sproc is in infinite loop?
Thanks Much!
CREATE PROCEDURE dbo.spGet_bag_nums
@work_ord_num char(6),
@work_ord_line_num char(3),
@bag_nums int OUT
AS
declare @bag_num as int
set @bag_num = 0
declare get_bags cursor
For
SELECT bag_num
FROM dbo.tblBag_data
WHERE LEFT(work_ord_num, 6) = @work_ord_num AND
work_ord_line_num = @work_ord_line_num AND bag_status = 1
ORDER BY work_ord_num , work_ord_line_num, bag_num
Open get_bags
Fetch Next FROM get_bags INTO @bag_num
While @@Fetch_status = 0
Begin
if @bag_num = 0
set @bag_nums = @bag_num
else
set @bag_nums = @bag_nums & @bag_num
Fetch Next FROM get_bags INTO @bag_num
End
close get_bags
deallocate get_bags
GO
April 11, 2005 at 11:41 am
Should this: set @bag_nums = @bag_nums & @bag_num
Be
set @bag_nums = @bag_nums + @bag_num
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 11, 2005 at 11:42 am
Is that the whole code from the proc??
If so then you just don't need a cursor to execute that command.
the only thing I could suggest is to change the cursor declaration to this (because cursors are updatable, and slower, by default) :
cursor FAST_FORWARD for Select...
Also how long does it take to run the query of the cursor in query analyser?
Have you debugged the proc to see where the bottleneck of the proc is (opening the cursor / infinite loop)?
Also is there a way to keep the id stored in work_ord_num into a separate column? This would allow you to index that column allowing you to do an index seek instead of an index scan (the left function forces a scan).
You can check the execution plan of these 2 queries to see what I mean :
Select * from dbo.SysObjects where id = 1
Select * from dbo.SysObjects where left(id, 6) = '1'
Also on a final note, I think this version of the proc would give you the same results :
CREATE PROCEDURE dbo.spGet_bag_nums
@work_ord_num char(6),
@work_ord_line_num char(3),
@bag_nums int OUT
AS
set @bag_nums = 0
SELECT @bag_nums = @bag_nums | bag_num
FROM dbo.tblBag_data
WHERE LEFT(work_ord_num, 6) = @work_ord_num AND
work_ord_line_num = @work_ord_line_num AND bag_status = 1
ORDER BY work_ord_num , work_ord_line_num, bag_num
April 11, 2005 at 11:49 am
Just correcting myself here :
In my previous post I assume that you are creating a bitmap so I suggested you do something like this :
Declare @BitMap as int
set @BitMap = 0
Select @BitMap = @BitMap | MyBit from
(
Select 1 as MyBit
union all
Select 2 as MyBit
union all
Select 4 as MyBit
) A
Select @BitMap as BitMap
/*but if all the attributes are unique you could also do it like this which may be a little less safe (have little experience with bitmaps)*/
Select sum(MyBit) as BitMap from
(
Select 1 as MyBit
union all
Select 2 as MyBit
union all
Select 4 as MyBit
) A
April 11, 2005 at 12:36 pm
Thanks AJ & Remi for your replies.
I need to explain what the @bag_nums should return. I need bag # for ea record that meets the criteria and the bag #'s need to concatenate and return in @bag_nums.
ex:
work_ord line_num status bag_num
123456 001 1 113
123456 001 1 114
123456 001 1 115
I need bag #'s concatenate in return value
@bag_nums = 113114115
Thanks
April 11, 2005 at 12:39 pm
CREATE PROCEDURE dbo.spGet_bag_nums
@work_ord_num char(6),
@work_ord_line_num char(3),
@bag_nums varchar(8000) OUTPUT
AS
SET @bag_nums = ''
SELECT @bag_nums = @bag_nums + CAST(bag_num AS VARCHAR(10)) + ',' /*so you can resplit them on the client*/
FROM dbo.tblBag_data
WHERE LEFT(work_ord_num, 6) = @work_ord_num AND
work_ord_line_num = @work_ord_line_num AND bag_status = 1
ORDER BY work_ord_num , work_ord_line_num, bag_num
April 11, 2005 at 2:50 pm
VERY NICE.
Thank you Remi!
April 11, 2005 at 2:58 pm
Have you considered my advice on keeping the work_ord_num in a separate column so that a index seek can be used? You'll see a huge speed improvement even on small tables.
April 12, 2005 at 6:56 am
Hi Remi,
Are you saying I should have a column for ID OR that work_ord_num should be in a separate column?
I don't have have ID column in the table. The primary keys are consisted of work_ord_num(char), work_ord_line_num(char) and bag_num(int). The work_ord_num stores 9 characters (ex: 123456-00) but the last two characters may not matche up. Are you saying I should place a six character work_ord_num column in the table use to speed up the search?
April 12, 2005 at 7:16 am
exactly... also you should convert it to int (unless it's impossible with the data).
Also even some have disagreed about this I've seen a huge speed improvement in changing my primary keys from char(6) to int on a orders table (yup crappy set-up but I can't change it). I simply copied the data to a new column of type int and moved the clustered index there. In this particular case I've seen speed improvement of 50%-75% on queries that are run 1000s times per day.
April 12, 2005 at 7:52 am
Hmmm but I'll duplicating the same data. I'll look into how often will I need to extract the work_ord_num. I do appreciate your suggestions.
Have a great day!
April 12, 2005 at 7:55 am
actually it might be the other way around...
In my case I had the id in char because of presentation, once I moved the id to the int column I create a calculated field on to replace the old columns. So I actually save space that way.
April 12, 2005 at 8:22 am
Hi Remi,
I just tried using like and it doesn't work. Can you take a quick look?
SELECT @bag_nums= COALESCE(@bag_nums + ', ','') + CAST(bag_num AS VARCHAR(10))
FROM tblBag_data
WHERE work_ord_num LIKE "@work_ord_num%" AND
work_ord_line_num = @work_ord_line_num AND bag_status = 1
ORDER BY work_ord_num , work_ord_line_num, bag_num
SELECT @bag_nums
Is it better using like than Left function?
Thanks!
April 12, 2005 at 8:50 am
WHERE work_ord_num LIKE @work_ord_num + '%' AND
April 12, 2005 at 9:36 am
Thanks Remi!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply