Query on iBatis Select - fetchSize

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Query on iBatis Select - fetchSize

m. arshad

Hi All,

 

Guess this is the right place to put in my query, if not please ignore . Sorry for inconvenience.

 

 Actually I'm using iBatis with Spring, now I have a query which returns more records and so I want to limit the data (rows)

at one shot. Say, setting fetchSize='20', but seems like this is not

working and the query is taking a long time to get all the records at one shot.

 

Thanks,

[hidden email] MARSH---User @ googlegroups.com

Reply | Threaded
Open this post in threaded view
|

Re: Query on iBatis Select - fetchSize

Jeff Butler
fetchSize is a performance strategy.  It does not limit the number of
rows returned, rather it is a driver hint regarding the number of rows
to be returned in a single communication between the JDBC driver and
the DB.

If you are looking to limit the number of rows returned, then most SQL
variants have some syntax for that as part of the select statement.

Jeff Butler



On Mon, Sep 27, 2010 at 12:46 PM, m. arshad <[hidden email]> wrote:

> Hi All,
>
>
>
> Guess this is the right place to put in my query, if not please ignore .
> Sorry for inconvenience.
>
>
>
>  Actually I'm using iBatis with Spring, now I have a query which returns
> more records and so I want to limit the data (rows)
>
> at one shot. Say, setting fetchSize='20', but seems like this is not
>
> working and the query is taking a long time to get all the records at one
> shot.
>
>
>
> Thanks,
>
>  MARSH---User @ googlegroups.com
Reply | Threaded
Open this post in threaded view
|

Re: Query on iBatis Select - fetchSize

m. arshad
Thanks Jeff,
 
Yep even I need to have this is place for performance improvement only. Currently the query returns me about 200 rows at one shot and as a result the user may have to wait for a long time untill the query executes and the data is available. Thus to avoid this scenario I need to have this "fetchSize" in place so that it can improve my query performance .
 
Note : I need the entire result set to be procured but only thing is that I need to fetch only a few set at one shot because by that way I can avoid the user waiting for long time.
 
Thanks!!!!!!!!!!! 

On Tue, Sep 28, 2010 at 12:03 AM, Jeff B utler <[hidden email]> wrote:
fetchSize is a performance strategy.  It does not limit the number of
rows returned, rather it is a driver hint regarding the number of rows
to be returned in a single communication between the JDBC driver and
the DB.

If you are looking to limit the number of rows returned, then most SQL
variants have some syntax for that as part of the select statement.

Jeff Butler



On Mon, Sep 27, 2010 at 12:46 PM, m. arshad <[hidden email]> wrote:
> Hi All,
>
>
>
> Guess this is the right place to put in my query, if not please ignore .
> Sorry for inconvenience.
>
>
>
>  Actually I'm using iBatis with Spring, now I have a query which returns
> more records and so I want to limit the data (rows)
>
> at one shot. Say, setting fetchSize='20', but seems like this is not
>
> working and the query is taking a long time to get all the records at one
> shot.
>
>
>
> Thanks,
>
>  MARSH---User @ googlegroups.com

Reply | Threaded
Open this post in threaded view
|

Re: Query on iBatis Select - fetchSize

Martin Kuen


On Tue, Sep 28, 2010 at 7:12 PM, m. arshad <[hidden email]> wrote:
 
Yep even I need to have this is place for performance improvement only. Currently the query returns me about 200 rows at one shot and as a result the user may have to wait for a long time untill the query executes and the data is available. Thus to avoid this scenario I need to have this "fetchSize" in place so that it can improve my query performance .
 
"fetchSize" doesn't do that, as stated by Jeff --> "It doesn't limit the number of rows returned, [...]"

 
Note : I need the entire result set to be procured but only thing is that I need to fetch only a few set at one shot because by that way I can avoid the user waiting for long time.
Jeff said:
If you are looking to limit the number of rows returned, then most SQL
variants have some syntax for that as part of the select statement.

Example for MySQL: Use a "limit" clause in your sql query

select e.id, e.salary
from employee e
where salary > '2500.0'
limit 100, 50;

This query will return 50 rows or less. The first number in limit is an offset, so the first 100 results are skipped and the next 50 rows are returned.

"limit 50" behaves like "limit 0, 50" --> don't skip anything and return the first 50 results but not more.

The "result set" is produced in its entirety, but only a part is returned. However, chances are good that "creating the result" takes long, whereas "sending result to client (DB-->YourApp)" works comparably fast . . . . .



Best Regards,

Martin


Reply | Threaded
Open this post in threaded view
|

Re: Query on iBatis Select - fetchSize

raupach
In reply to this post by Jeff Butler
This issue is already open. http://code.google.com/p/mybatis/issues/detail?id=65

I had the same problem and prefer the maxRows property of the jdbc driver instead of twiddling with the sql statement.

There is a patch attached. You could give it a try, even though its not officially approved.

Björn

On Sep 27, 2010, at 8:33 PM, Jeff Butler wrote:

> fetchSize is a performance strategy.  It does not limit the number of
> rows returned, rather it is a driver hint regarding the number of rows
> to be returned in a single communication between the JDBC driver and
> the DB.
>
> If you are looking to limit the number of rows returned, then most SQL
> variants have some syntax for that as part of the select statement.
>
> Jeff Butler
>
>
>
> On Mon, Sep 27, 2010 at 12:46 PM, m. arshad <[hidden email]> wrote:
>> Hi All,
>>
>>
>>
>> Guess this is the right place to put in my query, if not please ignore .
>> Sorry for inconvenience.
>>
>>
>>
>>  Actually I'm using iBatis with Spring, now I have a query which returns
>> more records and so I want to limit the data (rows)
>>
>> at one shot. Say, setting fetchSize='20', but seems like this is not
>>
>> working and the query is taking a long time to get all the records at one
>> shot.
>>
>>
>>
>> Thanks,
>>
>>  MARSH---User @ googlegroups.com