Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Performance issues with db query being executed several times
Performance issues with db query being executed several times [message #362721] Thu, 22 May 2008 10:35 Go to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
Hello all,

I have noticed that when generating a report with BIRT, a same SQL query
may actually be executed several times.
This behaviour is probably ok for small queries but when a query takes up
to a few seconds you surely do not want it to be executed several times.

My first example is I create a report with one datasource (a mysql server
in my case), one dataset (Say "SELECT 1 AS column1"), and one data item
that displays the value of column1. After running the report I can see in
the mysql query log that the query "SELECT 1 AS column1" was executed
twice.

Now even worse: I change my query to say "SELECT 1 AS column1, 2 AS
column2, 3 As column3" and add 2 more data times to the report so as to
display column2 and column3. On generation of the report, the query will
actually be executed 6 times!!!

I have given you a basic example here, but I have reports which execute
quite complicated queries and display the results at different places in
the report. I can see that the queries are executed several times on my
db server and that turns me mad!!

Is this an intended behaviour of BIRT? If yes is there a way to avoid the
queries from being executed several times. If not is there a plan to fix
the issue?

Thanks a lot in advance, this is very critical for me.

I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37 and
using mysql jdbc connector 3.0


Ben
Re: Performance issues with db query being executed several times [message #362739 is a reply to message #362721] Thu, 22 May 2008 15:46 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jasonweathersby.alltel.net

Serbenet,

Verify that each individual data item is not bound to a dataset. They
should be in a table that is bound to the dataset. If they are bound to
the dataset, each will execute the query. In 2.2.2 you should also be
able to bind to a previous report item. For example create on dataset
and bind it to a table1, name it in the general properties tab. Add a
second table, select the binding tab and bind it to a report item. The
first table should be in the selection.

Jason


serbenet wrote:
> Hello all,
>
> I have noticed that when generating a report with BIRT, a same SQL query
> may actually be executed several times.
> This behaviour is probably ok for small queries but when a query takes
> up to a few seconds you surely do not want it to be executed several times.
>
> My first example is I create a report with one datasource (a mysql
> server in my case), one dataset (Say "SELECT 1 AS column1"), and one
> data item that displays the value of column1. After running the report I
> can see in the mysql query log that the query "SELECT 1 AS column1" was
> executed twice.
>
> Now even worse: I change my query to say "SELECT 1 AS column1, 2 AS
> column2, 3 As column3" and add 2 more data times to the report so as to
> display column2 and column3. On generation of the report, the query will
> actually be executed 6 times!!!
>
> I have given you a basic example here, but I have reports which execute
> quite complicated queries and display the results at different places in
> the report. I can see that the queries are executed several times on my
> db server and that turns me mad!!
>
> Is this an intended behaviour of BIRT? If yes is there a way to avoid
> the queries from being executed several times. If not is there a plan to
> fix the issue?
>
> Thanks a lot in advance, this is very critical for me.
>
> I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37 and
> using mysql jdbc connector 3.0
>
>
> Ben
>
>
>
>
Re: Performance issues with db query being executed several times [message #362742 is a reply to message #362739] Thu, 22 May 2008 16:17 Go to previous messageGo to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
Thanks for the quick reply.

Each data item in my reports are bound to the dataset so, following your
explanation,
I understand now why the queries get executed several times.
Is this a normal behaviour or is this something that is going to be
changed in a later version?
Say if 2 data items use the same dataset then surely the data do not need
to be fetched twice from the database.

Now in some places I can put all the data items in one big table, and only
bind this table to the dataset. This solves the issue. But depending on
the layout of my report, I can not always put all data items in one big
table. I have tried binding to a previous report item as you mentionned,
but it seems that I can only bind to previous chart items, I do not have
the choice to select a previous data item. Is this a limitation of 2.2.2?

Thanks again

Ben



On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
<jasonweathersby@alltel.net> wrote:

> Serbenet,
>
> Verify that each individual data item is not bound to a dataset. They
> should be in a table that is bound to the dataset. If they are bound to
> the dataset, each will execute the query. In 2.2.2 you should also be
> able to bind to a previous report item. For example create on dataset
> and bind it to a table1, name it in the general properties tab. Add a
> second table, select the binding tab and bind it to a report item. The
> first table should be in the selection.
>
> Jason
>
>
> serbenet wrote:
>> Hello all,
>> I have noticed that when generating a report with BIRT, a same SQL
>> query may actually be executed several times.
>> This behaviour is probably ok for small queries but when a query takes
>> up to a few seconds you surely do not want it to be executed several
>> times.
>> My first example is I create a report with one datasource (a mysql
>> server in my case), one dataset (Say "SELECT 1 AS column1"), and one
>> data item that displays the value of column1. After running the report
>> I can see in the mysql query log that the query "SELECT 1 AS column1"
>> was executed twice.
>> Now even worse: I change my query to say "SELECT 1 AS column1, 2 AS
>> column2, 3 As column3" and add 2 more data times to the report so as to
>> display column2 and column3. On generation of the report, the query
>> will actually be executed 6 times!!!
>> I have given you a basic example here, but I have reports which
>> execute quite complicated queries and display the results at different
>> places in the report. I can see that the queries are executed several
>> times on my db server and that turns me mad!!
>> Is this an intended behaviour of BIRT? If yes is there a way to avoid
>> the queries from being executed several times. If not is there a plan
>> to fix the issue?
>> Thanks a lot in advance, this is very critical for me.
>> I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37 and
>> using mysql jdbc connector 3.0
>> Ben
>>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Re: Performance issues with db query being executed several times [message #362753 is a reply to message #362742] Thu, 22 May 2008 18:13 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jasonweathersby.alltel.net

Caching of datasets is being improved, but event now yours should not
have a problem. The first time you use a data set on a report element,
name it. This is in the general properties for the item. Then anytime
you want to use the query again just select the binding tab of your new
element, select the radial for report items and your named items should
be in the list. The reason you are seeing only charts, it because those
are probably the only items named in your report.

Jason


serbenet wrote:
> Thanks for the quick reply.
>
> Each data item in my reports are bound to the dataset so, following your
> explanation,
> I understand now why the queries get executed several times.
> Is this a normal behaviour or is this something that is going to be
> changed in a later version?
> Say if 2 data items use the same dataset then surely the data do not
> need to be fetched twice from the database.
>
> Now in some places I can put all the data items in one big table, and
> only bind this table to the dataset. This solves the issue. But
> depending on the layout of my report, I can not always put all data
> items in one big table. I have tried binding to a previous report item
> as you mentionned, but it seems that I can only bind to previous chart
> items, I do not have the choice to select a previous data item. Is this
> a limitation of 2.2.2?
>
> Thanks again
>
> Ben
>
>
>
> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
> <jasonweathersby@alltel.net> wrote:
>
>> Serbenet,
>>
>> Verify that each individual data item is not bound to a dataset. They
>> should be in a table that is bound to the dataset. If they are bound
>> to the dataset, each will execute the query. In 2.2.2 you should also
>> be able to bind to a previous report item. For example create on
>> dataset and bind it to a table1, name it in the general properties
>> tab. Add a second table, select the binding tab and bind it to a
>> report item. The first table should be in the selection.
>>
>> Jason
>>
>>
>> serbenet wrote:
>>> Hello all,
>>> I have noticed that when generating a report with BIRT, a same SQL
>>> query may actually be executed several times.
>>> This behaviour is probably ok for small queries but when a query
>>> takes up to a few seconds you surely do not want it to be executed
>>> several times.
>>> My first example is I create a report with one datasource (a mysql
>>> server in my case), one dataset (Say "SELECT 1 AS column1"), and one
>>> data item that displays the value of column1. After running the
>>> report I can see in the mysql query log that the query "SELECT 1 AS
>>> column1" was executed twice.
>>> Now even worse: I change my query to say "SELECT 1 AS column1, 2 AS
>>> column2, 3 As column3" and add 2 more data times to the report so as
>>> to display column2 and column3. On generation of the report, the
>>> query will actually be executed 6 times!!!
>>> I have given you a basic example here, but I have reports which
>>> execute quite complicated queries and display the results at
>>> different places in the report. I can see that the queries are
>>> executed several times on my db server and that turns me mad!!
>>> Is this an intended behaviour of BIRT? If yes is there a way to
>>> avoid the queries from being executed several times. If not is there
>>> a plan to fix the issue?
>>> Thanks a lot in advance, this is very critical for me.
>>> I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37
>>> and using mysql jdbc connector 3.0
>>> Ben
>>>
>
>
>
Re: Performance issues with db query being executed several times [message #362820 is a reply to message #362753] Tue, 27 May 2008 11:45 Go to previous messageGo to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
Thanks.

After having given a name to my data items, I could see them in the list
of previous data items.
I have now binded only one data item to my dataset and all other data
items are binded to this previous data item.
This definitely seems to improve things and the query is now executed only
twice (instead of 16 times before!!).
Also I do not understand why it is still executed twice when once should
be enough, but that's definitely already a big improvement.

I have also tested the behaviour with 2.3 RC1 and data set caching seems
to have been improved a lot.
In any cases, a query is never executed more than twice, even if the
report contains several data items directly bound to the dataset.
But also and again the query is executed twice when once should be enough.
Any thoughts on this? Is it really necessary to execute the query twice?

Thanks again for your help.

Ben

On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby
<jasonweathersby@alltel.net> wrote:

> Caching of datasets is being improved, but event now yours should not
> have a problem. The first time you use a data set on a report element,
> name it. This is in the general properties for the item. Then anytime
> you want to use the query again just select the binding tab of your new
> element, select the radial for report items and your named items should
> be in the list. The reason you are seeing only charts, it because those
> are probably the only items named in your report.
>
> Jason
>
>
> serbenet wrote:
>> Thanks for the quick reply.
>> Each data item in my reports are bound to the dataset so, following
>> your explanation,
>> I understand now why the queries get executed several times.
>> Is this a normal behaviour or is this something that is going to be
>> changed in a later version?
>> Say if 2 data items use the same dataset then surely the data do not
>> need to be fetched twice from the database.
>> Now in some places I can put all the data items in one big table, and
>> only bind this table to the dataset. This solves the issue. But
>> depending on the layout of my report, I can not always put all data
>> items in one big table. I have tried binding to a previous report item
>> as you mentionned, but it seems that I can only bind to previous chart
>> items, I do not have the choice to select a previous data item. Is this
>> a limitation of 2.2.2?
>> Thanks again
>> Ben
>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
>> <jasonweathersby@alltel.net> wrote:
>>
>>> Serbenet,
>>>
>>> Verify that each individual data item is not bound to a dataset. They
>>> should be in a table that is bound to the dataset. If they are bound
>>> to the dataset, each will execute the query. In 2.2.2 you should also
>>> be able to bind to a previous report item. For example create on
>>> dataset and bind it to a table1, name it in the general properties
>>> tab. Add a second table, select the binding tab and bind it to a
>>> report item. The first table should be in the selection.
>>>
>>> Jason
>>>
>>>
>>> serbenet wrote:
>>>> Hello all,
>>>> I have noticed that when generating a report with BIRT, a same SQL
>>>> query may actually be executed several times.
>>>> This behaviour is probably ok for small queries but when a query
>>>> takes up to a few seconds you surely do not want it to be executed
>>>> several times.
>>>> My first example is I create a report with one datasource (a mysql
>>>> server in my case), one dataset (Say "SELECT 1 AS column1"), and one
>>>> data item that displays the value of column1. After running the
>>>> report I can see in the mysql query log that the query "SELECT 1 AS
>>>> column1" was executed twice.
>>>> Now even worse: I change my query to say "SELECT 1 AS column1, 2 AS
>>>> column2, 3 As column3" and add 2 more data times to the report so as
>>>> to display column2 and column3. On generation of the report, the
>>>> query will actually be executed 6 times!!!
>>>> I have given you a basic example here, but I have reports which
>>>> execute quite complicated queries and display the results at
>>>> different places in the report. I can see that the queries are
>>>> executed several times on my db server and that turns me mad!!
>>>> Is this an intended behaviour of BIRT? If yes is there a way to
>>>> avoid the queries from being executed several times. If not is there
>>>> a plan to fix the issue?
>>>> Thanks a lot in advance, this is very critical for me.
>>>> I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37
>>>> and using mysql jdbc connector 3.0
>>>> Ben
>>>>
>>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Re: Performance issues with db query being executed several times [message #362822 is a reply to message #362820] Tue, 27 May 2008 14:42 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jasonweathersby.alltel.net

Ben,

On my system it is executed twice. Do yo have any look ahead aggregates
in the report? Are you grouping?

Jason

serbenet wrote:
> Thanks.
>
> After having given a name to my data items, I could see them in the list
> of previous data items.
> I have now binded only one data item to my dataset and all other data
> items are binded to this previous data item.
> This definitely seems to improve things and the query is now executed
> only twice (instead of 16 times before!!).
> Also I do not understand why it is still executed twice when once should
> be enough, but that's definitely already a big improvement.
>
> I have also tested the behaviour with 2.3 RC1 and data set caching seems
> to have been improved a lot.
> In any cases, a query is never executed more than twice, even if the
> report contains several data items directly bound to the dataset.
> But also and again the query is executed twice when once should be
> enough. Any thoughts on this? Is it really necessary to execute the
> query twice?
>
> Thanks again for your help.
>
> Ben
>
> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby
> <jasonweathersby@alltel.net> wrote:
>
>> Caching of datasets is being improved, but event now yours should not
>> have a problem. The first time you use a data set on a report
>> element, name it. This is in the general properties for the item.
>> Then anytime you want to use the query again just select the binding
>> tab of your new element, select the radial for report items and your
>> named items should be in the list. The reason you are seeing only
>> charts, it because those are probably the only items named in your
>> report.
>>
>> Jason
>>
>>
>> serbenet wrote:
>>> Thanks for the quick reply.
>>> Each data item in my reports are bound to the dataset so, following
>>> your explanation,
>>> I understand now why the queries get executed several times.
>>> Is this a normal behaviour or is this something that is going to be
>>> changed in a later version?
>>> Say if 2 data items use the same dataset then surely the data do not
>>> need to be fetched twice from the database.
>>> Now in some places I can put all the data items in one big table,
>>> and only bind this table to the dataset. This solves the issue. But
>>> depending on the layout of my report, I can not always put all data
>>> items in one big table. I have tried binding to a previous report
>>> item as you mentionned, but it seems that I can only bind to previous
>>> chart items, I do not have the choice to select a previous data item.
>>> Is this a limitation of 2.2.2?
>>> Thanks again
>>> Ben
>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
>>> <jasonweathersby@alltel.net> wrote:
>>>
>>>> Serbenet,
>>>>
>>>> Verify that each individual data item is not bound to a dataset.
>>>> They should be in a table that is bound to the dataset. If they are
>>>> bound to the dataset, each will execute the query. In 2.2.2 you
>>>> should also be able to bind to a previous report item. For example
>>>> create on dataset and bind it to a table1, name it in the general
>>>> properties tab. Add a second table, select the binding tab and bind
>>>> it to a report item. The first table should be in the selection.
>>>>
>>>> Jason
>>>>
>>>>
>>>> serbenet wrote:
>>>>> Hello all,
>>>>> I have noticed that when generating a report with BIRT, a same SQL
>>>>> query may actually be executed several times.
>>>>> This behaviour is probably ok for small queries but when a query
>>>>> takes up to a few seconds you surely do not want it to be executed
>>>>> several times.
>>>>> My first example is I create a report with one datasource (a mysql
>>>>> server in my case), one dataset (Say "SELECT 1 AS column1"), and
>>>>> one data item that displays the value of column1. After running the
>>>>> report I can see in the mysql query log that the query "SELECT 1 AS
>>>>> column1" was executed twice.
>>>>> Now even worse: I change my query to say "SELECT 1 AS column1, 2
>>>>> AS column2, 3 As column3" and add 2 more data times to the report
>>>>> so as to display column2 and column3. On generation of the report,
>>>>> the query will actually be executed 6 times!!!
>>>>> I have given you a basic example here, but I have reports which
>>>>> execute quite complicated queries and display the results at
>>>>> different places in the report. I can see that the queries are
>>>>> executed several times on my db server and that turns me mad!!
>>>>> Is this an intended behaviour of BIRT? If yes is there a way to
>>>>> avoid the queries from being executed several times. If not is
>>>>> there a plan to fix the issue?
>>>>> Thanks a lot in advance, this is very critical for me.
>>>>> I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37
>>>>> and using mysql jdbc connector 3.0
>>>>> Ben
>>>>>
>>>
>
>
>
Re: Performance issues with db query being executed several times [message #362826 is a reply to message #362822] Tue, 27 May 2008 15:21 Go to previous messageGo to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
------------1LBzGYjMGE1wAlgA4HbbU0
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
Content-Transfer-Encoding: 7bit

No I don't have any look ahead aggregates and I don't use grouping.

I have attached a simple report showing the issue on my system.
When looking at the logs of the mysql server I can see the query "select 1
AS col1" being executed twice.

Here is a description of my system:
- Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT 2.2.2)
running on windows XP pro sp2 with jdk1.6.0_03
- The databse server is a mysql server version 5.0.37 running on redhat
enterprise 4.
- The JDBC connector used is the official JDBC driver for MySQL, MySQL
Connector/J 5.1.6.

Ben


On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby
<jasonweathersby@alltel.net> wrote:

> Ben,
>
> On my system it is executed twice. Do yo have any look ahead aggregates
> in the report? Are you grouping?
>
> Jason
>
> serbenet wrote:
>> Thanks.
>> After having given a name to my data items, I could see them in the
>> list of previous data items.
>> I have now binded only one data item to my dataset and all other data
>> items are binded to this previous data item.
>> This definitely seems to improve things and the query is now executed
>> only twice (instead of 16 times before!!).
>> Also I do not understand why it is still executed twice when once
>> should be enough, but that's definitely already a big improvement.
>> I have also tested the behaviour with 2.3 RC1 and data set caching
>> seems to have been improved a lot.
>> In any cases, a query is never executed more than twice, even if the
>> report contains several data items directly bound to the dataset.
>> But also and again the query is executed twice when once should be
>> enough. Any thoughts on this? Is it really necessary to execute the
>> query twice?
>> Thanks again for your help.
>> Ben
>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby
>> <jasonweathersby@alltel.net> wrote:
>>
>>> Caching of datasets is being improved, but event now yours should not
>>> have a problem. The first time you use a data set on a report
>>> element, name it. This is in the general properties for the item.
>>> Then anytime you want to use the query again just select the binding
>>> tab of your new element, select the radial for report items and your
>>> named items should be in the list. The reason you are seeing only
>>> charts, it because those are probably the only items named in your
>>> report.
>>>
>>> Jason
>>>
>>>
>>> serbenet wrote:
>>>> Thanks for the quick reply.
>>>> Each data item in my reports are bound to the dataset so, following
>>>> your explanation,
>>>> I understand now why the queries get executed several times.
>>>> Is this a normal behaviour or is this something that is going to be
>>>> changed in a later version?
>>>> Say if 2 data items use the same dataset then surely the data do not
>>>> need to be fetched twice from the database.
>>>> Now in some places I can put all the data items in one big table,
>>>> and only bind this table to the dataset. This solves the issue. But
>>>> depending on the layout of my report, I can not always put all data
>>>> items in one big table. I have tried binding to a previous report
>>>> item as you mentionned, but it seems that I can only bind to previous
>>>> chart items, I do not have the choice to select a previous data item.
>>>> Is this a limitation of 2.2.2?
>>>> Thanks again
>>>> Ben
>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
>>>> <jasonweathersby@alltel.net> wrote:
>>>>
>>>>> Serbenet,
>>>>>
>>>>> Verify that each individual data item is not bound to a dataset.
>>>>> They should be in a table that is bound to the dataset. If they are
>>>>> bound to the dataset, each will execute the query. In 2.2.2 you
>>>>> should also be able to bind to a previous report item. For example
>>>>> create on dataset and bind it to a table1, name it in the general
>>>>> properties tab. Add a second table, select the binding tab and bind
>>>>> it to a report item. The first table should be in the selection.
>>>>>
>>>>> Jason
>>>>>
>>>>>
>>>>> serbenet wrote:
>>>>>> Hello all,
>>>>>> I have noticed that when generating a report with BIRT, a same SQL
>>>>>> query may actually be executed several times.
>>>>>> This behaviour is probably ok for small queries but when a query
>>>>>> takes up to a few seconds you surely do not want it to be executed
>>>>>> several times.
>>>>>> My first example is I create a report with one datasource (a mysql
>>>>>> server in my case), one dataset (Say "SELECT 1 AS column1"), and
>>>>>> one data item that displays the value of column1. After running the
>>>>>> report I can see in the mysql query log that the query "SELECT 1 AS
>>>>>> column1" was executed twice.
>>>>>> Now even worse: I change my query to say "SELECT 1 AS column1, 2
>>>>>> AS column2, 3 As column3" and add 2 more data times to the report
>>>>>> so as to display column2 and column3. On generation of the report,
>>>>>> the query will actually be executed 6 times!!!
>>>>>> I have given you a basic example here, but I have reports which
>>>>>> execute quite complicated queries and display the results at
>>>>>> different places in the report. I can see that the queries are
>>>>>> executed several times on my db server and that turns me mad!!
>>>>>> Is this an intended behaviour of BIRT? If yes is there a way to
>>>>>> avoid the queries from being executed several times. If not is
>>>>>> there a plan to fix the issue?
>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>> I am using BIRT 2.2.2 connecting to a mysql database server 5.0.37
>>>>>> and using mysql jdbc connector 3.0
>>>>>> Ben
>>>>>>
>>>>
>>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
------------1LBzGYjMGE1wAlgA4HbbU0
Content-Disposition: attachment; filename=new_report.rptdesign
Content-Type: application/octet-stream; name=new_report.rptdesign
Content-Transfer-Encoding: Base64

PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPHJlcG9y dCB4
bWxucz0iaHR0cDovL3d3dy5lY2xpcHNlLm9yZy9iaXJ0LzIwMDUvZGVzaWdu IiB2
ZXJzaW9uPSIzLjIuMTYiIGlkPSIxIj4KICAgIDxwcm9wZXJ0eSBuYW1lPSJj cmVh
dGVkQnkiPkVjbGlwc2UgQklSVCBEZXNpZ25lciBWZXJzaW9uIDIuMy4wLnYy MDA4
MDUxNSBCdWlsZCAmbHQ7Mi4zLjAudjIwMDgwNTIxLTA2MzA+PC9wcm9wZXJ0 eT4K
ICAgIDxwcm9wZXJ0eSBuYW1lPSJ1bml0cyI+aW48L3Byb3BlcnR5PgogICAg PHBy
b3BlcnR5IG5hbWU9Imljb25GaWxlIj4vdGVtcGxhdGVzL2JsYW5rX3JlcG9y dC5n
aWY8L3Byb3BlcnR5PgogICAgPGRhdGEtc291cmNlcz4KICAgICAgICA8b2Rh LWRh
dGEtc291cmNlIGV4dGVuc2lvbklEPSJvcmcuZWNsaXBzZS5iaXJ0LnJlcG9y dC5k
YXRhLm9kYS5qZGJjIiBuYW1lPSJEYXRhIFNvdXJjZSIgaWQ9IjYiPgogICAg ICAg
ICAgICA8cHJvcGVydHkgbmFtZT0ib2RhRHJpdmVyQ2xhc3MiPmNvbS5teXNx bC5q
ZGJjLkRyaXZlcjwvcHJvcGVydHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBu YW1l
PSJvZGFVUkwiPmpkYmM6bXlzcWw6Ly9sb2NhbGhvc3Q6MzMwNi90ZXN0X2Ri PC9w
cm9wZXJ0eT4KICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9Im9kYVVzZXIi PnJv
b3Q8L3Byb3BlcnR5PgogICAgICAgIDwvb2RhLWRhdGEtc291cmNlPgogICAg PC9k
YXRhLXNvdXJjZXM+CiAgICA8ZGF0YS1zZXRzPgogICAgICAgIDxvZGEtZGF0 YS1z
ZXQgZXh0ZW5zaW9uSUQ9Im9yZy5lY2xpcHNlLmJpcnQucmVwb3J0LmRhdGEu b2Rh
LmpkYmMuSmRiY1NlbGVjdERhdGFTZXQiIG5hbWU9IkRhdGEgU2V0IiBpZD0i NyI+
CiAgICAgICAgICAgIDxsaXN0LXByb3BlcnR5IG5hbWU9ImNvbHVtbkhpbnRz Ij4K
ICAgICAgICAgICAgICAgIDxzdHJ1Y3R1cmU+CiAgICAgICAgICAgICAgICAg ICAg
PHByb3BlcnR5IG5hbWU9ImNvbHVtbk5hbWUiPmNvbDE8L3Byb3BlcnR5Pgog ICAg
ICAgICAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJkaXNwbGF5TmFtZSI+ Y29s
MTwvcHJvcGVydHk+CiAgICAgICAgICAgICAgICA8L3N0cnVjdHVyZT4KICAg ICAg
ICAgICAgPC9saXN0LXByb3BlcnR5PgogICAgICAgICAgICA8c3RydWN0dXJl IG5h
bWU9ImNhY2hlZE1ldGFEYXRhIj4KICAgICAgICAgICAgICAgIDxsaXN0LXBy b3Bl
cnR5IG5hbWU9InJlc3VsdFNldCI+CiAgICAgICAgICAgICAgICAgICAgPHN0 cnVj
dHVyZT4KICAgICAgICAgICAgICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9 InBv
c2l0aW9uIj4xPC9wcm9wZXJ0eT4KICAgICAgICAgICAgICAgICAgICAgICAg PHBy
b3BlcnR5IG5hbWU9Im5hbWUiPmNvbDE8L3Byb3BlcnR5PgogICAgICAgICAg ICAg
ICAgICAgICAgICA8cHJvcGVydHkgbmFtZT0iZGF0YVR5cGUiPmRlY2ltYWw8 L3By
b3BlcnR5PgogICAgICAgICAgICAgICAgICAgIDwvc3RydWN0dXJlPgogICAg ICAg
ICAgICAgICAgPC9saXN0LXByb3BlcnR5PgogICAgICAgICAgICA8L3N0cnVj dHVy
ZT4KICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9ImRhdGFTb3VyY2UiPkRh dGEg
U291cmNlPC9wcm9wZXJ0eT4KICAgICAgICAgICAgPGxpc3QtcHJvcGVydHkg bmFt
ZT0icmVzdWx0U2V0Ij4KICAgICAgICAgICAgICAgIDxzdHJ1Y3R1cmU+CiAg ICAg
ICAgICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9InBvc2l0aW9uIj4xPC9w cm9w
ZXJ0eT4KICAgICAgICAgICAgICAgICAgICA8cHJvcGVydHkgbmFtZT0ibmFt ZSI+
Y29sMTwvcHJvcGVydHk+CiAgICAgICAgICAgICAgICAgICAgPHByb3BlcnR5 IG5h
bWU9Im5hdGl2ZU5hbWUiPmNvbDE8L3Byb3BlcnR5PgogICAgICAgICAgICAg ICAg
ICAgIDxwcm9wZXJ0eSBuYW1lPSJkYXRhVHlwZSI+ZGVjaW1hbDwvcHJvcGVy dHk+
CiAgICAgICAgICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9Im5hdGl2ZURh dGFU
eXBlIj4tNTwvcHJvcGVydHk+CiAgICAgICAgICAgICAgICA8L3N0cnVjdHVy ZT4K
ICAgICAgICAgICAgPC9saXN0LXByb3BlcnR5PgogICAgICAgICAgICA8cHJv cGVy
dHkgbmFtZT0icXVlcnlUZXh0Ij5zZWxlY3QgMSBBUyBjb2wxPC9wcm9wZXJ0 eT4K
ICAgICAgICAgICAgPHhtbC1wcm9wZXJ0eSBuYW1lPSJkZXNpZ25lclZhbHVl cyI+
PCFbQ0RBVEFbPD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgi Pz4K
PG1vZGVsOkRlc2lnblZhbHVlcyB4bWxuczpkZXNpZ249Imh0dHA6Ly93d3cu ZWNs
aXBzZS5vcmcvZGF0YXRvb2xzL2Nvbm5lY3Rpdml0eS9vZGEvZGVzaWduIiB4 bWxu
czptb2RlbD0iaHR0cDovL3d3dy5lY2xpcHNlLm9yZy9iaXJ0L3JlcG9ydC9t b2Rl
bC9hZGFwdGVyL29kYU1vZGVsIj4KICA8VmVyc2lvbj4xLjA8L1ZlcnNpb24+ CiAg
PGRlc2lnbjpSZXN1bHRTZXRzIGRlcml2ZWRNZXRhRGF0YT0idHJ1ZSI+CiAg ICA8
ZGVzaWduOnJlc3VsdFNldERlZmluaXRpb25zPgogICAgICA8ZGVzaWduOnJl c3Vs
dFNldENvbHVtbnM+CiAgICAgICAgPGRlc2lnbjpyZXN1bHRDb2x1bW5EZWZp bml0
aW9ucz4KICAgICAgICAgIDxkZXNpZ246YXR0cmlidXRlcz4KICAgICAgICAg ICAg
PGRlc2lnbjpuYW1lPmNvbDE8L2Rlc2lnbjpuYW1lPgogICAgICAgICAgICA8 ZGVz
aWduOnBvc2l0aW9uPjE8L2Rlc2lnbjpwb3NpdGlvbj4KICAgICAgICAgICAg PGRl
c2lnbjpuYXRpdmVEYXRhVHlwZUNvZGU+LTU8L2Rlc2lnbjpuYXRpdmVEYXRh VHlw
ZUNvZGU+CiAgICAgICAgICAgIDxkZXNpZ246cHJlY2lzaW9uPjE8L2Rlc2ln bjpw
cmVjaXNpb24+CiAgICAgICAgICAgIDxkZXNpZ246c2NhbGU+MDwvZGVzaWdu OnNj
YWxlPgogICAgICAgICAgICA8ZGVzaWduOm51bGxhYmlsaXR5Pk5vdE51bGxh Ymxl
PC9kZXNpZ246bnVsbGFiaWxpdHk+CiAgICAgICAgICAgIDxkZXNpZ246dWlI aW50
cz4KICAgICAgICAgICAgICA8ZGVzaWduOmRpc3BsYXlOYW1lPmNvbDE8L2Rl c2ln
bjpkaXNwbGF5TmFtZT4KICAgICAgICAgICAgPC9kZXNpZ246dWlIaW50cz4K ICAg
ICAgICAgIDwvZGVzaWduOmF0dHJpYnV0ZXM+CiAgICAgICAgICA8ZGVzaWdu OnVz
YWdlSGludHM+CiAgICAgICAgICAgIDxkZXNpZ246bGFiZWw+Y29sMTwvZGVz aWdu
OmxhYmVsPgogICAgICAgICAgICA8ZGVzaWduOmZvcm1hdHRpbmdIaW50cz4K ICAg
ICAgICAgICAgICA8ZGVzaWduOmRpc3BsYXlTaXplPjE8L2Rlc2lnbjpkaXNw bGF5
U2l6ZT4KICAgICAgICAgICAgPC9kZXNpZ246Zm9ybWF0dGluZ0hpbnRzPgog ICAg
ICAgICAgPC9kZXNpZ246dXNhZ2VIaW50cz4KICAgICAgICA8L2Rlc2lnbjpy ZXN1
bHRDb2x1bW5EZWZpbml0aW9ucz4KICAgICAgPC9kZXNpZ246cmVzdWx0U2V0 Q29s
dW1ucz4KICAgIDwvZGVzaWduOnJlc3VsdFNldERlZmluaXRpb25zPgogIDwv ZGVz
aWduOlJlc3VsdFNldHM+CjwvbW9kZWw6RGVzaWduVmFsdWVzPl1dPjwveG1s LXBy
b3BlcnR5PgogICAgICAgIDwvb2RhLWRhdGEtc2V0PgogICAgPC9kYXRhLXNl dHM+
CiAgICA8c3R5bGVzPgogICAgICAgIDxzdHlsZSBuYW1lPSJjcm9zc3RhYi1j ZWxs
IiBpZD0iNCI+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJib3JkZXJC b3R0
b21Db2xvciI+I0NDQ0NDQzwvcHJvcGVydHk+CiAgICAgICAgICAgIDxwcm9w ZXJ0
eSBuYW1lPSJib3JkZXJCb3R0b21TdHlsZSI+c29saWQ8L3Byb3BlcnR5Pgog ICAg
ICAgICAgICA8cHJvcGVydHkgbmFtZT0iYm9yZGVyQm90dG9tV2lkdGgiPjFw dDwv
cHJvcGVydHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJib3JkZXJM ZWZ0
Q29sb3IiPiNDQ0NDQ0M8L3Byb3BlcnR5PgogICAgICAgICAgICA8cHJvcGVy dHkg
bmFtZT0iYm9yZGVyTGVmdFN0eWxlIj5zb2xpZDwvcHJvcGVydHk+CiAgICAg ICAg
ICAgIDxwcm9wZXJ0eSBuYW1lPSJib3JkZXJMZWZ0V2lkdGgiPjFwdDwvcHJv cGVy
dHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJib3JkZXJSaWdodENv bG9y
Ij4jQ0NDQ0NDPC9wcm9wZXJ0eT4KICAgICAgICAgICAgPHByb3BlcnR5IG5h bWU9
ImJvcmRlclJpZ2h0U3R5bGUiPnNvbGlkPC9wcm9wZXJ0eT4KICAgICAgICAg ICAg
PHByb3BlcnR5IG5hbWU9ImJvcmRlclJpZ2h0V2lkdGgiPjFwdDwvcHJvcGVy dHk+
CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJib3JkZXJUb3BDb2xvciI+ I0ND
Q0NDQzwvcHJvcGVydHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJi b3Jk
ZXJUb3BTdHlsZSI+c29saWQ8L3Byb3BlcnR5PgogICAgICAgICAgICA8cHJv cGVy
dHkgbmFtZT0iYm9yZGVyVG9wV2lkdGgiPjFwdDwvcHJvcGVydHk+CiAgICAg ICAg
PC9zdHlsZT4KICAgICAgICA8c3R5bGUgbmFtZT0iY3Jvc3N0YWIiIGlkPSI1 Ij4K
ICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9ImJvcmRlckJvdHRvbUNvbG9y Ij4j
Q0NDQ0NDPC9wcm9wZXJ0eT4KICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9 ImJv
cmRlckJvdHRvbVN0eWxlIj5zb2xpZDwvcHJvcGVydHk+CiAgICAgICAgICAg IDxw
cm9wZXJ0eSBuYW1lPSJib3JkZXJCb3R0b21XaWR0aCI+MXB0PC9wcm9wZXJ0 eT4K
ICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9ImJvcmRlckxlZnRDb2xvciI+ I0ND
Q0NDQzwvcHJvcGVydHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJi b3Jk
ZXJMZWZ0U3R5bGUiPnNvbGlkPC9wcm9wZXJ0eT4KICAgICAgICAgICAgPHBy b3Bl
cnR5IG5hbWU9ImJvcmRlckxlZnRXaWR0aCI+MXB0PC9wcm9wZXJ0eT4KICAg ICAg
ICAgICAgPHByb3BlcnR5IG5hbWU9ImJvcmRlclJpZ2h0Q29sb3IiPiNDQ0ND Q0M8
L3Byb3BlcnR5PgogICAgICAgICAgICA8cHJvcGVydHkgbmFtZT0iYm9yZGVy Umln
aHRTdHlsZSI+c29saWQ8L3Byb3BlcnR5PgogICAgICAgICAgICA8cHJvcGVy dHkg
bmFtZT0iYm9yZGVyUmlnaHRXaWR0aCI+MXB0PC9wcm9wZXJ0eT4KICAgICAg ICAg
ICAgPHByb3BlcnR5IG5hbWU9ImJvcmRlclRvcENvbG9yIj4jQ0NDQ0NDPC9w cm9w
ZXJ0eT4KICAgICAgICAgICAgPHByb3BlcnR5IG5hbWU9ImJvcmRlclRvcFN0 eWxl
Ij5zb2xpZDwvcHJvcGVydHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1l PSJi
b3JkZXJUb3BXaWR0aCI+MXB0PC9wcm9wZXJ0eT4KICAgICAgICA8L3N0eWxl Pgog
ICAgPC9zdHlsZXM+CiAgICA8cGFnZS1zZXR1cD4KICAgICAgICA8c2ltcGxl LW1h
c3Rlci1wYWdlIG5hbWU9IlNpbXBsZSBNYXN0ZXJQYWdlIiBpZD0iMiI+CiAg ICAg
ICAgICAgIDxwYWdlLWZvb3Rlcj4KICAgICAgICAgICAgICAgIDx0ZXh0IGlk PSIz
Ij4KICAgICAgICAgICAgICAgICAgICA8cHJvcGVydHkgbmFtZT0iY29udGVu dFR5
cGUiPmh0bWw8L3Byb3BlcnR5PgogICAgICAgICAgICAgICAgICAgIDx0ZXh0 LXBy
b3BlcnR5IG5hbWU9ImNvbnRlbnQiPjwhW0NEQVRBWzx2YWx1ZS1vZj5uZXcg RGF0
ZSgpPC92YWx1ZS1vZj5dXT48L3RleHQtcHJvcGVydHk+CiAgICAgICAgICAg ICAg
ICA8L3RleHQ+CiAgICAgICAgICAgIDwvcGFnZS1mb290ZXI+CiAgICAgICAg PC9z
aW1wbGUtbWFzdGVyLXBhZ2U+CiAgICA8L3BhZ2Utc2V0dXA+CiAgICA8Ym9k eT4K
ICAgICAgICA8ZGF0YSBpZD0iOCI+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBu YW1l
PSJkYXRhU2V0Ij5EYXRhIFNldDwvcHJvcGVydHk+CiAgICAgICAgICAgIDxs aXN0
LXByb3BlcnR5IG5hbWU9ImJvdW5kRGF0YUNvbHVtbnMiPgogICAgICAgICAg ICAg
ICAgPHN0cnVjdHVyZT4KICAgICAgICAgICAgICAgICAgICA8cHJvcGVydHkg bmFt
ZT0ibmFtZSI+Y29sMTwvcHJvcGVydHk+CiAgICAgICAgICAgICAgICAgICAg PHBy
b3BlcnR5IG5hbWU9ImRpc3BsYXlOYW1lIj5jb2wxPC9wcm9wZXJ0eT4KICAg ICAg
ICAgICAgICAgICAgICA8ZXhwcmVzc2lvbiBuYW1lPSJleHByZXNzaW9uIj5k YXRh
U2V0Um93WyJjb2wxIl08L2V4cHJlc3Npb24+CiAgICAgICAgICAgICAgICAg ICAg
PHByb3BlcnR5IG5hbWU9ImRhdGFUeXBlIj5kZWNpbWFsPC9wcm9wZXJ0eT4K ICAg
ICAgICAgICAgICAgIDwvc3RydWN0dXJlPgogICAgICAgICAgICA8L2xpc3Qt cHJv
cGVydHk+CiAgICAgICAgICAgIDxwcm9wZXJ0eSBuYW1lPSJyZXN1bHRTZXRD b2x1
bW4iPmNvbDE8L3Byb3BlcnR5PgogICAgICAgIDwvZGF0YT4KICAgIDwvYm9k eT4K
PC9yZXBvcnQ+Cg==

------------1LBzGYjMGE1wAlgA4HbbU0--
Re: Performance issues with db query being executed several times [message #362849 is a reply to message #362826] Tue, 27 May 2008 22:12 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jasonweathersby.alltel.net

Serbenet,

Can you modify your report to add an onFetch script to your data set.
Enter something like:

importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/test/dsaccess.txt", true ) );
out.println( "onfetch");
out.close();

I did this and only get the one call. I do not have the mysql admin
console.


Jason

serbenet wrote:
> No I don't have any look ahead aggregates and I don't use grouping.
>
> I have attached a simple report showing the issue on my system.
> When looking at the logs of the mysql server I can see the query "select
> 1 AS col1" being executed twice.
>
> Here is a description of my system:
> - Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT 2.2.2)
> running on windows XP pro sp2 with jdk1.6.0_03
> - The databse server is a mysql server version 5.0.37 running on redhat
> enterprise 4.
> - The JDBC connector used is the official JDBC driver for MySQL, MySQL
> Connector/J 5.1.6.
>
> Ben
>
>
> On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby
> <jasonweathersby@alltel.net> wrote:
>
>> Ben,
>>
>> On my system it is executed twice. Do yo have any look ahead
>> aggregates in the report? Are you grouping?
>>
>> Jason
>>
>> serbenet wrote:
>>> Thanks.
>>> After having given a name to my data items, I could see them in the
>>> list of previous data items.
>>> I have now binded only one data item to my dataset and all other data
>>> items are binded to this previous data item.
>>> This definitely seems to improve things and the query is now executed
>>> only twice (instead of 16 times before!!).
>>> Also I do not understand why it is still executed twice when once
>>> should be enough, but that's definitely already a big improvement.
>>> I have also tested the behaviour with 2.3 RC1 and data set caching
>>> seems to have been improved a lot.
>>> In any cases, a query is never executed more than twice, even if the
>>> report contains several data items directly bound to the dataset.
>>> But also and again the query is executed twice when once should be
>>> enough. Any thoughts on this? Is it really necessary to execute the
>>> query twice?
>>> Thanks again for your help.
>>> Ben
>>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby
>>> <jasonweathersby@alltel.net> wrote:
>>>
>>>> Caching of datasets is being improved, but event now yours should
>>>> not have a problem. The first time you use a data set on a report
>>>> element, name it. This is in the general properties for the item.
>>>> Then anytime you want to use the query again just select the binding
>>>> tab of your new element, select the radial for report items and your
>>>> named items should be in the list. The reason you are seeing only
>>>> charts, it because those are probably the only items named in your
>>>> report.
>>>>
>>>> Jason
>>>>
>>>>
>>>> serbenet wrote:
>>>>> Thanks for the quick reply.
>>>>> Each data item in my reports are bound to the dataset so,
>>>>> following your explanation,
>>>>> I understand now why the queries get executed several times.
>>>>> Is this a normal behaviour or is this something that is going to be
>>>>> changed in a later version?
>>>>> Say if 2 data items use the same dataset then surely the data do
>>>>> not need to be fetched twice from the database.
>>>>> Now in some places I can put all the data items in one big table,
>>>>> and only bind this table to the dataset. This solves the issue. But
>>>>> depending on the layout of my report, I can not always put all data
>>>>> items in one big table. I have tried binding to a previous report
>>>>> item as you mentionned, but it seems that I can only bind to
>>>>> previous chart items, I do not have the choice to select a previous
>>>>> data item. Is this a limitation of 2.2.2?
>>>>> Thanks again
>>>>> Ben
>>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>
>>>>>> Serbenet,
>>>>>>
>>>>>> Verify that each individual data item is not bound to a dataset.
>>>>>> They should be in a table that is bound to the dataset. If they
>>>>>> are bound to the dataset, each will execute the query. In 2.2.2
>>>>>> you should also be able to bind to a previous report item. For
>>>>>> example create on dataset and bind it to a table1, name it in the
>>>>>> general properties tab. Add a second table, select the binding
>>>>>> tab and bind it to a report item. The first table should be in
>>>>>> the selection.
>>>>>>
>>>>>> Jason
>>>>>>
>>>>>>
>>>>>> serbenet wrote:
>>>>>>> Hello all,
>>>>>>> I have noticed that when generating a report with BIRT, a same
>>>>>>> SQL query may actually be executed several times.
>>>>>>> This behaviour is probably ok for small queries but when a query
>>>>>>> takes up to a few seconds you surely do not want it to be
>>>>>>> executed several times.
>>>>>>> My first example is I create a report with one datasource (a
>>>>>>> mysql server in my case), one dataset (Say "SELECT 1 AS
>>>>>>> column1"), and one data item that displays the value of column1.
>>>>>>> After running the report I can see in the mysql query log that
>>>>>>> the query "SELECT 1 AS column1" was executed twice.
>>>>>>> Now even worse: I change my query to say "SELECT 1 AS column1, 2
>>>>>>> AS column2, 3 As column3" and add 2 more data times to the report
>>>>>>> so as to display column2 and column3. On generation of the
>>>>>>> report, the query will actually be executed 6 times!!!
>>>>>>> I have given you a basic example here, but I have reports which
>>>>>>> execute quite complicated queries and display the results at
>>>>>>> different places in the report. I can see that the queries are
>>>>>>> executed several times on my db server and that turns me mad!!
>>>>>>> Is this an intended behaviour of BIRT? If yes is there a way to
>>>>>>> avoid the queries from being executed several times. If not is
>>>>>>> there a plan to fix the issue?
>>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>>> I am using BIRT 2.2.2 connecting to a mysql database server
>>>>>>> 5.0.37 and using mysql jdbc connector 3.0
>>>>>>> Ben
>>>>>>>
>>>>>
>>>
>
>
>
Re: Performance issues with db query being executed several times [message #362851 is a reply to message #362849] Wed, 28 May 2008 09:04 Go to previous messageGo to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
The onfetch method is called only once on my system too.
However I still see in the mysql logs that the query is executed twice.

You do not need the mysql admin console to check the mysql logs.
All you need to do is to start your mysql server with the --log option.
Each SQL statement received from clients will then be logged to a file =

called
host_name.log in the data directory.

Here is more information:
http://dev.mysql.com/doc/refman/5.0/en/query-log.html

Ben

On Tue, 27 May 2008 23:12:13 +0100, Jason Weathersby =

<jasonweathersby@alltel.net> wrote:

> Serbenet,
>
> Can you modify your report to add an onFetch script to your data set.
> Enter something like:
>
> importPackage( Packages.java.io );
> out =3D new PrintWriter( new FileWriter( "c:/test/dsaccess.txt", true =
) );
> out.println( "onfetch");
> out.close();
>
> I did this and only get the one call. I do not have the mysql admin =

> console.
>
>
> Jason
>
> serbenet wrote:
>> No I don't have any look ahead aggregates and I don't use grouping.
>> I have attached a simple report showing the issue on my system.
>> When looking at the logs of the mysql server I can see the query =

>> "select 1 AS col1" being executed twice.
>> Here is a description of my system:
>> - Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT 2.2.=
2) =

>> running on windows XP pro sp2 with jdk1.6.0_03
>> - The databse server is a mysql server version 5.0.37 running on redh=
at =

>> enterprise 4.
>> - The JDBC connector used is the official JDBC driver for MySQL, MySQ=
L =

>> Connector/J 5.1.6.
>> Ben
>> On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby =

>> <jasonweathersby@alltel.net> wrote:
>>
>>> Ben,
>>>
>>> On my system it is executed twice. Do yo have any look ahead =

>>> aggregates in the report? Are you grouping?
>>>
>>> Jason
>>>
>>> serbenet wrote:
>>>> Thanks.
>>>> After having given a name to my data items, I could see them in th=
e =

>>>> list of previous data items.
>>>> I have now binded only one data item to my dataset and all other da=
ta =

>>>> items are binded to this previous data item.
>>>> This definitely seems to improve things and the query is now execut=
ed =

>>>> only twice (instead of 16 times before!!).
>>>> Also I do not understand why it is still executed twice when once =

>>>> should be enough, but that's definitely already a big improvement.
>>>> I have also tested the behaviour with 2.3 RC1 and data set caching=
=

>>>> seems to have been improved a lot.
>>>> In any cases, a query is never executed more than twice, even if th=
e =

>>>> report contains several data items directly bound to the dataset.
>>>> But also and again the query is executed twice when once should be =
=

>>>> enough. Any thoughts on this? Is it really necessary to execute the=
=

>>>> query twice?
>>>> Thanks again for your help.
>>>> Ben
>>>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby =

>>>> <jasonweathersby@alltel.net> wrote:
>>>>
>>>>> Caching of datasets is being improved, but event now yours should =
=

>>>>> not have a problem. The first time you use a data set on a report=
=

>>>>> element, name it. This is in the general properties for the item.=
=

>>>>> Then anytime you want to use the query again just select the bindi=
ng =

>>>>> tab of your new element, select the radial for report items and yo=
ur =

>>>>> named items should be in the list. The reason you are seeing only=
=

>>>>> charts, it because those are probably the only items named in your=
=

>>>>> report.
>>>>>
>>>>> Jason
>>>>>
>>>>>
>>>>> serbenet wrote:
>>>>>> Thanks for the quick reply.
>>>>>> Each data item in my reports are bound to the dataset so, =

>>>>>> following your explanation,
>>>>>> I understand now why the queries get executed several times.
>>>>>> Is this a normal behaviour or is this something that is going to =
be =

>>>>>> changed in a later version?
>>>>>> Say if 2 data items use the same dataset then surely the data do =
=

>>>>>> not need to be fetched twice from the database.
>>>>>> Now in some places I can put all the data items in one big table=
, =

>>>>>> and only bind this table to the dataset. This solves the issue. B=
ut =

>>>>>> depending on the layout of my report, I can not always put all da=
ta =

>>>>>> items in one big table. I have tried binding to a previous report=
=

>>>>>> item as you mentionned, but it seems that I can only bind to =

>>>>>> previous chart items, I do not have the choice to select a previo=
us =

>>>>>> data item. Is this a limitation of 2.2.2?
>>>>>> Thanks again
>>>>>> Ben
>>>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby =

>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>
>>>>>>> Serbenet,
>>>>>>>
>>>>>>> Verify that each individual data item is not bound to a dataset.=
=

>>>>>>> They should be in a table that is bound to the dataset. If they=
=

>>>>>>> are bound to the dataset, each will execute the query. In 2.2.2=
=

>>>>>>> you should also be able to bind to a previous report item. For =
=

>>>>>>> example create on dataset and bind it to a table1, name it in th=
e =

>>>>>>> general properties tab. Add a second table, select the binding =
=

>>>>>>> tab and bind it to a report item. The first table should be in =
=

>>>>>>> the selection.
>>>>>>>
>>>>>>> Jason
>>>>>>>
>>>>>>>
>>>>>>> serbenet wrote:
>>>>>>>> Hello all,
>>>>>>>> I have noticed that when generating a report with BIRT, a same=
=

>>>>>>>> SQL query may actually be executed several times.
>>>>>>>> This behaviour is probably ok for small queries but when a quer=
y =

>>>>>>>> takes up to a few seconds you surely do not want it to be =

>>>>>>>> executed several times.
>>>>>>>> My first example is I create a report with one datasource (a =

>>>>>>>> mysql server in my case), one dataset (Say "SELECT 1 AS =

>>>>>>>> column1"), and one data item that displays the value of column1=
.. =

>>>>>>>> After running the report I can see in the mysql query log that =
=

>>>>>>>> the query "SELECT 1 AS column1" was executed twice.
>>>>>>>> Now even worse: I change my query to say "SELECT 1 AS column1,=
2 =

>>>>>>>> AS column2, 3 As column3" and add 2 more data times to the repo=
rt =

>>>>>>>> so as to display column2 and column3. On generation of the =

>>>>>>>> report, the query will actually be executed 6 times!!!
>>>>>>>> I have given you a basic example here, but I have reports whic=
h =

>>>>>>>> execute quite complicated queries and display the results at =

>>>>>>>> different places in the report. I can see that the queries are=
=

>>>>>>>> executed several times on my db server and that turns me mad!!
>>>>>>>> Is this an intended behaviour of BIRT? If yes is there a way t=
o =

>>>>>>>> avoid the queries from being executed several times. If not is =
=

>>>>>>>> there a plan to fix the issue?
>>>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>>>> I am using BIRT 2.2.2 connecting to a mysql database server =

>>>>>>>> 5.0.37 and using mysql jdbc connector 3.0
>>>>>>>> Ben
>>>>>>>>
>>>>>>
>>>>
>>



-- =

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Re: Performance issues with db query being executed several times [message #362855 is a reply to message #362851] Wed, 28 May 2008 15:42 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jasonweathersby.alltel.net

Serbenet,

I believe the first call to the data source is used to discover
metadeta. If I am not mistaken it should not be returning all rows.
The second call gets all the data. Hence the onfetch being called only
once.

Jason


serbenet wrote:
> The onfetch method is called only once on my system too.
> However I still see in the mysql logs that the query is executed twice.
>
> You do not need the mysql admin console to check the mysql logs.
> All you need to do is to start your mysql server with the --log option.
> Each SQL statement received from clients will then be logged to a file
> called
> host_name.log in the data directory.
>
> Here is more information:
> http://dev.mysql.com/doc/refman/5.0/en/query-log.html
>
> Ben
>
> On Tue, 27 May 2008 23:12:13 +0100, Jason Weathersby
> <jasonweathersby@alltel.net> wrote:
>
>> Serbenet,
>>
>> Can you modify your report to add an onFetch script to your data set.
>> Enter something like:
>>
>> importPackage( Packages.java.io );
>> out = new PrintWriter( new FileWriter( "c:/test/dsaccess.txt", true ) );
>> out.println( "onfetch");
>> out.close();
>>
>> I did this and only get the one call. I do not have the mysql admin
>> console.
>>
>>
>> Jason
>>
>> serbenet wrote:
>>> No I don't have any look ahead aggregates and I don't use grouping.
>>> I have attached a simple report showing the issue on my system.
>>> When looking at the logs of the mysql server I can see the query
>>> "select 1 AS col1" being executed twice.
>>> Here is a description of my system:
>>> - Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT
>>> 2.2.2) running on windows XP pro sp2 with jdk1.6.0_03
>>> - The databse server is a mysql server version 5.0.37 running on
>>> redhat enterprise 4.
>>> - The JDBC connector used is the official JDBC driver for MySQL,
>>> MySQL Connector/J 5.1.6.
>>> Ben
>>> On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby
>>> <jasonweathersby@alltel.net> wrote:
>>>
>>>> Ben,
>>>>
>>>> On my system it is executed twice. Do yo have any look ahead
>>>> aggregates in the report? Are you grouping?
>>>>
>>>> Jason
>>>>
>>>> serbenet wrote:
>>>>> Thanks.
>>>>> After having given a name to my data items, I could see them in
>>>>> the list of previous data items.
>>>>> I have now binded only one data item to my dataset and all other
>>>>> data items are binded to this previous data item.
>>>>> This definitely seems to improve things and the query is now
>>>>> executed only twice (instead of 16 times before!!).
>>>>> Also I do not understand why it is still executed twice when once
>>>>> should be enough, but that's definitely already a big improvement.
>>>>> I have also tested the behaviour with 2.3 RC1 and data set caching
>>>>> seems to have been improved a lot.
>>>>> In any cases, a query is never executed more than twice, even if
>>>>> the report contains several data items directly bound to the dataset.
>>>>> But also and again the query is executed twice when once should be
>>>>> enough. Any thoughts on this? Is it really necessary to execute the
>>>>> query twice?
>>>>> Thanks again for your help.
>>>>> Ben
>>>>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby
>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>
>>>>>> Caching of datasets is being improved, but event now yours should
>>>>>> not have a problem. The first time you use a data set on a report
>>>>>> element, name it. This is in the general properties for the
>>>>>> item. Then anytime you want to use the query again just select
>>>>>> the binding tab of your new element, select the radial for report
>>>>>> items and your named items should be in the list. The reason you
>>>>>> are seeing only charts, it because those are probably the only
>>>>>> items named in your report.
>>>>>>
>>>>>> Jason
>>>>>>
>>>>>>
>>>>>> serbenet wrote:
>>>>>>> Thanks for the quick reply.
>>>>>>> Each data item in my reports are bound to the dataset so,
>>>>>>> following your explanation,
>>>>>>> I understand now why the queries get executed several times.
>>>>>>> Is this a normal behaviour or is this something that is going to
>>>>>>> be changed in a later version?
>>>>>>> Say if 2 data items use the same dataset then surely the data do
>>>>>>> not need to be fetched twice from the database.
>>>>>>> Now in some places I can put all the data items in one big
>>>>>>> table, and only bind this table to the dataset. This solves the
>>>>>>> issue. But depending on the layout of my report, I can not always
>>>>>>> put all data items in one big table. I have tried binding to a
>>>>>>> previous report item as you mentionned, but it seems that I can
>>>>>>> only bind to previous chart items, I do not have the choice to
>>>>>>> select a previous data item. Is this a limitation of 2.2.2?
>>>>>>> Thanks again
>>>>>>> Ben
>>>>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
>>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>>
>>>>>>>> Serbenet,
>>>>>>>>
>>>>>>>> Verify that each individual data item is not bound to a
>>>>>>>> dataset. They should be in a table that is bound to the
>>>>>>>> dataset. If they are bound to the dataset, each will execute
>>>>>>>> the query. In 2.2.2 you should also be able to bind to a
>>>>>>>> previous report item. For example create on dataset and bind it
>>>>>>>> to a table1, name it in the general properties tab. Add a
>>>>>>>> second table, select the binding tab and bind it to a report
>>>>>>>> item. The first table should be in the selection.
>>>>>>>>
>>>>>>>> Jason
>>>>>>>>
>>>>>>>>
>>>>>>>> serbenet wrote:
>>>>>>>>> Hello all,
>>>>>>>>> I have noticed that when generating a report with BIRT, a same
>>>>>>>>> SQL query may actually be executed several times.
>>>>>>>>> This behaviour is probably ok for small queries but when a
>>>>>>>>> query takes up to a few seconds you surely do not want it to be
>>>>>>>>> executed several times.
>>>>>>>>> My first example is I create a report with one datasource (a
>>>>>>>>> mysql server in my case), one dataset (Say "SELECT 1 AS
>>>>>>>>> column1"), and one data item that displays the value of
>>>>>>>>> column1. After running the report I can see in the mysql query
>>>>>>>>> log that the query "SELECT 1 AS column1" was executed twice.
>>>>>>>>> Now even worse: I change my query to say "SELECT 1 AS column1,
>>>>>>>>> 2 AS column2, 3 As column3" and add 2 more data times to the
>>>>>>>>> report so as to display column2 and column3. On generation of
>>>>>>>>> the report, the query will actually be executed 6 times!!!
>>>>>>>>> I have given you a basic example here, but I have reports
>>>>>>>>> which execute quite complicated queries and display the results
>>>>>>>>> at different places in the report. I can see that the queries
>>>>>>>>> are executed several times on my db server and that turns me mad!!
>>>>>>>>> Is this an intended behaviour of BIRT? If yes is there a way
>>>>>>>>> to avoid the queries from being executed several times. If not
>>>>>>>>> is there a plan to fix the issue?
>>>>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>>>>> I am using BIRT 2.2.2 connecting to a mysql database server
>>>>>>>>> 5.0.37 and using mysql jdbc connector 3.0
>>>>>>>>> Ben
>>>>>>>>>
>>>>>>>
>>>>>
>>>
>
>
>
Re: Performance issues with db query being executed several times [message #362860 is a reply to message #362855] Wed, 28 May 2008 17:05 Go to previous messageGo to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
Jason,

I believe you are right, the first query is probably used to get column =
=

names and
column types and the second query may be used to fetch the data. The =

onfetch function is called
only during the second call.

However the first query takes as much time to process as the second quer=
y, =

which makes my report
generation twice slower compare to what it could be.

For example, here is a dump of the mysql log file after generating a =

report:

8 17:10:58 199 Connect root@localhost on xxx
199 Init DB xxx
199 Query select round('inf'), round('-inf'), =

round('nan')
199 Query SHOW VARIABLES
199 Query SET autocommit=3D1
199 Query SET NAMES latin1
199 Query select avg(t1.ulId) as f1 from table1 as=
t1 =

join (select * from table1 limit 30) as t2
8 17:11:12 199 Query select avg(t1.ulId) as f1 from table1 as =
t1 =

join (select * from table1 limit 30) as t2
8 17:11:27 199 Quit

The query executed is "select avg(t1.ulId) as f1 from table1 as t1 join =
=

(select * from table1 limit 30) as t2".
It is a slow query which on its own takes about 15 seconds to process.
Looking at the log I can see that the query is executed twice,
first time starting at 17:10:58 and finishing at 17:11:12 and the second=

time starting at 17:11:12 and finishing at 17:11:27. So the total time t=
o =

generate
the report was roughfly 30 seconds, when it should have been only 15 =

seconds if the query was
executed only once.

I do not know if it would be possible to do both metadata discovery and =
=

data retrieval
in one query, but I believe there is a big performance improvement that =
=

could be made, and it
is probably worth investigating the matter.

Ben


On Wed, 28 May 2008 16:42:07 +0100, Jason Weathersby =

<jasonweathersby@alltel.net> wrote:

> Serbenet,
>
> I believe the first call to the data source is used to discover =

> metadeta. If I am not mistaken it should not be returning all rows. T=
he =

> second call gets all the data. Hence the onfetch being called only on=
ce.
>
> Jason
>
>
> serbenet wrote:
>> The onfetch method is called only once on my system too.
>> However I still see in the mysql logs that the query is executed twic=
e.
>> You do not need the mysql admin console to check the mysql logs.
>> All you need to do is to start your mysql server with the --log optio=
n.
>> Each SQL statement received from clients will then be logged to a fil=
e =

>> called
>> host_name.log in the data directory.
>> Here is more information:
>> http://dev.mysql.com/doc/refman/5.0/en/query-log.html
>> Ben
>> On Tue, 27 May 2008 23:12:13 +0100, Jason Weathersby =

>> <jasonweathersby@alltel.net> wrote:
>>
>>> Serbenet,
>>>
>>> Can you modify your report to add an onFetch script to your data set=
..
>>> Enter something like:
>>>
>>> importPackage( Packages.java.io );
>>> out =3D new PrintWriter( new FileWriter( "c:/test/dsaccess.txt", tru=
e ) =

>>> );
>>> out.println( "onfetch");
>>> out.close();
>>>
>>> I did this and only get the one call. I do not have the mysql admin=
=

>>> console.
>>>
>>>
>>> Jason
>>>
>>> serbenet wrote:
>>>> No I don't have any look ahead aggregates and I don't use grouping.=

>>>> I have attached a simple report showing the issue on my system.
>>>> When looking at the logs of the mysql server I can see the query =

>>>> "select 1 AS col1" being executed twice.
>>>> Here is a description of my system:
>>>> - Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT =

>>>> 2.2.2) running on windows XP pro sp2 with jdk1.6.0_03
>>>> - The databse server is a mysql server version 5.0.37 running on =

>>>> redhat enterprise 4.
>>>> - The JDBC connector used is the official JDBC driver for MySQL, =

>>>> MySQL Connector/J 5.1.6.
>>>> Ben
>>>> On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby =

>>>> <jasonweathersby@alltel.net> wrote:
>>>>
>>>>> Ben,
>>>>>
>>>>> On my system it is executed twice. Do yo have any look ahead =

>>>>> aggregates in the report? Are you grouping?
>>>>>
>>>>> Jason
>>>>>
>>>>> serbenet wrote:
>>>>>> Thanks.
>>>>>> After having given a name to my data items, I could see them in =
=

>>>>>> the list of previous data items.
>>>>>> I have now binded only one data item to my dataset and all other =
=

>>>>>> data items are binded to this previous data item.
>>>>>> This definitely seems to improve things and the query is now =

>>>>>> executed only twice (instead of 16 times before!!).
>>>>>> Also I do not understand why it is still executed twice when once=
=

>>>>>> should be enough, but that's definitely already a big improvement=
..
>>>>>> I have also tested the behaviour with 2.3 RC1 and data set cachi=
ng =

>>>>>> seems to have been improved a lot.
>>>>>> In any cases, a query is never executed more than twice, even if =
=

>>>>>> the report contains several data items directly bound to the =

>>>>>> dataset.
>>>>>> But also and again the query is executed twice when once should b=
e =

>>>>>> enough. Any thoughts on this? Is it really necessary to execute t=
he =

>>>>>> query twice?
>>>>>> Thanks again for your help.
>>>>>> Ben
>>>>>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby =

>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>
>>>>>>> Caching of datasets is being improved, but event now yours shoul=
d =

>>>>>>> not have a problem. The first time you use a data set on a repo=
rt =

>>>>>>> element, name it. This is in the general properties for the =

>>>>>>> item. Then anytime you want to use the query again just select =
=

>>>>>>> the binding tab of your new element, select the radial for repor=
t =

>>>>>>> items and your named items should be in the list. The reason yo=
u =

>>>>>>> are seeing only charts, it because those are probably the only =

>>>>>>> items named in your report.
>>>>>>>
>>>>>>> Jason
>>>>>>>
>>>>>>>
>>>>>>> serbenet wrote:
>>>>>>>> Thanks for the quick reply.
>>>>>>>> Each data item in my reports are bound to the dataset so, =

>>>>>>>> following your explanation,
>>>>>>>> I understand now why the queries get executed several times.
>>>>>>>> Is this a normal behaviour or is this something that is going t=
o =

>>>>>>>> be changed in a later version?
>>>>>>>> Say if 2 data items use the same dataset then surely the data d=
o =

>>>>>>>> not need to be fetched twice from the database.
>>>>>>>> Now in some places I can put all the data items in one big =

>>>>>>>> table, and only bind this table to the dataset. This solves the=
=

>>>>>>>> issue. But depending on the layout of my report, I can not alwa=
ys =

>>>>>>>> put all data items in one big table. I have tried binding to a =
=

>>>>>>>> previous report item as you mentionned, but it seems that I can=
=

>>>>>>>> only bind to previous chart items, I do not have the choice to =
=

>>>>>>>> select a previous data item. Is this a limitation of 2.2.2?
>>>>>>>> Thanks again
>>>>>>>> Ben
>>>>>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby =

>>>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>>>
>>>>>>>>> Serbenet,
>>>>>>>>>
>>>>>>>>> Verify that each individual data item is not bound to a =

>>>>>>>>> dataset. They should be in a table that is bound to the =

>>>>>>>>> dataset. If they are bound to the dataset, each will execute =
=

>>>>>>>>> the query. In 2.2.2 you should also be able to bind to a =

>>>>>>>>> previous report item. For example create on dataset and bind =
it =

>>>>>>>>> to a table1, name it in the general properties tab. Add a =

>>>>>>>>> second table, select the binding tab and bind it to a report =

>>>>>>>>> item. The first table should be in the selection.
>>>>>>>>>
>>>>>>>>> Jason
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> serbenet wrote:
>>>>>>>>>> Hello all,
>>>>>>>>>> I have noticed that when generating a report with BIRT, a sa=
me =

>>>>>>>>>> SQL query may actually be executed several times.
>>>>>>>>>> This behaviour is probably ok for small queries but when a =

>>>>>>>>>> query takes up to a few seconds you surely do not want it to =
be =

>>>>>>>>>> executed several times.
>>>>>>>>>> My first example is I create a report with one datasource (a=
=

>>>>>>>>>> mysql server in my case), one dataset (Say "SELECT 1 AS =

>>>>>>>>>> column1"), and one data item that displays the value of =

>>>>>>>>>> column1. After running the report I can see in the mysql quer=
y =

>>>>>>>>>> log that the query "SELECT 1 AS column1" was executed twice.
>>>>>>>>>> Now even worse: I change my query to say "SELECT 1 AS column=
1, =

>>>>>>>>>> 2 AS column2, 3 As column3" and add 2 more data times to the =
=

>>>>>>>>>> report so as to display column2 and column3. On generation of=
=

>>>>>>>>>> the report, the query will actually be executed 6 times!!!
>>>>>>>>>> I have given you a basic example here, but I have reports =

>>>>>>>>>> which execute quite complicated queries and display the resul=
ts =

>>>>>>>>>> at different places in the report. I can see that the querie=
s =

>>>>>>>>>> are executed several times on my db server and that turns me =
=

>>>>>>>>>> mad!!
>>>>>>>>>> Is this an intended behaviour of BIRT? If yes is there a way=
=

>>>>>>>>>> to avoid the queries from being executed several times. If no=
t =

>>>>>>>>>> is there a plan to fix the issue?
>>>>>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>>>>>> I am using BIRT 2.2.2 connecting to a mysql database server =
=

>>>>>>>>>> 5.0.37 and using mysql jdbc connector 3.0
>>>>>>>>>> Ben
>>>>>>>>>>
>>>>>>>>
>>>>>>
>>>>
>>



-- =

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Re: Performance issues with db query being executed several times [message #362862 is a reply to message #362860] Wed, 28 May 2008 18:36 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jasonweathersby.alltel.net

Ben,

Can you log a bugzilla entry to track this?

Jason

serbenet wrote:
> Jason,
>
> I believe you are right, the first query is probably used to get column
> names and
> column types and the second query may be used to fetch the data. The
> onfetch function is called
> only during the second call.
>
> However the first query takes as much time to process as the second
> query, which makes my report
> generation twice slower compare to what it could be.
>
> For example, here is a dump of the mysql log file after generating a
> report:
>
> 8 17:10:58 199 Connect root@localhost on xxx
> 199 Init DB xxx
> 199 Query select round('inf'), round('-inf'),
> round('nan')
> 199 Query SHOW VARIABLES
> 199 Query SET autocommit=1
> 199 Query SET NAMES latin1
> 199 Query select avg(t1.ulId) as f1 from table1 as
> t1 join (select * from table1 limit 30) as t2
> 8 17:11:12 199 Query select avg(t1.ulId) as f1 from table1 as
> t1 join (select * from table1 limit 30) as t2
> 8 17:11:27 199 Quit
>
> The query executed is "select avg(t1.ulId) as f1 from table1 as t1 join
> (select * from table1 limit 30) as t2".
> It is a slow query which on its own takes about 15 seconds to process.
> Looking at the log I can see that the query is executed twice,
> first time starting at 17:10:58 and finishing at 17:11:12 and the second
> time starting at 17:11:12 and finishing at 17:11:27. So the total time
> to generate
> the report was roughfly 30 seconds, when it should have been only 15
> seconds if the query was
> executed only once.
>
> I do not know if it would be possible to do both metadata discovery and
> data retrieval
> in one query, but I believe there is a big performance improvement that
> could be made, and it
> is probably worth investigating the matter.
>
> Ben
>
>
> On Wed, 28 May 2008 16:42:07 +0100, Jason Weathersby
> <jasonweathersby@alltel.net> wrote:
>
>> Serbenet,
>>
>> I believe the first call to the data source is used to discover
>> metadeta. If I am not mistaken it should not be returning all rows.
>> The second call gets all the data. Hence the onfetch being called
>> only once.
>>
>> Jason
>>
>>
>> serbenet wrote:
>>> The onfetch method is called only once on my system too.
>>> However I still see in the mysql logs that the query is executed twice.
>>> You do not need the mysql admin console to check the mysql logs.
>>> All you need to do is to start your mysql server with the --log option.
>>> Each SQL statement received from clients will then be logged to a
>>> file called
>>> host_name.log in the data directory.
>>> Here is more information:
>>> http://dev.mysql.com/doc/refman/5.0/en/query-log.html
>>> Ben
>>> On Tue, 27 May 2008 23:12:13 +0100, Jason Weathersby
>>> <jasonweathersby@alltel.net> wrote:
>>>
>>>> Serbenet,
>>>>
>>>> Can you modify your report to add an onFetch script to your data set.
>>>> Enter something like:
>>>>
>>>> importPackage( Packages.java.io );
>>>> out = new PrintWriter( new FileWriter( "c:/test/dsaccess.txt", true
>>>> ) );
>>>> out.println( "onfetch");
>>>> out.close();
>>>>
>>>> I did this and only get the one call. I do not have the mysql admin
>>>> console.
>>>>
>>>>
>>>> Jason
>>>>
>>>> serbenet wrote:
>>>>> No I don't have any look ahead aggregates and I don't use grouping.
>>>>> I have attached a simple report showing the issue on my system.
>>>>> When looking at the logs of the mysql server I can see the query
>>>>> "select 1 AS col1" being executed twice.
>>>>> Here is a description of my system:
>>>>> - Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT
>>>>> 2.2.2) running on windows XP pro sp2 with jdk1.6.0_03
>>>>> - The databse server is a mysql server version 5.0.37 running on
>>>>> redhat enterprise 4.
>>>>> - The JDBC connector used is the official JDBC driver for MySQL,
>>>>> MySQL Connector/J 5.1.6.
>>>>> Ben
>>>>> On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby
>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>
>>>>>> Ben,
>>>>>>
>>>>>> On my system it is executed twice. Do yo have any look ahead
>>>>>> aggregates in the report? Are you grouping?
>>>>>>
>>>>>> Jason
>>>>>>
>>>>>> serbenet wrote:
>>>>>>> Thanks.
>>>>>>> After having given a name to my data items, I could see them in
>>>>>>> the list of previous data items.
>>>>>>> I have now binded only one data item to my dataset and all other
>>>>>>> data items are binded to this previous data item.
>>>>>>> This definitely seems to improve things and the query is now
>>>>>>> executed only twice (instead of 16 times before!!).
>>>>>>> Also I do not understand why it is still executed twice when once
>>>>>>> should be enough, but that's definitely already a big improvement.
>>>>>>> I have also tested the behaviour with 2.3 RC1 and data set
>>>>>>> caching seems to have been improved a lot.
>>>>>>> In any cases, a query is never executed more than twice, even if
>>>>>>> the report contains several data items directly bound to the
>>>>>>> dataset.
>>>>>>> But also and again the query is executed twice when once should
>>>>>>> be enough. Any thoughts on this? Is it really necessary to
>>>>>>> execute the query twice?
>>>>>>> Thanks again for your help.
>>>>>>> Ben
>>>>>>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby
>>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>>
>>>>>>>> Caching of datasets is being improved, but event now yours
>>>>>>>> should not have a problem. The first time you use a data set on
>>>>>>>> a report element, name it. This is in the general properties
>>>>>>>> for the item. Then anytime you want to use the query again just
>>>>>>>> select the binding tab of your new element, select the radial
>>>>>>>> for report items and your named items should be in the list.
>>>>>>>> The reason you are seeing only charts, it because those are
>>>>>>>> probably the only items named in your report.
>>>>>>>>
>>>>>>>> Jason
>>>>>>>>
>>>>>>>>
>>>>>>>> serbenet wrote:
>>>>>>>>> Thanks for the quick reply.
>>>>>>>>> Each data item in my reports are bound to the dataset so,
>>>>>>>>> following your explanation,
>>>>>>>>> I understand now why the queries get executed several times.
>>>>>>>>> Is this a normal behaviour or is this something that is going
>>>>>>>>> to be changed in a later version?
>>>>>>>>> Say if 2 data items use the same dataset then surely the data
>>>>>>>>> do not need to be fetched twice from the database.
>>>>>>>>> Now in some places I can put all the data items in one big
>>>>>>>>> table, and only bind this table to the dataset. This solves the
>>>>>>>>> issue. But depending on the layout of my report, I can not
>>>>>>>>> always put all data items in one big table. I have tried
>>>>>>>>> binding to a previous report item as you mentionned, but it
>>>>>>>>> seems that I can only bind to previous chart items, I do not
>>>>>>>>> have the choice to select a previous data item. Is this a
>>>>>>>>> limitation of 2.2.2?
>>>>>>>>> Thanks again
>>>>>>>>> Ben
>>>>>>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby
>>>>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>>>>
>>>>>>>>>> Serbenet,
>>>>>>>>>>
>>>>>>>>>> Verify that each individual data item is not bound to a
>>>>>>>>>> dataset. They should be in a table that is bound to the
>>>>>>>>>> dataset. If they are bound to the dataset, each will execute
>>>>>>>>>> the query. In 2.2.2 you should also be able to bind to a
>>>>>>>>>> previous report item. For example create on dataset and bind
>>>>>>>>>> it to a table1, name it in the general properties tab. Add a
>>>>>>>>>> second table, select the binding tab and bind it to a report
>>>>>>>>>> item. The first table should be in the selection.
>>>>>>>>>>
>>>>>>>>>> Jason
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> serbenet wrote:
>>>>>>>>>>> Hello all,
>>>>>>>>>>> I have noticed that when generating a report with BIRT, a
>>>>>>>>>>> same SQL query may actually be executed several times.
>>>>>>>>>>> This behaviour is probably ok for small queries but when a
>>>>>>>>>>> query takes up to a few seconds you surely do not want it to
>>>>>>>>>>> be executed several times.
>>>>>>>>>>> My first example is I create a report with one datasource (a
>>>>>>>>>>> mysql server in my case), one dataset (Say "SELECT 1 AS
>>>>>>>>>>> column1"), and one data item that displays the value of
>>>>>>>>>>> column1. After running the report I can see in the mysql
>>>>>>>>>>> query log that the query "SELECT 1 AS column1" was executed
>>>>>>>>>>> twice.
>>>>>>>>>>> Now even worse: I change my query to say "SELECT 1 AS
>>>>>>>>>>> column1, 2 AS column2, 3 As column3" and add 2 more data
>>>>>>>>>>> times to the report so as to display column2 and column3. On
>>>>>>>>>>> generation of the report, the query will actually be executed
>>>>>>>>>>> 6 times!!!
>>>>>>>>>>> I have given you a basic example here, but I have reports
>>>>>>>>>>> which execute quite complicated queries and display the
>>>>>>>>>>> results at different places in the report. I can see that
>>>>>>>>>>> the queries are executed several times on my db server and
>>>>>>>>>>> that turns me mad!!
>>>>>>>>>>> Is this an intended behaviour of BIRT? If yes is there a way
>>>>>>>>>>> to avoid the queries from being executed several times. If
>>>>>>>>>>> not is there a plan to fix the issue?
>>>>>>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>>>>>>> I am using BIRT 2.2.2 connecting to a mysql database server
>>>>>>>>>>> 5.0.37 and using mysql jdbc connector 3.0
>>>>>>>>>>> Ben
>>>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>
>>>
>
>
>
Re: Performance issues with db query being executed several times [message #362871 is a reply to message #362862] Thu, 29 May 2008 08:28 Go to previous messageGo to next message
serbenet is currently offline serbenetFriend
Messages: 517
Registered: July 2009
Senior Member
Here it is:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D234553

Thanks


On Wed, 28 May 2008 19:36:25 +0100, Jason Weathersby =

<jasonweathersby@alltel.net> wrote:

> Ben,
>
> Can you log a bugzilla entry to track this?
>
> Jason
>
> serbenet wrote:
>> Jason,
>> I believe you are right, the first query is probably used to get =

>> column names and
>> column types and the second query may be used to fetch the data. The =
=

>> onfetch function is called
>> only during the second call.
>> However the first query takes as much time to process as the second =
=

>> query, which makes my report
>> generation twice slower compare to what it could be.
>> For example, here is a dump of the mysql log file after generating a=
=

>> report:
>> 8 17:10:58 199 Connect root@localhost on xxx
>> 199 Init DB xxx
>> 199 Query select round('inf'), round('-inf'), =

>> round('nan')
>> 199 Query SHOW VARIABLES
>> 199 Query SET autocommit=3D1
>> 199 Query SET NAMES latin1
>> 199 Query select avg(t1.ulId) as f1 from table1 =
as =

>> t1 join (select * from table1 limit 30) as t2
>> 8 17:11:12 199 Query select avg(t1.ulId) as f1 from table1 =
as =

>> t1 join (select * from table1 limit 30) as t2
>> 8 17:11:27 199 Quit
>> The query executed is "select avg(t1.ulId) as f1 from table1 as t1 =

>> join (select * from table1 limit 30) as t2".
>> It is a slow query which on its own takes about 15 seconds to process=
..
>> Looking at the log I can see that the query is executed twice,
>> first time starting at 17:10:58 and finishing at 17:11:12 and the sec=
ond
>> time starting at 17:11:12 and finishing at 17:11:27. So the total tim=
e =

>> to generate
>> the report was roughfly 30 seconds, when it should have been only 15 =
=

>> seconds if the query was
>> executed only once.
>> I do not know if it would be possible to do both metadata discovery =
=

>> and data retrieval
>> in one query, but I believe there is a big performance improvement th=
at =

>> could be made, and it
>> is probably worth investigating the matter.
>> Ben
>> On Wed, 28 May 2008 16:42:07 +0100, Jason Weathersby =

>> <jasonweathersby@alltel.net> wrote:
>>
>>> Serbenet,
>>>
>>> I believe the first call to the data source is used to discover =

>>> metadeta. If I am not mistaken it should not be returning all rows.=
=

>>> The second call gets all the data. Hence the onfetch being called =

>>> only once.
>>>
>>> Jason
>>>
>>>
>>> serbenet wrote:
>>>> The onfetch method is called only once on my system too.
>>>> However I still see in the mysql logs that the query is executed =

>>>> twice.
>>>> You do not need the mysql admin console to check the mysql logs.
>>>> All you need to do is to start your mysql server with the --log =

>>>> option.
>>>> Each SQL statement received from clients will then be logged to a =

>>>> file called
>>>> host_name.log in the data directory.
>>>> Here is more information:
>>>> http://dev.mysql.com/doc/refman/5.0/en/query-log.html
>>>> Ben
>>>> On Tue, 27 May 2008 23:12:13 +0100, Jason Weathersby =

>>>> <jasonweathersby@alltel.net> wrote:
>>>>
>>>>> Serbenet,
>>>>>
>>>>> Can you modify your report to add an onFetch script to your data s=
et.
>>>>> Enter something like:
>>>>>
>>>>> importPackage( Packages.java.io );
>>>>> out =3D new PrintWriter( new FileWriter( "c:/test/dsaccess.txt", t=
rue =

>>>>> ) );
>>>>> out.println( "onfetch");
>>>>> out.close();
>>>>>
>>>>> I did this and only get the one call. I do not have the mysql adm=
in =

>>>>> console.
>>>>>
>>>>>
>>>>> Jason
>>>>>
>>>>> serbenet wrote:
>>>>>> No I don't have any look ahead aggregates and I don't use groupin=
g.
>>>>>> I have attached a simple report showing the issue on my system.
>>>>>> When looking at the logs of the mysql server I can see the query =
=

>>>>>> "select 1 AS col1" being executed twice.
>>>>>> Here is a description of my system:
>>>>>> - Eclipse + BIRT 2.3.0RC1 (But the problem is the same with BIRT =
=

>>>>>> 2.2.2) running on windows XP pro sp2 with jdk1.6.0_03
>>>>>> - The databse server is a mysql server version 5.0.37 running on =
=

>>>>>> redhat enterprise 4.
>>>>>> - The JDBC connector used is the official JDBC driver for MySQL, =
=

>>>>>> MySQL Connector/J 5.1.6.
>>>>>> Ben
>>>>>> On Tue, 27 May 2008 15:42:42 +0100, Jason Weathersby =

>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>
>>>>>>> Ben,
>>>>>>>
>>>>>>> On my system it is executed twice. Do yo have any look ahead =

>>>>>>> aggregates in the report? Are you grouping?
>>>>>>>
>>>>>>> Jason
>>>>>>>
>>>>>>> serbenet wrote:
>>>>>>>> Thanks.
>>>>>>>> After having given a name to my data items, I could see them i=
n =

>>>>>>>> the list of previous data items.
>>>>>>>> I have now binded only one data item to my dataset and all othe=
r =

>>>>>>>> data items are binded to this previous data item.
>>>>>>>> This definitely seems to improve things and the query is now =

>>>>>>>> executed only twice (instead of 16 times before!!).
>>>>>>>> Also I do not understand why it is still executed twice when on=
ce =

>>>>>>>> should be enough, but that's definitely already a big improveme=
nt.
>>>>>>>> I have also tested the behaviour with 2.3 RC1 and data set =

>>>>>>>> caching seems to have been improved a lot.
>>>>>>>> In any cases, a query is never executed more than twice, even i=
f =

>>>>>>>> the report contains several data items directly bound to the =

>>>>>>>> dataset.
>>>>>>>> But also and again the query is executed twice when once should=
=

>>>>>>>> be enough. Any thoughts on this? Is it really necessary to =

>>>>>>>> execute the query twice?
>>>>>>>> Thanks again for your help.
>>>>>>>> Ben
>>>>>>>> On Thu, 22 May 2008 19:13:36 +0100, Jason Weathersby =

>>>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>>>
>>>>>>>>> Caching of datasets is being improved, but event now yours =

>>>>>>>>> should not have a problem. The first time you use a data set =
on =

>>>>>>>>> a report element, name it. This is in the general properties =
=

>>>>>>>>> for the item. Then anytime you want to use the query again ju=
st =

>>>>>>>>> select the binding tab of your new element, select the radial =
=

>>>>>>>>> for report items and your named items should be in the list. =
=

>>>>>>>>> The reason you are seeing only charts, it because those are =

>>>>>>>>> probably the only items named in your report.
>>>>>>>>>
>>>>>>>>> Jason
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> serbenet wrote:
>>>>>>>>>> Thanks for the quick reply.
>>>>>>>>>> Each data item in my reports are bound to the dataset so, =

>>>>>>>>>> following your explanation,
>>>>>>>>>> I understand now why the queries get executed several times.
>>>>>>>>>> Is this a normal behaviour or is this something that is going=
=

>>>>>>>>>> to be changed in a later version?
>>>>>>>>>> Say if 2 data items use the same dataset then surely the data=
=

>>>>>>>>>> do not need to be fetched twice from the database.
>>>>>>>>>> Now in some places I can put all the data items in one big =

>>>>>>>>>> table, and only bind this table to the dataset. This solves t=
he =

>>>>>>>>>> issue. But depending on the layout of my report, I can not =

>>>>>>>>>> always put all data items in one big table. I have tried =

>>>>>>>>>> binding to a previous report item as you mentionned, but it =

>>>>>>>>>> seems that I can only bind to previous chart items, I do not =
=

>>>>>>>>>> have the choice to select a previous data item. Is this a =

>>>>>>>>>> limitation of 2.2.2?
>>>>>>>>>> Thanks again
>>>>>>>>>> Ben
>>>>>>>>>> On Thu, 22 May 2008 16:46:56 +0100, Jason Weathersby =

>>>>>>>>>> <jasonweathersby@alltel.net> wrote:
>>>>>>>>>>
>>>>>>>>>>> Serbenet,
>>>>>>>>>>>
>>>>>>>>>>> Verify that each individual data item is not bound to a =

>>>>>>>>>>> dataset. They should be in a table that is bound to the =

>>>>>>>>>>> dataset. If they are bound to the dataset, each will execut=
e =

>>>>>>>>>>> the query. In 2.2.2 you should also be able to bind to a =

>>>>>>>>>>> previous report item. For example create on dataset and bin=
d =

>>>>>>>>>>> it to a table1, name it in the general properties tab. Add =
a =

>>>>>>>>>>> second table, select the binding tab and bind it to a report=
=

>>>>>>>>>>> item. The first table should be in the selection.
>>>>>>>>>>>
>>>>>>>>>>> Jason
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> serbenet wrote:
>>>>>>>>>>>> Hello all,
>>>>>>>>>>>> I have noticed that when generating a report with BIRT, a =
=

>>>>>>>>>>>> same SQL query may actually be executed several times.
>>>>>>>>>>>> This behaviour is probably ok for small queries but when a =
=

>>>>>>>>>>>> query takes up to a few seconds you surely do not want it t=
o =

>>>>>>>>>>>> be executed several times.
>>>>>>>>>>>> My first example is I create a report with one datasource =
(a =

>>>>>>>>>>>> mysql server in my case), one dataset (Say "SELECT 1 AS =

>>>>>>>>>>>> column1"), and one data item that displays the value of =

>>>>>>>>>>>> column1. After running the report I can see in the mysql =

>>>>>>>>>>>> query log that the query "SELECT 1 AS column1" was executed=
=

>>>>>>>>>>>> twice.
>>>>>>>>>>>> Now even worse: I change my query to say "SELECT 1 AS =

>>>>>>>>>>>> column1, 2 AS column2, 3 As column3" and add 2 more data =

>>>>>>>>>>>> times to the report so as to display column2 and column3. O=
n =

>>>>>>>>>>>> generation of the report, the query will actually be execut=
ed =

>>>>>>>>>>>> 6 times!!!
>>>>>>>>>>>> I have given you a basic example here, but I have reports =
=

>>>>>>>>>>>> which execute quite complicated queries and display the =

>>>>>>>>>>>> results at different places in the report. I can see that =
=

>>>>>>>>>>>> the queries are executed several times on my db server and =
=

>>>>>>>>>>>> that turns me mad!!
>>>>>>>>>>>> Is this an intended behaviour of BIRT? If yes is there a w=
ay =

>>>>>>>>>>>> to avoid the queries from being executed several times. If =
=

>>>>>>>>>>>> not is there a plan to fix the issue?
>>>>>>>>>>>> Thanks a lot in advance, this is very critical for me.
>>>>>>>>>>>> I am using BIRT 2.2.2 connecting to a mysql database serve=
r =

>>>>>>>>>>>> 5.0.37 and using mysql jdbc connector 3.0
>>>>>>>>>>>> Ben
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>
>>>>>>
>>>>
>>



-- =

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Re: Performance issues with db query being executed several times [message #1782550 is a reply to message #362871] Mon, 26 February 2018 11:25 Go to previous message
Leonardo Pinho is currently offline Leonardo PinhoFriend
Messages: 3
Registered: February 2018
Junior Member
I have the same problem. BIRT 4.5/4.6/4.7 - mysql jdbc driver 5.1.35/5.1.45.

BIRT is running the same query twice. First time w/ "set limit = 1" and then w/ "set limit = default". This is very inefficient.

2018-02-23T14:18:44.107184Z 4 Query SET SQL_SELECT_LIMIT=1
2018-02-23T14:18:44.107184Z 4 Query select id from game where id >= 1
2018-02-23T14:18:44.125145Z 4 Query SET SQL_SELECT_LIMIT=DEFAULT
2018-02-23T14:18:44.125145Z 4 Query select id from game where id >= 1

When we use parameters in the SQL, it is even worse as the first query does not have any parameter filled.

2018-02-22T20:18:28.159070Z 52 Query SET SQL_SELECT_LIMIT=1
2018-02-22T20:18:28.159070Z 52 Query select id,username from customer where id = '' and username != ''
2018-02-22T20:18:28.161104Z 52 Query SET SQL_SELECT_LIMIT=DEFAULT
2018-02-22T20:18:28.161104Z 52 Query select id,username from customer where id = 123123 and username != 'dasdsadsa'

I already tried to add cacheResultSetMetadata=true to the connection string but it didn't help.

Any idea?

Thanks,
Leonardo

Previous Topic:Select INTO temporary table
Next Topic:Set Parameter using ActiveX
Goto Forum:
  


Current Time: Mon Sep 24 20:56:29 GMT 2018

Powered by FUDForum. Page generated in 0.02619 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top