Eclipse Community Forums: BIRT » Setting jdbc fetch size for dataset

Home » Archived » BIRT » Setting jdbc fetch size for dataset

Setting jdbc fetch size for dataset [message #367939]

Tue, 21 April 2009 06:28

Eclipse User

Is it possible to set the fetch size for a dataset? Ive examined the api
and can find no mention of this capability. Is it simply missing?

Thanks.

Re: Setting jdbc fetch size for dataset [message #367955 is a reply to message #367939]

Wed, 22 April 2009 00:02

Eclipse User

Originally posted by: johnw.innoventsolutions.com

If your using the API, wouldn't IRunTask.setMaxRowsPerQuery(int) work? I
haven't tried it first hand...

john mcteague wrote:
> Is it possible to set the fetch size for a dataset? Ive examined the api
> and can find no mention of this capability. Is it simply missing?
>
> Thanks.
>

Re: Setting jdbc fetch size for dataset [message #368087 is a reply to message #367955]

Fri, 01 May 2009 10:52

Eclipse User

setMaxRowsPerQuery limits the amount of data returned (i.e.
preparedStatement.setMaxRows). Fetchsize sets how many rows are returned
each time the driver gets data from the server side, but the total number
of rows is unchanged.

The fetchsize can be tuned to help performance, particularly with Oracle
where the default fetchsize is only 10. If you are returning 1000's of
rows, this can be quite inefficent.

John Ward wrote:

> If your using the API, wouldn't IRunTask.setMaxRowsPerQuery(int) work? I
> haven't tried it first hand...

> john mcteague wrote:
>> Is it possible to set the fetch size for a dataset? Ive examined the api
>> and can find no mention of this capability. Is it simply missing?
>>
>> Thanks.
>>

Re: Setting jdbc fetch size for dataset [message #483456 is a reply to message #367939]

Tue, 01 September 2009 12:26

Eclipse User

I'd also love to know if this is possible. We're pulling back some large
Oracle CLOBs and it's just taking forever through BIRT.

And a more general Oracle question for anyone who might be listening - is
there a separate knob to tweak the way in which CLOB data is retrieved?
From the .NET world there's a row fetch size, but separate from that you
can tweak CLOB fetching, which by default makes a separate round trip for
each CLOB as it's accessed. In my app I'd rather be retrieving it at the
same time I retrieve the other fields in the row.

Re: Setting jdbc fetch size for dataset [message #483479 is a reply to message #483456]

Tue, 01 September 2009 13:25

Eclipse User

To clarify, since the link back to the original post doesn't appear to be
working, Oracle's JDBC driver defaults to a fetch size of 10, meaning that
to retrieve 6000 rows the JDBC driver does 600 round-trips to the DB.
It's a common best practice to increase this value for data sets with many
rows.

We're seeing BIRT (via JDBC) severely under-performing Oracle's query
tools on a data set of this size, and would like to try tweaking this.

I'm no JDBC expert, but it looks like Statement.setFetchSize() is the only
way to set it. Oracle has some Connection-level properties that serve as
defaults for all Statements issued on that Connection, but I don't think
that's a JDBC standard.

I've been toying w/ the debugger to see if I could use an event handler to
navigate from the ODA data source/set down to an underlying JDBC
connection/statement, but I'm getting nowhere.

Other alternatives I've considered are:
- Building a custom ODA source; ouch!
- Building a connection in Java/JavaScript, setting the Oracle-specific
default FetchSize, then using "OdaJDBCDriverPassInConnection" to force
that connection into my Data Source.

Re: Setting jdbc fetch size for dataset [message #800653 is a reply to message #367939]

Fri, 17 February 2012 05:13

Eclipse User

Hi,

Is this feature available in BIRT 2.3.2. We are using BIRT 2.3.2 and we are very close to going live, so we cannot move to the new version. So is the feature of setting the JDBC Fetch Size available in BIRT 2.3.2?

Thanks,
Ananta

Re: Setting jdbc fetch size for dataset [message #801001 is a reply to message #800653]

Fri, 17 February 2012 15:04

Eclipse User

I believe this was in 2.3.2. If you are using the viewer add
&__maxrows=5 to the url to test it.

Jason

On 2/17/2012 5:13 AM, Anantasrinivas Lakshmanan wrote:
> Hi,
>
> Is this feature available in BIRT 2.3.2. We are using BIRT 2.3.2 and we
> are very close to going live, so we cannot move to the new version. So
> is the feature of setting the JDBC Fetch Size available in BIRT 2.3.2?
>
> Thanks,
> Ananta

Re: Setting jdbc fetch size for dataset [message #802570 is a reply to message #801001]

Mon, 20 February 2012 00:42

Eclipse User

Hi Jason,

Thanks for your response.

The maxRows is available but i want the settings for RowFetchSize which is the number of rows retreived from the DB to the app server in one request. I dont want to restrict the number of rows returned but i want to reduce the number of calls to the DB for getting the result set.
In 2.6.1 i see a parameter rowFecthSize whcih does this task.

Thanks,
Ananta.

Re: Setting jdbc fetch size for dataset [message #803637 is a reply to message #802570]

Tue, 21 February 2012 10:59

Eclipse User

I checked the 2.3.2 source and that property does not exist. If you
have to have it in 2.3.2 you will need to modify the code or implement a
driverBridge extension point like what is described here:
http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html

Jason

On 2/20/2012 12:42 AM, Anantasrinivas Lakshmanan wrote:
> Hi Jason,
>
> Thanks for your response.
> The maxRows is available but i want the settings for RowFetchSize which
> is the number of rows retreived from the DB to the app server in one
> request. I dont want to restrict the number of rows returned but i want
> to reduce the number of calls to the DB for getting the result set.
> In 2.6.1 i see a parameter rowFecthSize whcih does this task.
>
> Thanks,
> Ananta.

Previous Topic:	Chart incomplete in PDF
Next Topic:	Problem to download birt via eclipse

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 15:20:28 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter