nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Master query and subqueries (only run once each) using multiple datasets

jonc

I have a report where I am running one main query to pull top level results based on user-defined filter criteria. In addition, there are some subqueries that will have to be run per the results of this main query.

My intention is to run the queries (main and all subqueries) just once each. I will then add filters on the result rows to limit the subquery results to only those that match the main query on a specific column.

I have done this on a report before where I created a table that is bound to my main query's dataset. I then added a list in the table's detail. The list is bound to a subquery's dataset and then filtered where one of its columns matches one in the parent table.

In this manner, only two queries are run, and I match up correctly.

Now, my issue is that I attempted to follow this pattern in a different report and now the subquery is getting run once per result row of the main query. I am not sure why this is happening as I have not tied the subquery directly to any row value of the main query aside from the filter. The one difference between this and the previous report is that I have a grid inside the main table. I have tried pulling the list out of that grid, however it seems like I get the N + 1 query issue any time the list (bound to my subquery) is inside of the main table (bound to my main query).

Any idea how I might be able to force BIRT to only run each dataset's backing query once?

Find more posts tagged with

Comments

mwilliams

Hi jonc,

So, your two reports run differently, even though set up exactly the same? Meaning, when you remove the sub-list/table from the grid, you still get the different behavior? What version of BIRT are you using?

jonc

Allow me to answer your questions in order: 
<blockquote class='ipsBlockquote' data-author="mwilliams">So, your two reports run differently, even though set up exactly the same? Meaning, when you remove the sub-list/table from the grid, you still get the different behavior?</blockquote>
No, and the hierarchy is as follows: table( grid( list() ) ). And table() is bound to my main dataset. So, when I move the list outside the table, the subquery bound to this list through the dataset is only run once. 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">What version of BIRT are you using?</blockquote>
I am running BIRT 2.5.1.

mwilliams

Right, but in your first post, you say you tried pulling the list out of the grid, but you still get the same amount of calls to the database. Even though you said that in the other report you already had, with a list within a table only ran once each.

Are you trying to limit hits on the database for a specific reason? Or could using a dataSet parameter binding tied to a value in your outer table work for you? It'd be faster than filtering the inner table every time, but it would hit up the database for each outer table value.

jonc

This particular report will commonly be used to generate data for upwards of one hundred main query results. Add to that the fact that there will be 5 subqueries for each of these using a parameter bound to a dataset row value, I think upwards of 500 queries on one report would be too much.

I am only testing with one subquery right now to figure out how to limit it.

jonc

I managed to find a workaround for this issue.

It requires adding hidden lists on the report that are bound to all of my subqueries. I then use those lists as report item bindings inside the table that is bound to my master query.

Controlling visibility based on the foreign key constraints is handled through the visibility parameter on the individual elements, using an expression.

This is somewhat of a hack and I feel like there should be some hint a developer can give the ROM saying that a subquery does not have to be re-run for every containing element's row.

mwilliams

jonc, 
 
Glad you found a solution. You can always send in enhancement requests/bug reports to <a class='bbc_url' href='http://www.birt-exchange.org/bug-reporting/'>Report Bugs - BIRT Exchange</a> on this and see what they say.

jonc

I had originally thought the solution I described in my previous post actually only ran each query once. After some testing with larger data sets, I find that this is incorrect. BIRT is simply not logging each query run separately as I thought it would.

As a result, one of my more complicated reports is taking far too long to complete for even small result sets.

I am still at a loss about how to suggest to BIRT not to re-run certain queries. I've tried fiddling with the cache for data-engine advanced property with no luck.

I really need this report to return in a reasonable amount of time, and I can't see any way to define only one dataset and still have it display correctly. Any help will be greatly appreciated.

mwilliams

jonc,

Can you explain what you're tying to do that you can't find a way to do if you only use one dataSet?

jonc

I am trying to display a result set that can have nested tabular data:

Result 1:
-Field 1
-Field 2
-Subtable 1:
- -stField 1.1
- -stField 1.2
-Subtable 2
- -stField 2.1
- -Subtable 2.1:
- - -ststField 2.1.1
- - -ststField 2.1.2
Result 2:
-....
Where fields above are scalar result column data and subtables can be easily populated by subqueries. This gives me three levels of nesting for the returned data.

I tried setting up separate data sources and binding them to each table. But this makes each sub table's query re-run for each of its parent records. This becomes a nightmare with more than one nested table and is undesirable even at one.

My second attempt was to just run one massive query, joining all the levels into a flat result set and then group the data in the report. This results in data that I cannot format how I want and the grouping doesn't seem to work in that I am getting duplicate data in my subtables.