SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Family or Corp Tree - Code works - too slow?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

markiandean
Starting Member

6 Posts

Posted - 11/30/2012 :  08:20:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/30/2012 :  08:44:05  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

markiandean
Starting Member

6 Posts

Posted - 11/30/2012 :  10:16:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000