HELP - Build new stucture from recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP - Build new stucture from recordset

Author

Topic

laca81
Starting Member

9 Posts

Posted - 2015-04-27 : 08:57:12

Hi guys,

I have the following task and I am looking for the best solution from performance perspective. I use MS SQL 2008R2.
The task:
there is a recordset with the following columns:
ID, level, ParentID, LabelText

For instance (recordset of table1):
1, 'D', null, 'Main 1'
2, 'D', null, 'Main 2'
3. 'C', 1, 'Sub 1'
4, 'C', 2, 'Sub 2'
5, 'B', 4, 'SubSub 1'

I would like to create the following table/recordset (table2):
LabelText (D), LabelText (C), LabelText(B)
Main 1
Main 2
Main 1 Sub 1
Main 2 Sub 2
Main 2 Sub 2 SubSub 1

Any idea is appreciated.
Thanks in advance!

AuroraS
Starting Member

3 Posts

Posted - 2015-04-27 : 09:59:27

What I understood from your example is that you are dealing with 'one to many' relationship(for each Level you can have more LabelText).If this is the case.....you must add a column to the table where are the multiple choices(table 2 ) that references the primary key of the table with one choice(table 1).

laca81
Starting Member

9 Posts

Posted - 2015-04-27 : 10:11:01

quote:
Originally posted by AuroraS

What I understood from your example is that you are dealing with 'one to many' relationship(for each Level you can have more LabelText).If this is the case.....you must add a column to the table where are the multiple choices(table 2 ) that references the primary key of the table with one choice(table 1).

Basically the new table should contain the parents, children and grand children. As a starting point I have only the first table. Is this a good idea:
1. order the table by main items (parents, e.g: main 1 main 2)
2. create a loop. Investigate the record in each iteration.
If the item type is 'D' -> look after whether it has child (C) or not.
If has -> get it's label -> look after whether it has child (B) or not. If there is no child -> go for the next item.
Add these values into the second table as a record
Do this with all of the records in table 1.

If the record is 'C' -> look for parent, and look for children
If the record is 'B' -> look for parent and look for grand parent

Is there any easier solution from performance perspective?

Yes, it is one to many

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-27 : 14:04:38

1. by "order the table" do you mean "create a clustered index"? If not, what do you mean, since SQL tables are sets which by definition have no ordering, nor can they.
2. Don't code a loop. Let SQL do it. Think set-based, not row-by-row.

In your case, if you have only three levels (child, parent, grandparent) you can do this with self-joins


select ...
from table child
join table parent on child.parentid = parent.id
join table grandparent on parent.parentid = grandparent.parentid
...

laca81
Starting Member

9 Posts

Posted - 2015-04-27 : 14:56:23

quote:
Originally posted by gbritton

1. by "order the table" do you mean "create a clustered index"? If not, what do you mean, since SQL tables are sets which by definition have no ordering, nor can they.
2. Don't code a loop. Let SQL do it. Think set-based, not row-by-row.

In your case, if you have only three levels (child, parent, grandparent) you can do this with self-joins

select ...
from table child
join table parent on child.parentid = parent.id
join table grandparent on parent.parentid = grandparent.parentid
...

I will do this! Thank you very much guys!

laca81
Starting Member

9 Posts

Posted - 2015-05-11 : 04:01:40

Guys, it works perfect but it is very slow. There are only 1300 records in the @mergedMenus table. The select looks like this:

SELECT DISTINCT Parent.MENU_ID AS Main_MenuID
, Parent.MENU_ITM_NR AS Main_MENU_ITM_NR
, Parent.LTRL_TXT AS [Main Menu]
, Parent.MENU_ITM_AREA AS [Main Menu Area]
, Child.LTRL_TXT AS [Level 1 Menu]
, Child.MENU_ITM_AREA AS [Level 1 Menu Area]
, Child.MENU_ID AS Lvl1_MenuID
, Child.MENU_ITM_NR AS Lvl1_MENU_ITM_NR
, GrandChild.MENU_ID AS Lvl2_MenuID
, GrandChild.MENU_ITM_NR AS Lvl2_MENU_ITM_NR
, GrandChild.LTRL_TXT AS [Level 2 Menu]
, GrandChild.MENU_ITM_AREA AS [Level 2 Menu Area]
, GreatGrandChild.MENU_ID AS Lvl3_MenuID
, GreatGrandChild.MENU_ITM_NR AS Lvl3_MENU_ITM_NR
, GreatGrandChild.LTRL_TXT AS [Level 3 Menu]
, GreatGrandChild.MENU_ITM_AREA AS [Level 3 Menu Area]
, Parent.FromAccessGroupA
, Child.FromAccessGroupA AS FromAccessGroupA_1
, GrandChild.FromAccessGroupA AS FromAccessGroupA_2
, GreatGrandChild.FromAccessGroupA AS FromAccessGroupA_3
, Parent.FromAccessGroupB
, Child.FromAccessGroupB AS FromAccessGroupB_1
, GrandChild.FromAccessGroupB AS FromAccessGroupB_2
, GreatGrandChild.FromAccessGroupB AS FromAccessGroupB_3
, Parent.RestrictedByFailSafe
, Child.RestrictedByFailSafe AS RestrictedByFailSafe_1
, GrandChild.RestrictedByFailSafe AS RestrictedByFailSafe_2
, GreatGrandChild.RestrictedByFailSafe AS RestrictedByFailSafe_3
, Parent.RestrictedByPrincipal
, Child.RestrictedByPrincipal AS RestrictedByPrincipal_1
, GrandChild.RestrictedByPrincipal AS RestrictedByPrincipal_2
, GreatGrandChild.RestrictedByPrincipal AS RestrictedByPrincipal_3
, Parent.RestrictedByBrand
, CASE WHEN GreatGrandChild.MENU_ID IS NOT NULL THEN REPLACE(GreatGrandChild.DEMO_ACTN,'_',' ') WHEN GrandChild.MENU_ID IS NOT NULL THEN REPLACE(GrandChild.DEMO_ACTN,'_',' ') WHEN Child.MENU_ID IS NOT NULL THEN REPLACE(Child.DEMO_ACTN,'_',' ') WHEN Parent.MENU_ID IS NOT NULL THEN REPLACE(Parent.DEMO_ACTN,'_',' ') END AS Brand
, Parent.WhichUser AS Main_WhichUser
, Child.WhichUser AS Lvl1_WhichUser
, GrandChild.WhichUser AS Lvl2_WhichUser
, GreatGrandChild.WhichUser AS Lvl3_WhichUser
, '' AS User1_Info
, '' AS User2_Info

FROM @mergedMenus AS Parent
LEFT OUTER JOIN @mergedMenus AS Child ON (Child.PAR_MENU_ID = Parent.MENU_ID and Child.PAR_MENU_ITM_NR = Parent.MENU_ITM_NR )
LEFT OUTER JOIN @mergedMenus AS GrandChild ON (GrandChild.PAR_MENU_ID = Child.MENU_ID and GrandChild.PAR_MENU_ITM_NR = Child.MENU_ITM_NR )
LEFT OUTER JOIN @mergedMenus AS GreatGrandChild ON (GreatGrandChild.PAR_MENU_ID = GrandChild.MENU_ID and GreatGrandChild.PAR_MENU_ITM_NR = GrandChild.MENU_ITM_NR )
WHERE Parent.MENU_ITM_AREA = 'D' AND Parent.MENU_ID IS NOT NULL

The table is this:

DECLARE @mergedMenus TABLE (
MENU_ID CHAR(6) INDEX IX1 CLUSTERED,
MENU_ITM_NR CHAR(3) INDEX IX2 NONCLUSTERED,
MENU_ITM_AREA CHAR(1),
PAR_MENU_ID CHAR(6) INDEX IX3 NONCLUSTERED,
PAR_MENU_ITM_NR CHAR(3) INDEX IX4 NONCLUSTERED,
LTRL_TXT NVARCHAR(4000),
DEMO_ACTN VARCHAR(255),
FromAccessGroupA BIT,
FromAccessGroupB BIT,
RestrictedByFailSafe BIT,
RestrictedByPrincipal BIT,
RestrictedByBrand BIT,
WhichUser INT,
AccessA INT,
AccessB INT)

Any idea what should I do?

Kristen
Test

22859 Posts

Posted - 2015-05-11 : 04:12:26

I would change @mergedMenus TableVar to a #mergedMenus TempTable and create an index (on #mergedMenus) on PAR_MENU_ID, PAR_MENU_ITM_NR

laca81
Starting Member

9 Posts

Posted - 2015-05-12 : 02:34:38

quote:
Originally posted by Kristen

I would change @mergedMenus TableVar to a #mergedMenus TempTable and create an index (on #mergedMenus) on PAR_MENU_ID, PAR_MENU_ITM_NR

Thanks! I did that and now the result is 10 sec instead of 50 sec, interesting....
I tried to make it even better. So I forgot about the left joins. I inserted the parent related items into a 'final' temp table with inner join and after it I inserted the child related records and so on... The result is 3-4 sec. It is very good but very strangefor me as well :)

Now:

INSERT INTO #Final (Main_MENU_ID
, Main_MENU_ITM_NR
, [Main Menu]
, [Main Menu Area]
, FromAccessGroup
, RestrictedByFailSafe
, RestrictedByPrincipal
, RestrictedByBrand
, DEMO_ACTN
, Main_WhichUser
, Access)
SELECT DISTINCT Parent.MENU_ID AS Main_MenuID
, Parent.MENU_ITM_NR AS Main_MENU_ITM_NR
, Parent.LTRL_TXT AS [Main Menu]
, Parent.MENU_ITM_AREA AS [Main Menu Area]
, Parent.FromAccessGroup
, Parent.RestrictedByFailSafe
, Parent.RestrictedByPrincipal
, Parent.RestrictedByBrand
, CASE WHEN Parent.MENU_ID IS NOT NULL THEN Parent.DEMO_ACTN ELSE NULL END
, Parent.WhichUser
, CASE WHEN Parent.MENU_ID IS NOT NULL THEN Parent.Access ELSE NULL END
FROM #mergedMenus AS Parent
WHERE Parent.MENU_ITM_AREA = 'D' AND Parent.MENU_ID IS NOT NULL
ORDER BY Main_MenuID

--select * from #Final

-- Child
INSERT INTO #Final (
Main_MENU_ID, Main_MENU_ITM_NR, [Main Menu]
, [Main Menu Area], FromAccessGroup
, RestrictedByFailSafe, RestrictedByPrincipal, RestrictedByBrand
, Lvl1_MENU_ID, Lvl1_MENU_ITM_NR, [Level 1 Menu]
, [Level 1 Menu Area], FromAccessGroup_1
, RestrictedByFailSafe_1, RestrictedByPrincipal_1, RestrictedByBrand_1
, DEMO_ACTN, Lvl1_WhichUser, Access)
(SELECT #Final.Main_Menu_ID, #Final.Main_MENU_ITM_NR, #Final.[Main Menu]
, #Final.[Main Menu Area], #Final.FromAccessGroup
, #Final.RestrictedByFailSafe, #Final.RestrictedByPrincipal, #Final.RestrictedByBrand
, Child.MENU_ID, Child.MENU_ITM_NR, Child.LTRL_TXT
, Child.MENU_ITM_AREA, Child.FromAccessGroup
, Child.RestrictedByFailSafe, Child.RestrictedByPrincipal, Child.RestrictedByBrand
, CASE WHEN Child.MENU_ID IS NOT NULL THEN Child.DEMO_ACTN ELSE NULL END, Child.WhichUser
, CASE WHEN Child.MENU_ID IS NOT NULL THEN Child.Access ELSE NULL END
FROM #Final
INNER JOIN #mergedMenus AS Child ON #Final.Main_MENU_ID = Child.PAR_MENU_ID AND #Final.Main_MENU_ITM_NR = Child.PAR_MENU_ITM_NR)

-- GrandChild
INSERT INTO #Final (
Main_MENU_ID, Main_MENU_ITM_NR, [Main Menu]
, [Main Menu Area], FromAccessGroup
, RestrictedByFailSafe, RestrictedByPrincipal, RestrictedByBrand
, Lvl1_MENU_ID, Lvl1_MENU_ITM_NR, [Level 1 Menu]
, [Level 1 Menu Area], FromAccessGroup_1
, RestrictedByFailSafe_1, RestrictedByPrincipal_1, RestrictedByBrand_1
--GrandChild
, Lvl2_MENU_ID, Lvl2_MENU_ITM_NR, [Level 2 Menu]
, [Level 2 Menu Area], FromAccessGroup_2
, RestrictedByFailSafe_2, RestrictedByPrincipal_2, RestrictedByBrand_2
, DEMO_ACTN, Lvl2_WhichUser, Access
)
(SELECT #Final.Main_MENU_ID, #Final.Main_MENU_ITM_NR, #Final.[Main Menu]
, #Final.[Main Menu Area], #Final.FromAccessGroup
, #Final.RestrictedByFailSafe, #Final.RestrictedByPrincipal, #Final.RestrictedByBrand
, #Final.Lvl1_MENU_ID, #Final.Lvl1_MENU_ITM_NR, #Final.[Level 1 Menu]
, #Final.[Level 1 Menu Area], #Final.FromAccessGroup_1
, #Final.RestrictedByFailSafe_1, #Final.RestrictedByPrincipal_1, #Final.RestrictedByBrand_1

, GrandChild.MENU_ID, GrandChild.MENU_ITM_NR , GrandChild.LTRL_TXT
, GrandChild.MENU_ITM_AREA, GrandChild.FromAccessGroup
, GrandChild.RestrictedByFailSafe, GrandChild.RestrictedByPrincipal, GrandChild.RestrictedByBrand
, CASE WHEN GrandChild.MENU_ID IS NOT NULL THEN GrandChild.DEMO_ACTN ELSE NULL END, GrandChild.WhichUser
, CASE WHEN GrandChild.MENU_ID IS NOT NULL THEN GrandChild.Access ELSE NULL END
FROM #Final
INNER JOIN #mergedMenus AS GrandChild ON #Final.Lvl1_MENU_ID = GrandChild.PAR_MENU_ID AND #Final.Lvl1_MENU_ITM_NR = GrandChild.PAR_MENU_ITM_NR)

--GreatGrandChild
INSERT INTO #Final (
Main_MENU_ID, Main_MENU_ITM_NR, [Main Menu]
, [Main Menu Area], FromAccessGroup
, RestrictedByFailSafe, RestrictedByPrincipal, RestrictedByBrand
, Lvl1_MENU_ID, Lvl1_MENU_ITM_NR, [Level 1 Menu]
, [Level 1 Menu Area], FromAccessGroup_1
, RestrictedByFailSafe_1, RestrictedByPrincipal_1, RestrictedByBrand_1
--GrandChild
, Lvl2_MENU_ID, Lvl2_MENU_ITM_NR, [Level 2 Menu]
, [Level 2 Menu Area], FromAccessGroup_2
, RestrictedByFailSafe_2, RestrictedByPrincipal_2, RestrictedByBrand_2
--GreatGrandChild
, Lvl3_MENU_ID, Lvl3_MENU_ITM_NR, [Level 3 Menu]
, [Level 3 Menu Area], FromAccessGroup_3
, RestrictedByFailSafe_3, RestrictedByPrincipal_3, RestrictedByBrand_3
, DEMO_ACTN, Lvl3_WhichUser, Access
)
SELECT #Final.Main_MENU_ID, #Final.Main_MENU_ITM_NR, #Final.[Main Menu]
, #Final.[Main Menu Area], #Final.FromAccessGroup
, #Final.RestrictedByFailSafe, #Final.RestrictedByPrincipal, #Final.RestrictedByBrand
, #Final.Lvl1_MENU_ID, #Final.Lvl1_MENU_ITM_NR, #Final.[Level 1 Menu]
, #Final.[Level 1 Menu Area], #Final.FromAccessGroup_1
, #Final.RestrictedByFailSafe_1, #Final.RestrictedByPrincipal_1, #Final.RestrictedByBrand_1

