Getting rid of cursors to help with performance
From: Kevin Munro (kevin_at_c3amulet.com)
Date: 01/10/05
- Next message: Alejandro Mesa: "RE: date variable in a DTS package"
- Previous message: Honza Novak: "Re: processor doesn't leave the 100%"
- Next in thread: Hugo Kornelis: "Re: Getting rid of cursors to help with performance"
- Reply: Hugo Kornelis: "Re: Getting rid of cursors to help with performance"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 10 Jan 2005 15:51:45 -0000
Hello, I've been in and out of this ng for a while and I've read that you
should really get rid of cursors to increase performance. Well, I'm in a
bit of a pickle with this stored procedure and function and wonder if anyone
can help me out!
My database has a million records scattered over the main tables and one of
the tables called Container contains a hierarchy system. This is simply a
join on Container.parent=Container.ident.
Now, I have a query that may bring back several thousand containers and I
put all my container idents into a table called Pqtselection and give it a
unique session id so that I can retrieve it later. It takes about 30
seconds to get all my results and I'm hoping to get the time down on this.
So, to get all the ancestors for these several thousand containers I call
one stored procedure, amsp_InsertAncestors
The stored procedure takes each container ident and looks up a function that
returns a table for the ancestors. Now I believe this part of the process
is pretty fast and I was kindly helped with this in December (I think it was
Itzik). But this function will only accept one container ident, I can't see
how to get this working with one SQL statement. It may be impossible and/or
time for a rethink.
My main problem is that I need to support an adhoc user query by returning
all the ancestors for a given set of records - and I need this to be as fast
as possible. Getting the parent of each of these containers is easy doing a
join, but getting all the ancestors is very difficult!!
Any help much appreciated.
Many thanks, Kevin Munro.
------------------SP
CREATE PROCEDURE amsp_InsertAncestors
@sessionid as varchar(8)
AS
declare @counter int
SET NOCOUNT ON
-- get all container rows into a temporary table
SELECT CONTAINERID INTO #tree FROM PQTSELECTION WHERE SESSIONID=@sessionid
-- set up the cursor
declare objects cursor
for
select containerid from #tree
declare @ident int
-- open the cursor and fetch the first row
open objects
fetch objects into @ident
while (@@fetch_status=0)
begin
-- insert into the container table and fetch the newxt row from the cursor
insert into pqtcontext select parent,@sessionid from
ufn_GetAncestors(@ident)
fetch objects into @ident
end
-- close and release memory
close objects
deallocate objects
-- drop temporary table
drop table #tree
----------------FUNC
CREATE FUNCTION ufn_GetAncestors(@ident AS int)
RETURNS @tree table
(
ident INT NOT NULL,
parent INT NULL,
lvl INT NOT NULL
)
AS
BEGIN
DECLARE @lvl AS INT
SET @lvl = 0
-- get given node
INSERT INTO @tree
SELECT ident, parent, @lvl
FROM container
WHERE ident = @ident
WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1
-- get parent of prev level's node
INSERT INTO @tree
SELECT E.ident, E.parent, @lvl
FROM container AS E JOIN @tree AS T
ON E.ident = T.parent AND T.lvl = @lvl - 1
END
RETURN
END
- Next message: Alejandro Mesa: "RE: date variable in a DTS package"
- Previous message: Honza Novak: "Re: processor doesn't leave the 100%"
- Next in thread: Hugo Kornelis: "Re: Getting rid of cursors to help with performance"
- Reply: Hugo Kornelis: "Re: Getting rid of cursors to help with performance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
