Family or Corp Tree - Code works - too slow? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

Family or Corp Tree - Code works - too slow?

Author

Topic

markiandean
Starting Member

6 Posts

Posted - 2012-11-30 : 08:20:30

Hi all,

My initial feeling happiness and surprise that I'd managed to write some code of real use (and succinctly!) for the first time, slowly disappeared when I took my code from test into prod and performance died... I hope you can help ;-)

DB: Test_DB
Table: TEST_SUBS
Columns are:

HOLDER_SEARCH_CIQ_ID
HELD_CO_NAME
HELD_CO_CIQ_ID
HOLDER_CO_NAME
PCT_OWNED

Table data without headers looks like:

4 C 3 D 0.5
3 B 2 C 0.5
2 A 1 B 0.5

Eg. Company 4 called D owns 50% of company 3 called C, company 3 owns 50% of company 2 called B, so company 4 indirectly owns 25% of company 2.

I got some code working. The results had the company I wanted to know the holdings of in column1, each company it holds in column2, how much it owns in column3 and via which co it owns its part in column4. Happy days...

Perfect... or so I thought.

This code below does the job on the test_db (with four lines), but taken into production with 13000 lines that - it seems to have perf issues:

With PARENT (ULTIMATE_PARENT_CIQ_ID, HOLDING_CIQ_ID, PCT_OWNED, INTERMEDIARY_HOLDING_CO_CIQ_ID) As
(

SELECT

B.HOLDER_SEARCH_CIQ_ID as ULTIMATE_PARENT_ID,
z.HELD_CO_CIQ_ID,
z.PCT_OWNED,
z.HOLDER_SEARCH_CIQ_ID

FROM
CARNA_TEST_DB.dbo.TEST_SUBS as z, CARNA_TEST_DB.dbo.TEST_SUBS as b
Where z.HOLDER_SEARCH_CIQ_ID = b.HOLDER_SEARCH_CIQ_ID

UNION ALL

SELECT

ULTIMATE_PARENT_CIQ_ID,
U.HELD_CO_CIQ_ID,
S.PCT_OWNED * U.PCT_OWNED,
U.HOLDER_SEARCH_CIQ_ID

FROM
CARNA_TEST_DB.dbo.TEST_SUBS U, PARENT S
Where S.HOLDING_CIQ_ID = U.HOLDER_SEARCH_CIQ_ID
)

select *
FROM PARENT

ORDER BY ULTIMATE_PARENT_CIQ_ID, INTERMEDIARY_HOLDING_CO_CIQ_ID, HOLDING_CIQ_ID
OPTION (MAXRECURSION 0)

Preformance is sooo bad that I cannot get any results AT ALL out of this now.

Any ideas?

Have a great weekend all!

Mark

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 08:44:05

Anchor query in cte
SELECT

B.HOLDER_SEARCH_CIQ_ID as ULTIMATE_PARENT_ID,
z.HELD_CO_CIQ_ID,
z.PCT_OWNED,
z.HOLDER_SEARCH_CIQ_ID

FROM
CARNA_TEST_DB.dbo.TEST_SUBS as z, CARNA_TEST_DB.dbo.TEST_SUBS as b
Where z.HOLDER_SEARCH_CIQ_ID = b.HOLDER_SEARCH_CIQ_ID

That second table and where clause isn't needed - assuming that the holder is unique.
If it's not unique then you will have a lot of duplicated rows which could be the cause of your problem.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

markiandean
Starting Member

6 Posts

Posted - 2012-11-30 : 10:16:27

Thanks for that Nigel. You're right - second table and where clause superfluous, thanks. Obviously not so succinct after all ;-)

Neither the holder_search_ciq_id nor the held_co_ciq_id are unique, but the combination of the two are (one company can only own another once of course), so I've added a primary key over both of them.

So now Im at:

-----------------------------------------------------------------------------

With PARENT (ULTIMATE_PARENT_CIQ_ID, HOLDING_CIQ_ID, PCT_OWNED, INTERMEDIARY_HOLDING_CO_CIQ_ID) As
(

SELECT

z.HOLDER_SEARCH_CIQ_ID as ULTIMATE_PARENT_ID,
z.HELD_CO_CIQ_ID,
z.PCT_OWNED,
z.HOLDER_SEARCH_CIQ_ID

FROM
CARNA_COMPANY_UNIVERSE.dbo.ALL_HOLDING_INFO as z

UNION ALL

SELECT

ULTIMATE_PARENT_CIQ_ID,
U.HELD_CO_CIQ_ID,
S.PCT_OWNED * U.PCT_OWNED,
U.HOLDER_SEARCH_CIQ_ID

FROM
CARNA_COMPANY_UNIVERSE.dbo.ALL_HOLDING_INFO U, PARENT S
Where S.HOLDING_CIQ_ID = U.HOLDER_SEARCH_CIQ_ID

)

select DISTINCT ULTIMATE_PARENT_CIQ_ID, HOLDING_CIQ_ID, PCT_OWNED, INTERMEDIARY_HOLDING_CO_CIQ_ID
FROM PARENT

ORDER BY ULTIMATE_PARENT_CIQ_ID, INTERMEDIARY_HOLDING_CO_CIQ_ID, HOLDING_CIQ_ID

OPTION (MAXRECURSION 0)

----------------------------------------------------------------------------

Adding a "distinct" in the query of the CTE "Parent" as above does not take out the multiple iterations within the CTE, any ideas how I would go about creating a "distinct" using the primary key?

Cheers,

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources