April 1, 2009 at 6:49 am
Hi all,
Im a C# developer, and while I can perform a little magic with SQL this is beyond me. We have a legacy asp front end to our companies website which references a vb6 dll. It calls the following stored proc to build our menu structure. The stored proc can take anywhere from 15 seconds to 2 minutes to complet and is full of temp tables and cursors.
The easiest way for me to improve the efficiency of this is by editing the stored procedure, though ideally, I'd like to change the entire process. Any help you can provide to make this procedure more efficient would be appreciated.
EXEC sp_recompile spA_ReadDataListForLevel
DECLARE @ReCnt Int, @dataID int, @data_type int, @data_path varchar(500), @data_name varchar(100)
DECLARE @level_id1 int, @level_parent_id1 int, @level_desc1 varchar(100), @level_order1 int, @role_id1 int
DECLARE @level_id2 int, @level_parent_id2 int, @level_desc2 varchar(100), @level_order2 int, @role_id2 int
DECLARE @level_id3 int, @level_parent_id3 int, @level_desc3 varchar(100), @level_order3 int, @role_id3 int
/*
Create Temprory Tables to hold the data during the execution of the SP and will be dropped at the end
of the session. These temp tables are unique to each established session
*/
Create Table #L1a (level_id int, level_parent_id int, level_desc varchar(100), level_order int, role_id int)
Create Table #L2a (level_id int, level_parent_id int, level_desc varchar(100), level_order int, role_id int)
Create Table #L3a (level_id int, level_parent_id int, level_desc varchar(100), level_order int, role_id int)
--added on 7/8/2008 by AZB
CREATE INDEX [levelidind] ON [dbo].[#L3a]([level_id]) ON [PRIMARY]
Create Table #Data (level_id int, data_id int, data_type_id int, data_path_web varchar(500), data_name_1 varchar(100))
Create Table #ALL (LevelNo int, level_id int, level_parent_id int, level_desc varchar(100), level_order int, role_id int,
data_id int, data_type_id int, data_path_web varchar(500), data_name_1 varchar(100), Datacount int)
Insert Into #L1a
Exec dbo.spA_ReadMenuLevels @lngUserID, @intRoleID, @intLevelParentID
-- Declare a cursor for Level One
DECLARE rsL1 CURSOR FOR
Select * from #L1a
OPEN rsL1
FETCH NEXT FROM rsL1
INTO @level_id1, @level_parent_id1, @level_desc1, @level_order1, @role_id1
--Level One loop
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into #Data (level_id, data_id, data_type_id, data_path_web, data_name_1)
Exec dbo.spA_ReadDataListForLevel @lngUserID, @intRoleID, @level_id1
--Get count of record in #Data
Select @ReCnt = Count(*) From #Data
IF @ReCnt = 1
Select @dataID = data_id, @data_type = data_type_id, @data_path = data_path_web, @data_name = data_name_1 From #Data
Else
Select @dataID = Null, @data_type = Null, @data_path = Null, @data_name = Null
--Building the RecordSet
Insert Into #ALL (levelNo, level_id, level_parent_id, level_desc, level_order, role_id, data_id, data_type_id, data_path_web, data_name_1, Datacount)
Values (1, @level_id1, @level_parent_id1, @level_desc1, @level_order1, @role_id1,@dataID, @data_type, @data_path, @data_name, @ReCnt)
Truncate Table #Data
Insert Into #L2a
Exec dbo.spA_ReadMenuLevels @lngUserID, @intRoleID, @level_id1
--Declare a cursor for Level Two
DECLARE rsL2 CURSOR FOR
Select * From #L2a
OPEN rsL2
FETCH NEXT FROM rsL2
INTO @level_id2, @level_parent_id2, @level_desc2, @level_order2, @role_id2
--Level 2 loop
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into #Data (level_id, data_id, data_type_id, data_path_web, data_name_1)
Exec dbo.spA_ReadDataListForLevel @lngUserID, @intRoleID, @level_id2
-- Get count of records in #Data
Select @ReCnt = Count(*) From #Data
IF @ReCnt = 1
Select @dataID = data_id, @data_type = data_type_id, @data_path = data_path_web, @data_name = data_name_1 From #Data
Else
Select @dataID = Null, @data_type = Null, @data_path = Null, @data_name = Null
-- Insert into the RecordSet
Insert Into #ALL (levelNo, level_id, level_parent_id, level_desc, level_order, role_id, data_id, data_type_id, data_path_web, data_name_1, Datacount)
Values (2, @level_id2, @level_parent_id2, @level_desc2, @level_order2, @role_id2, @dataID, @data_type, @data_path, @data_name, @ReCnt)
Truncate Table #Data
Insert Into #L3a
Exec dbo.spA_ReadMenuLevels @lngUserID, @intRoleID, @level_id2
-- Declare a cursor for Level Three
DECLARE rsL3 CURSOR FOR
Select * From #L3a
OPEN rsL3
FETCH NEXT FROM rsL3
INTO @level_id3, @level_parent_id3, @level_desc3, @level_order3, @role_id3
-- Level 3 loop
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into #Data (level_id, data_id, data_type_id, data_path_web, data_name_1)
Exec dbo.spA_ReadDataListForLevel @lngUserID, @intRoleID, @level_id3
-- Get count of record in #Data
Select @ReCnt = Count(*) From #Data
IF @ReCnt = 1
Select @dataID = data_id, @data_type = data_type_id, @data_path = data_path_web, @data_name = data_name_1 From #Data
Else
Select @dataID = Null, @data_type = Null, @data_path = Null, @data_name = Null
-- Insert into the RecordSet
Insert Into #ALL (levelNo, level_id, level_parent_id, level_desc, level_order, role_id, data_id, data_type_id, data_path_web, data_name_1, Datacount)
Values (3, @level_id3, @level_parent_id3, @level_desc3, @level_order3, @role_id3, @dataID, @data_type, @data_path, @data_name, @ReCnt)
Truncate Table #Data
FETCH NEXT FROM rsL3
INTO @level_id3, @level_parent_id3, @level_desc3, @level_order3, @role_id3
End
-- Close cursor Level Three
CLOSE rsL3
DEALLOCATE rsL3
Truncate Table #L3a
FETCH NEXT FROM rsL2
INTO @level_id2, @level_parent_id2, @level_desc2, @level_order2, @role_id2
End
--Close cursor Level Two
CLOSE rsL2
DEALLOCATE rsL2
Truncate Table #L2a
FETCH NEXT FROM rsL1
INTO @level_id1, @level_parent_id1, @level_desc1, @level_order1, @role_id1
End
select * From #All
--Close cursor Level One
CLOSE rsL1
DEALLOCATE rsL1
--Drop all Temp Tables. Although it is not needed since when the session close it will drop all temp tables
--belong to the session but just in case the session doesn't close properly
Drop Table #All
Drop Table #L1a
Drop Table #L2a
Drop Table #L3a
Drop Table #Data
April 1, 2009 at 7:28 am
We are going to need the listings of those stored procedures that you are calling.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2009 at 7:31 am
i'm still reading your code, but it looks like it is doing nothing more than getting a bunch of links for navigation recursively right?
i'd like to see the code of the procedure spA_ReadDataListForLevel to be sure, but i think the thing can be scrapped and replaced either a CTE or maybe even FOR XML PATH; i'd have to play with it.
can we see the final results, ie select * From #All from the end of the proc so we know what the results should look like?
Lowell
April 1, 2009 at 8:02 am
Yup, all its doing is building a three tiered menu system with some authtenication built around a role.
[spA_ReadDataListForLevel] @lngUserID Int, @intRoleID Int, @intLevelID Int
AS
SET NOCOUNT ON
SELECT @intLevelID as LevelID, d.data_id, d.data_type_id, d.data_path_web, d.data_name_1
FROM dbo.levels_x_data lxd
JOIN dbo.data d ON lxd.data_id = d.data_id
LEFT OUTER JOIN dbo.data_role dr ON d.data_id = dr.data_id
LEFT OUTER JOIN dbo.nodes n ON dr.node_id = n.node_id
WHERE
-- Blanket Conditions (Apply to all data elements)
lxd.level_id = @intLevelID
AND d.active = 1
AND (
-- There are 3 scenarios in which data can be associated to a user:
-- Scenario 1: Non-Specified Data
(d.role_id <= @intRoleID AND dr.data_id IS NULL)
OR
-- Scenario 2: Specific, Inherited Data
-- User's Role <= Node's Role, Inherit = 1, Node is Associated to User
(dr.data_id IS NOT NULL
AND @intRoleID <= n.role_id
AND dr.inherit = 1
AND dr.node_id IN
(select distinct DataNodes.node_id from LevelNodes2 as DataNodes,
(select distinct node_id, rownum from LevelNodes2 as UserNodes where role_id = @intRoleID) as UserNodes
where --DataNodes.role_id = @intLevelID and
UserNodes.node_id in
(select node_id from users_x_nodes where user_id = @lngUserID) --4914
and DataNodes.rownum = usernodes.rownum
)
)
OR
-- Scenario 3: Specific Data
-- User's Role >= Node's Role, Node is Associated to User
(dr.data_id IS NOT NULL
AND n.role_id <= @intRoleID
AND dr.inherit = 0
AND dr.node_id IN
(select distinct DataNodes.node_id from LevelNodes2 as DataNodes,
(select distinct node_id, rownum from LevelNodes2 as UserNodes where role_id = @intRoleID) as UserNodes
where --DataNodes.role_id = @intLevelID and
UserNodes.node_id in
(select node_id from users_x_nodes where user_id = @lngUserID) --4914
and DataNodes.rownum = usernodes.rownum)
))
GROUP BY
d.data_id,
d.data_type_id,
d.data_path_web,
d.data_name_1,
d.data_order
ORDER BY d.data_order, d.data_name_1
option(force order)
Return
What is the best way to post result sets in this forum so that you can read them?
April 1, 2009 at 10:55 am
Do you have the listing for "dbo.spA_ReadMenuLevels" also?
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2009 at 10:56 am
Lowell (4/1/2009)
i'm still reading your code, but it looks like it is doing nothing more than getting a bunch of links for navigation recursively right?i'd like to see the code of the procedure spA_ReadDataListForLevel to be sure, but i think the thing can be scrapped and replaced either a CTE or maybe even FOR XML PATH; i'd have to play with it.
can we see the final results, ie select * From #All from the end of the proc so we know what the results should look like?
Lowell: don't forget that this is the SQL 2000 forum. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2009 at 10:59 am
PROCEDURE [dbo].[spA_ReadMenuLevels]
@user-id Int,
@RoleID Int,
@LevelParentID Int
AS
SELECT
l.level_id,
l.level_parent_id,
l.level_desc,
l.level_order,
l.role_id
FROM
dbo.levels l
WHERE
l.level_parent_id = @LevelParentID
AND l.active = 1
AND ((l.specific = 0 AND l.role_id <= @RoleID) OR (l.specific = 1 AND l.role_id = @RoleID))
AND (l.node_id IS NULL OR l.node_id IN
(SELECT
CASE
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 3) THEN distributor
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 4) THEN sub_territory
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 5) THEN territory
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 6) THEN district
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 7) THEN region
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 8) THEN sub_department
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 9) THEN department
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 10) THEN division
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 11) THEN platform
WHEN ((SELECT role_id FROM nodes WHERE node_id = l.node_id GROUP BY role_id) = 12) THEN corporation
END
FROM dbo.nodes_view WHERE
CASE
WHEN (@RoleID = 2 OR @RoleID = 3) THEN distributor
WHEN (@RoleID = 4) THEN sub_territory
WHEN (@RoleID = 5) THEN territory
WHEN (@RoleID = 6) THEN district
WHEN (@RoleID = 7) THEN region
WHEN (@RoleID = 8) THEN sub_department
WHEN (@RoleID = 9) THEN department
WHEN (@RoleID = 10) THEN division
WHEN (@RoleID = 11) THEN platform
WHEN (@RoleID = 12) THEN corporation
END
IN (SELECT node_id FROM dbo.users_x_nodes WHERE user_id = @user-id)
)
)
GROUP BY
l.level_id,
l.level_parent_id,
l.level_desc,
l.level_order,
l.role_id
ORDER BY
l.level_order
Return
April 1, 2009 at 11:21 am
OK, I just want to confirm: are you on SQL Server 2000 or 2005?
For 2005 then CTE's like Lowell suggested are the way to go. For 2000 then change these two stored procedures to Table-Valued functions. (they had those in SQL 2000 didn't they?)
I am pretty busy today, but maybe I can get to it tonight (unless Lowell does it first 🙂 ).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2009 at 11:30 am
Ya, its 2000. We have had an AFE in the accounting departments hands for 2008 since early last year. Given the market, I wouldnt expect that to get approved till maybe next time this year!
April 1, 2009 at 2:10 pm
Lets keep the stored procedure aside..and tell us what is your requirement.
I did implement similar using Metadata tables.
April 1, 2009 at 3:46 pm
Question: this code from your level 3 loop:
--- Get count of record in #Data
Select @ReCnt = Count(*) From #Data
IF @ReCnt = 1
Select @dataID = data_id, @data_type = data_type_id, @data_path = data_path_web, @data_name = data_name_1 From #Data
Else
Select @dataID = Null, @data_type = Null, @data_path = Null, @data_name = Null
Is it testing for (1 vs 0) or for (1 vs more-than-one)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 8:57 am
From the result set, looks like 1 vs more than one.
If its 1, there is a physical link in the result set to a page.
If there is more than one, it has null for a link because it has child elements.
The weird thing is, they hard coded a max of three levels, I have no idea why they would run that test in the third level, unless its there to catch any bad data.
April 2, 2009 at 9:01 am
My ultimate goal is to change the process all together, unfortunately, the log in process is maintained in a VB6 .dll that I really dont want to touch because its used anywhere, and this company wasnt big on automated testing, so changing it is high risk.
I was thinking the "easiest" solution would be to optimize the stored procedure.
Again, please done kill yourself with this, if its a pain in the butt, we can live with it until we can change the process.
April 2, 2009 at 1:19 pm
Actually, I think that the best solution would be to re-write it with CTE's and then "de-volve" the CTE's into subqueries and temp tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 1:21 pm
jaybouch (4/2/2009)
From the result set, looks like 1 vs more than one.If its 1, there is a physical link in the result set to a page.
If there is more than one, it has null for a link because it has child elements.
The weird thing is, they hard coded a max of three levels, I have no idea why they would run that test in the third level, unless its there to catch any bad data.
Actually, I think that you just convinced me of the opposite. It is normal in a recursive routing (even a static one!) to have a pre-check for non-existence at the bottom levels.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply