Multiple derived table slow down the performance

system · September 13, 2010, 10:15pm

we have two derived tables, with exact same logic, one is the generic table, another one is the alias table. once I put them both on a report, each with one data provider, it extremely slow down the performace. what should i do now? Make it a view, would that sovle the problem?
I tired to put them both in one data provider, then I got “Context” issue, i believe it’s because they needs to be in two dataproviders to merge them.

here is the SQL

SELECT
(case when CPTY_EXPSR_FCT.FWD_FLG = ‘F’ then ‘Forward’ when CPTY_EXPSR_FCT.FWD_FLG = ‘S’ then ‘Settled’ end) As “Forward Indicator”,
(case when CPTY_EXPSR_FCT.MRGN_CALL_FLG = ‘E’ then ‘Exclude’ when CPTY_EXPSR_FCT.MRGN_CALL_FLG = ‘N’ then ‘Not a Margin Call’ end) As “Margin Call”,
CPTY_EXPSR_FCT.TRIPTY_CD As “Triparty Code”,
CPTY_EXPSR_FCT.ASET_ALOCN_CD As “New Shell”,
CPTY_EXPSR_FCT.MKT_VALUE_USD As “Market Value”,
(case when TBF_BCKTS_DIM.TBF_BCKT_CD=‘720+’ then ‘+’ else TBF_BCKTS_DIM.TBF_BCKT_CD end) As “Bucket Code” ,
(case
when
CPTY_EXPSR_FCT.TRD_DIRCT_FLG = ‘A’
then
‘Asset’
when
CPTY_EXPSR_FCT.TRD_DIRCT_FLG = ‘L’
then
‘Liability’
else
‘0’
end) As “Trade Direction”,
( case when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = ‘E’ then ‘Ext’ when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = ‘I’ then ‘Int’ end) As “Ext\Int Indicator”,
CPTY_EXPSR_FCT.MKT_VALUE_USD As “Cash/Market Value”,
CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE As “Source Business Date”
FROM
CPTY_EXPSR_FCT,
TBF_BCKTS_DIM,
SEC_SBCATG_DIM SEC_SBCATG_DIM_ALIAS_CPTY
WHERE
( CPTY_EXPSR_FCT.BCKT_DW_KEY=TBF_BCKTS_DIM.TBF_BCKTS_DW_KEY )
AND ( CPTY_EXPSR_FCT.SEC_SBCATG_DW_KEY=SEC_SBCATG_DIM_ALIAS_CPTY.SEC_SBCATG_DW_KEY )
AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN (‘Alocn’, ‘Nontriparty’) )
–AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN (‘Shell’, ‘Nontriparty’) )
AND
(
CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE = @prompt(‘Enter Business Date:’,‘D’,‘Counterparty Concentration\Source System Business Date’,Mono,Free,)
AND
CPTY_EXPSR_FCT.ASET_ALOCN_CD = @prompt(‘Enter for New Shell:’,‘A’,‘Counterparty Concentration\New Shell’,Mono,Free,)
AND
( CPTY_EXPSR_FCT.RGN_CD=‘AMERS’ )
AND
( SEC_SBCATG_DIM_ALIAS_CPTY.TBF_INCL_EXCL_FLG=‘N’ )
AND
‘Market’ = @prompt(‘Select Cash/Market Value:’,‘A’,{‘Cash’,‘Market’},Mono,Free,)
)

UNION ALL
SELECT
(case when CPTY_EXPSR_FCT.FWD_FLG = ‘F’ then ‘Forward’ when CPTY_EXPSR_FCT.FWD_FLG = ‘S’ then ‘Settled’ end) As “Forward Indicator”,
(case when CPTY_EXPSR_FCT.MRGN_CALL_FLG = ‘E’ then ‘Exclude’ when CPTY_EXPSR_FCT.MRGN_CALL_FLG = ‘N’ then ‘Not a Margin Call’ end) As “Margin Call”,
CPTY_EXPSR_FCT.TRIPTY_CD As “Triparty Code”,
CPTY_EXPSR_FCT.ASET_ALOCN_CD As “New Shell”,
CPTY_EXPSR_FCT.CSH_USD*(-1) As “Cash Value”,
(case when TBF_BCKTS_DIM.TBF_BCKT_CD=‘720+’ then ‘+’ else TBF_BCKTS_DIM.TBF_BCKT_CD end) As “Bucket Code” ,
(case
when
CPTY_EXPSR_FCT.TRD_DIRCT_FLG = ‘A’
then
‘Asset’
when
CPTY_EXPSR_FCT.TRD_DIRCT_FLG = ‘L’
then
‘Liability’
else
‘0’
end) As “Trade Direction”,
( case when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = ‘E’ then ‘Ext’ when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = ‘I’ then ‘Int’ end) As “Ext\Int Indicator”,
–CPTY_EXPSR_FCT.CSH_USD As “Cash Value”,
CPTY_EXPSR_FCT.CSH_USD As “Cash/Market Value”,
CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE As “Source Business Date”
FROM
CPTY_EXPSR_FCT,
TBF_BCKTS_DIM,
SEC_SBCATG_DIM SEC_SBCATG_DIM_ALIAS_CPTY
WHERE
( CPTY_EXPSR_FCT.BCKT_DW_KEY=TBF_BCKTS_DIM.TBF_BCKTS_DW_KEY )
AND ( CPTY_EXPSR_FCT.SEC_SBCATG_DW_KEY=SEC_SBCATG_DIM_ALIAS_CPTY.SEC_SBCATG_DW_KEY )
– AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN (‘Alocn’, ‘Nontriparty’) )
AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN (‘Shell’, ‘Nontriparty’) )
AND
(
CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE = @prompt(‘Enter Business Date:’,‘D’,‘Counterparty Concentration\Source System Business Date’,Mono,Free,)
AND
CPTY_EXPSR_FCT.ASET_ALOCN_CD = @prompt(‘Enter for New Shell:’,‘A’,‘Counterparty Concentration\New Shell’,Mono,Free,)
AND
( CPTY_EXPSR_FCT.RGN_CD=‘AMERS’ )
AND
( SEC_SBCATG_DIM_ALIAS_CPTY.TBF_INCL_EXCL_FLG=‘N’ )
AND
‘Cash’ = @prompt(‘Select Cash/Market Value:’,‘A’,{‘Cash’,‘Market’},Mono,Free,)
)

lavinaluo (BOB member since 2005-02-14)

system · September 14, 2010, 12:28am

Hi Lavinaluo,

Your problem is strange. If you run similar queries, but not at the same time, then the SQL is cached and the second query must run faster.

But if you run the queries at the same time, it will scan the same table, use the same indexes, et. It sjow down the query, because the CPU tries to read the same part on the hard disk.

But in Desktop Intelligence you run the queries one after the other, so it must be the first case, so I’m confused. Can you please elaborate on your problem (version you use, Webi or Deski, etc). Also, please use code formatting.

Regards,
Csisz

DBArchitect (BOB member since 2009-06-23)

system · September 14, 2010, 1:39am

thanks for replying, Bohacker. I am running those two queries at the same time, as they both are 2 dataproviders in same report; I am running this report at WEBI R3; They are not quite exact same query, only one table difference, one is using TBF_BCKTS_DIM, and another one is TBF_BCKTS_DIM_alias, rest of the tables and joins are the same in the SQL. So, any idea? should i use view instead? then i can’t use prompt any more

lavinaluo (BOB member since 2005-02-14)

system · September 14, 2010, 4:20am

Hi Lavinaulo,

I rewrite your query because it is very hard to read (for me). Can you copy and paste it into your first post (and please use code formatting)? The SQL query:

SELECT
case 
  when CPTY_EXPSR_FCT.FWD_FLG = 'F' then 'Forward' 
  when CPTY_EXPSR_FCT.FWD_FLG = 'S' then 'Settled' 
end As "Forward Indicator",
case 
  when CPTY_EXPSR_FCT.MRGN_CALL_FLG = 'E' then 'Exclude' 
  when CPTY_EXPSR_FCT.MRGN_CALL_FLG = 'N' then 'Not a Margin Call' 
end As "Margin Call",
CPTY_EXPSR_FCT.TRIPTY_CD As "Triparty Code",
CPTY_EXPSR_FCT.ASET_ALOCN_CD As "New Shell",
CPTY_EXPSR_FCT.MKT_VALUE_USD As "Market Value",
case 
  when TBF_BCKTS_DIM.TBF_BCKT_CD='720+' then '+' 
  else TBF_BCKTS_DIM.TBF_BCKT_CD 
end As "Bucket Code" ,
case
  when CPTY_EXPSR_FCT.TRD_DIRCT_FLG = 'A' then 'Asset'
  when CPTY_EXPSR_FCT.TRD_DIRCT_FLG = 'L' then 'Liability'
  else '0'
end As "Trade Direction",
case 
  when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = 'E' then 'Ext' 
  when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = 'I' then 'Int' 
end As "Ext\Int Indicator",
CPTY_EXPSR_FCT.MKT_VALUE_USD As "Cash/Market Value",
CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE As "Source Business Date"
FROM
  CPTY_EXPSR_FCT,
  TBF_BCKTS_DIM,
  SEC_SBCATG_DIM SEC_SBCATG_DIM_ALIAS_CPTY
WHERE
( CPTY_EXPSR_FCT.BCKT_DW_KEY=TBF_BCKTS_DIM.TBF_BCKTS_DW_KEY )
AND ( CPTY_EXPSR_FCT.SEC_SBCATG_DW_KEY=SEC_SBCATG_DIM_ALIAS_CPTY.SEC_SBCATG_DW_KEY )
AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN ('Alocn', 'Nontriparty') )
--AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN ('Shell', 'Nontriparty') )
AND (CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE = @prompt('Enter Business Date:','D','Counterparty Concentration\Source System Business Date',Mono,Free,)
AND (CPTY_EXPSR_FCT.ASET_ALOCN_CD = @prompt('Enter for New Shell:','A','Counterparty Concentration\New Shell',Mono,Free,)
    AND CPTY_EXPSR_FCT.RGN_CD='AMERS' 
    AND SEC_SBCATG_DIM_ALIAS_CPTY.TBF_INCL_EXCL_FLG='N' 
    AND 'Market' = @prompt('Select Cash/Market Value:','A',{'Cash','Market'},Mono,Free,)
    )
UNION ALL
SELECT
case 
  when CPTY_EXPSR_FCT.FWD_FLG = 'F' then 'Forward' 
  when CPTY_EXPSR_FCT.FWD_FLG = 'S' then 'Settled' 
end As "Forward Indicator",
case 
  when CPTY_EXPSR_FCT.MRGN_CALL_FLG = 'E' then 'Exclude' 
  when CPTY_EXPSR_FCT.MRGN_CALL_FLG = 'N' then 'Not a Margin Call' 
end As "Margin Call",
CPTY_EXPSR_FCT.TRIPTY_CD As "Triparty Code",
CPTY_EXPSR_FCT.ASET_ALOCN_CD As "New Shell",
CPTY_EXPSR_FCT.CSH_USD*(-1) As "Cash Value",
case 
  when TBF_BCKTS_DIM.TBF_BCKT_CD='720+' then '+' 
  else TBF_BCKTS_DIM.TBF_BCKT_CD 
end As "Bucket Code" ,
case
  when CPTY_EXPSR_FCT.TRD_DIRCT_FLG = 'A' then 'Asset'
  when CPTY_EXPSR_FCT.TRD_DIRCT_FLG = 'L' then 'Liability'
  else '0'
end As "Trade Direction",
case 
  when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = 'E' then 'Ext' 
  when CPTY_EXPSR_FCT.CPTY_EXTL_INTRNL_FLG = 'I' then 'Int' 
end As "Ext\Int Indicator"
--CPTY_EXPSR_FCT.CSH_USD As "Cash Value",
CPTY_EXPSR_FCT.CSH_USD As "Cash/Market Value",
CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE As "Source Business Date"
FROM
  CPTY_EXPSR_FCT,
  TBF_BCKTS_DIM,
  SEC_SBCATG_DIM SEC_SBCATG_DIM_ALIAS_CPTY
WHERE
( CPTY_EXPSR_FCT.BCKT_DW_KEY=TBF_BCKTS_DIM.TBF_BCKTS_DW_KEY )
AND ( CPTY_EXPSR_FCT.SEC_SBCATG_DW_KEY=SEC_SBCATG_DIM_ALIAS_CPTY.SEC_SBCATG_DW_KEY )
-- AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN ('Alocn', 'Nontriparty') )
AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN ('Shell', 'Nontriparty') )
AND (CPTY_EXPSR_FCT.SRC_DATA_BUS_DTE = @prompt('Enter Business Date:','D','Counterparty Concentration\Source System Business Date',Mono,Free,)
AND CPTY_EXPSR_FCT.ASET_ALOCN_CD = @prompt('Enter for New Shell:','A','Counterparty Concentration\New Shell',Mono,Free,)
AND ( CPTY_EXPSR_FCT.RGN_CD='AMERS' )
AND ( SEC_SBCATG_DIM_ALIAS_CPTY.TBF_INCL_EXCL_FLG='N' )
AND 'Cash' = @prompt('Select Cash/Market Value:','A',{'Cash','Market'},Mono,Free,))

As far as I see you select a join based on the Select Cash/Market Value prompt. So here is the trick (I have never tried this, but we will find out very quickly whether it works). You have to insert a join, which contains this prompt. And the syntax of this join:


(@prompt('Select Cash/Market Value:','A',{'Cash','Market'},Mono,Free,)) = 'Cash'  AND( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN ('Alocn', 'Nontriparty') )) OR
  (@prompt('Select Cash/Market Value:','A',{'Cash','Market'},Mono,Free,)) = 'Market' AND ( ( CPTY_EXPSR_FCT.TRIPTY_CD ) IN ('Shell', 'Nontriparty') ))

And then you wont have to choose between the contexts in your query. Though this Cash/Market prompt will come up every time you use this two tables together.

Regards,
Csisz

DBArchitect (BOB member since 2009-06-23)

MarkP · September 14, 2010, 7:58am

What indexes have you got on your tables?

system · September 14, 2010, 1:32pm

hi, BOhacketer, i guess i am not quite sure how that join would help this issue?

Mark, i am not sure the index on the table, would that help you think or there is any other way around?

lavinaluo (BOB member since 2005-02-14)

system · September 14, 2010, 1:37pm

You avoid the union and querying the same table twice. Meanwhile I found out an error in my solution, but I leave it to you to solve the problem.

Please edit your posts!

Regards,
Csisz

DBArchitect (BOB member since 2009-06-23)

MarkP · September 14, 2010, 2:20pm

Good indexing always helps.

system · September 14, 2010, 2:32pm

i got your point, thanks. I tried it. I remove the union and add the or funtion as you suggestioned, it works good with Market value , but having problem with “Cash value” as I defined here—"CPTY_EXPSR_FCT.MKT_VALUE_USD As “Cash/Market Value”,.

So my questions , how do i make it work for “Cash value”?

lavinaluo (BOB member since 2005-02-14)

system · September 14, 2010, 3:13pm

the SQL itsel works pretty fast at database side, it only has 1117 rows and comes back in a few seconds. But once I am creating variables in the report and slows down a lot. is that mean that every time I create a object, it will still go and reference the SQL or is there any other reasons? Please clearify me if you can

lavinaluo (BOB member since 2005-02-14)

system · September 14, 2010, 3:25pm

How does your report look like? Is it the only Data Provider, or there are more? Are these Data Provider’s linked? How does your variable definitions look like?

With this remark it seems that it is a reporting issue - I believed that we have to correct an SQL which queries a table which contains millions of rows.

Once you retrieved the data it sits on the server, and you have nothing to do with the database.

Regards,
Csisz

DBArchitect (BOB member since 2009-06-23)

system · September 14, 2010, 3:36pm

Creating variables within a report has no impact on query performance.

anorak (BOB member since 2002-09-13)

system · September 14, 2010, 6:59pm

then what could be the issue? I could not figure it out. I tried 'Explain plan", it looks good. Does that mean if everytime I create a object on the report, since it’s using derived table, it hits back to query again? Any idea any one?

lavinaluo (BOB member since 2005-02-14)

system · September 14, 2010, 7:33pm

Hi,

I don’t think it is a database problem. I think it is a reporting problem. I have two questions:
[list]

Is it the only Data Provider in the report
If not, than is any of the data providers linked
[/list]
Data Provider linking can be the problem.

Q: What is it?
A: It is like joining two tables together.

Q: What can go wrong?
A:If the two Data Provider is not properly linked, than it creates a cartesian product. 1000 row quickly becomes 1000000, and it consumes memory and pushes up CPU usage.

Q: What is the solution?
A: Link the two Data Provider correctly. Linking is done by BO automatically, the same objects are linked. Now if the first query contains an object from the original table, and the second query from an alias, then it is not the same for BO => then it is not linked. You can link the Data Providers manually in the Data Manager.

Now I have to stress , that these are mere assumptions, because I know nothing about your reports. So I can be wrong, of course, and the problem can lie elsewhere…

Thanks,
Csisz

DBArchitect (BOB member since 2009-06-23)

MarkP · September 15, 2010, 8:31am

So does the query result data display fine in a basic report table?