November 30, 2011 at 8:41 am
What is best to rewrite the below query instead of using temp table or cursors..
Thanks for your help in advance !
CREATE TABLE #UpdateItems
(id int identity(1, 1),
Iname varchar(200),
Items_id int,
Finditems_id int)
insert into #UpdateItems Iname,Items_id,Finditems_id)
select itname,
us.mapitems,
uo.misitems
from store uo
Left outer join storemap uom on
uo.itemname = uom.storeitemname
inner join processs ub on uo.processid = ub.id
inner join primarytable us on us.primaryid= ub.processmainid
left outer join masterstoreitems mo on uo.itemname = mo.itemname
where us.status ='y' and uo.misitems is null
and uo.processid = @processid
and mo.itemname is null
select @rowcount = @@rowcount
set @i = 0
select * from #UpdateItems
while @i <= @rowcount
begin
select top 1
@id = id,
@ItemsIname = Iname,
@Items_id = Items_id,
@Finditems_id = Finditems_id
from #UpdateItems
where id > @i
select @row = @@rowcount
if @row = 0 break
exec dbo.Itemsstock @Items_id,@ItemsIname,@Finditems_id
set @i = @id
end
drop table #UpdateItems
November 30, 2011 at 8:48 am
Depends what your Itemstock stored procedure does. You may need to rewrite that as well.
John
November 30, 2011 at 1:01 pm
The itemstock procedure which create new items found into ITEMMASTER table.
November 30, 2011 at 1:10 pm
What is best to rewrite the below query instead of using temp table or cursors
Do you have specific concerns with using the temp tables? Or are you seeing some sort of a performance issue or are you just curious? The WHILE loop will work just fine for things like this (generally preferred over cursors to begin with)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2011 at 1:58 pm
MyDoggieJessie (11/30/2011)
What is best to rewrite the below query instead of using temp table or cursors
Do you have specific concerns with using the temp tables? Or are you seeing some sort of a performance issue or are you just curious? The WHILE loop will work just fine for things like this (generally preferred over cursors to begin with)
You shouldn't make blanket statements like this. The following article shows that cursors can indeed perform better than a WHILE loop. http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741 (And that was only the first article I picked from a Google search.)
Where WHILE loops benefit the most over cursors is being able process a batch of records in each iteration versus cursors only allowing one record per iteration. But since the stored proc can only handle one record at a time, this particular WHILE loop would be forced to have a batch size of one, which loses the main benefit of using a WHILE loop in the first place.
The most likely place to improve performance for this particular query is to rewrite the stored proc so that it will be able to process multiple records at the same time. Since we know next to nothing about the stored proc, we can't say if this is even possible.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 30, 2011 at 2:33 pm
Where WHILE loops benefit the most over cursors is being able process a batch of records in each iteration versus cursors only allowing one record per iteration. But since the stored proc can only handle one record at a time, this particular WHILE loop would be forced to have a batch size of one, which loses the main benefit of using a WHILE loop in the first place.
You're quite right Drew, it was one of those off the hook statements that should be taken with a grain of salt, check your processes, and so forth. It wasn't mean to serve as a SQL standard, just one of mine based upon my past experiences using the two 🙂
IMHO, the amount of CPU utilization endured by using cursors far outweigh that of temp tables and a loop. If something is being called to run that code simultaneously (which is something we really don't know at this point), the overhead on the server would be considerably increased if using a cursor, and much lighter with the loop. Again, this is speaking only from my experience using them.
Based upon what the OP has stated, we still don't know if there's any issue regarding performance, they were just asking if there was a better way...
I'll be more careful forward about making such statements, thanks for pointing that out :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply