Report performance vs. SQL performance

[system]

I have a complex report which resolves to 14 SQL queries in the data provider. I’m trying to test the impact of some volume increases which will only affect one data table - and consequently only a limited number of the SQL statements.

On a sample run the report takes ~45 minutes, and when I extracted the SQL statements and ran those separately they took a total of 10 minutes to run. So what I need help in understanding is where the extra 35 minutes for the report is spent. Is it just on synchronisation of the results and building the information in a way which is efficient for presentation? Or are there elements I’m missing?

The nature of the queries and volume increase means that this element shouldn’t be impacted (as data is grouped up to a higher level in the queries). Hence my need to be clear on where the bulk of this time is spent.

Thanks.

---

DanDensley (BOB member since 2009-05-12)

[system]

Try to build 14 different reports for each query and then compare the performance, if it take 10 mins only then may be cache problem.

---

Rakesh_K (BOB member since 2007-12-11)

[system]

If I had 14 different reports then it wouldn’t be doing the same thing though. The report with 14 queries displays the results in a single table - i.e. all the results are linked and are displayed together in some form. So I would expect some synchronisation over head - the question is whether it is normal or possible to have a synchronisation overhead like this?

---

DanDensley (BOB member since 2009-05-12)

[system]

On a sample run the report takes ~45 minutes, and when I extracted the SQL statements and ran those separately they took a total of 10 minutes to run.

Are you returning all the results? BO returns all the results, whereas, generally, a SQL editor returns the first x rows.

Did you use the BO user account to do this?

You could also be facing caching issues which can give a false impression of speed, as Rakesh has suggested, depending upon the queries being run.

I would expect some time lag depending on the number of variables and merging on the report.

However, the difference should never be as large as you have suggested.

---

Mak 1 (BOB member since 2005-01-06)

[system]

All results were included in the SQL Editor and the connection to the database was via the same user. Caching for the SQL run outside BO could be a potential reason. I’ll try re-running the queries.

I suppose it could also be a performance limitation on my laptop locally? Memory limitation causing the synchronisation? I’ll also re-run the report with just BO running.

Thanks for the responses.

---

DanDensley (BOB member since 2009-05-12)

[system]

All results were included in the SQL Editor

You have it set to return all rows in one go, yes?

I suppose it could also be a performance limitation on my laptop locally? Memory limitation causing the synchronisation?

Dan, very much so, the whole data cube makes its way and is held in memory for use by Deski, before report engine calcuations start.

Compare the query times, in the data dialogue box, that will show you how long each SQL statement took to run.

You will then see how long the report then takes to actually “display” the results.

---

Mak 1 (BOB member since 2005-01-06)

[system]

@Mak 1

All results were included in the SQL Editor

You have it set to return all rows in one go, yes?

Yep

Dan, very much so, the whole data cube makes its way and is held in memory for use by Deski, before report engine calcuations start.

Compare the query times, in the data dialogue box, that will show you how long each SQL statement took to run.

You will then see how long the report then takes to actually “display” the results.

Because it is all from one data provider I just get a single query execution time - 40 minutes - not all that helpful. So the report engine calculations are minimal.

As data is brought in there must be some work going on there to form the data cube. The number of rows so far does not go up anywhere near as quickly as the SQL query through PL/SQL Developer.

---

DanDensley (BOB member since 2009-05-12)

[system]

The number of rows so far does not go up anywhere near as quickly as the SQL query through PL/SQL Developer.

You should look at the connections settings, in Designer. Maybe you can change the array fetch size.

Discussed here:-

https://bobj-board.org/t/140429

---

Mak 1 (BOB member since 2005-01-06)

[system]

Pushed the Array Fetch Size up to 200 and the report run time has come down to 10m with the query time reported in BO at 8m. Thank you very much for your help and pointers - I’ve learned a lot.

I noticed the Array Fetch Size will not go above 200 - how is that restricted? I am connecting to an Oracle 10 Database. I took a look at the service defined in Net Manager - under Advanced in Address Configuration there are some Total Send/Receive Buffer sizes - both unset.

---

DanDensley (BOB member since 2009-05-12)

[system]

Great 8) .

I hope you, also, took notice of Dave Rathbuns quote, here:-

Array fetch size specifies the number of rows to be queued on the server before a data packet is sent down to the client. It’s one of the hardest items to tune because there are so many variables involved. One report might run best at 200 (what you said) but other more complex reports with larger data providers might run better at 50.

If you alter the setting based on testing of a single report, you have tuned only that single report. You need to run multiple reports using different settings and test them all.

As for the other settings, I would have to get the books out, I believe these are resonably new…

---

Mak 1 (BOB member since 2005-01-06)