, #Final.Lvl2_MENU_ID, #Final.Lvl2_MENU_ITM_NR, #Final.[Level 2 Menu]
, #Final.[Level 2 Menu Area], #Final.FromAccessGroup_2
, #Final.RestrictedByFailSafe_2, #Final.RestrictedByPrincipal_2, #Final.RestrictedByBrand_2

, GreatGrandChild.MENU_ID, GreatGrandChild.MENU_ITM_NR , GreatGrandChild.LTRL_TXT
, GreatGrandChild.MENU_ITM_AREA, GreatGrandChild.FromAccessGroup
, GreatGrandChild.RestrictedByFailSafe, GreatGrandChild.RestrictedByPrincipal, GreatGrandChild.RestrictedByBrand

, CASE WHEN GreatGrandChild.MENU_ID IS NOT NULL THEN GreatGrandChild.DEMO_ACTN ELSE NULL END, GreatGrandChild.WhichUser
, CASE WHEN GreatGrandChild.MENU_ID IS NOT NULL THEN GreatGrandChild.Access ELSE NULL END
FROM #Final
INNER JOIN #mergedMenus AS GreatGrandChild ON #Final.Lvl2_MENU_ID = GreatGrandChild.PAR_MENU_ID AND #Final.Lvl2_MENU_ITM_NR = GreatGrandChild.PAR_MENU_ITM_NR

Kristen
Test

22859 Posts

Posted - 2015-05-12 : 12:13:01

You'd need to have a look at the query plan to see which indexes it is using. You might expect SQL to use IndexA but perhaps it is using IndexB (or, worse, a table scan!!). Armed with that you could have a go at trying some different indexes.

You'll probably find it easier to use a physical table, rather than a #TEMP one, in order to view query plans etc.

You might find that a CTE is more efficient than self-joining the table, and it might avoid having to produce a #TEMP table in the first place.

(EDIT: I mean several CTEs, for the "recursive" / self-join levels you need)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources