| Author |
 Topic  |
|
|
markiandean
Starting Member
6 Posts |
 Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/30/2012 : 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 - 11/30/2012 : 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 |
|
|
| |
Topic |
|
|
|
Family or Corp Tree - Code works - too slow?
