Question on getDataSetByQuery

Questions, answers, tips and ideas on Servoy Client

Question on getDataSetByQuery

Postby umberto.piccolo.lavoro » Wed May 23, 2012 4:22 pm

Hi,

I have a question about using a parameter of this method
The max_returned_rows is the maximum number of rows returned by the query, but there is a difference with the LIMIT (in sql comand )??
For example:

var v_query = 'SELECT * FROM table'
var v_data = databaseManager.getDataSetByQuery( server_loc, v_query, null, 100 );

and

var v_query = 'SELECT * FROM table LIMIT 100'
var v_data = databaseManager.getDataSetByQuery( server_loc, v_query, null, -1 );

There is a difference in architecture?
umberto.piccolo.lavoro
 
Posts: 3
Joined: Wed May 23, 2012 4:03 pm

Re: Question on getDataSetByQuery

Postby ROCLASI » Wed May 23, 2012 8:48 pm

Hi Umberto,

Essentially the 2 limits do the same thing. The thing is that the LIMIT keyword is not used by all database vendors. If you want to limit your result set in MSSQL or Sybase you need to use the syntax 'SELECT TOP 100 column1, column2 FROM tableName'.
Using the limit parameter in the getDataSetByQuery() function will use the proper syntax for you. Just look at the 'Performance Data' page in the Servoy admin pages to see what actual queries are send to the backend database.

Hope this explains it for you.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5332
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Question on getDataSetByQuery

Postby umberto.piccolo.lavoro » Thu May 24, 2012 9:35 am

Thanks Robert,
If I understand correctly, according to your argument, in case max_returned_rows = 100 should I see a query slightly different from the initial.

This is the case where I use a LIMIT in the query and in Performance data of course see it that way:

SELECT versione, soluzione, release_tipo, numero_release_tipo FROM versioning_release WHERE soluzione = ? and versione like ? and id_versioning_release != ? ORDER BY soluzione desc, versione desc, release_tipo desc, numero_release_tipo desc LIMIT 10

But, when i use the max_returned_rows I thought that Servoy put some limit in the query in Performance data and instead:

SELECT versione, soluzione, release_tipo, numero_release_tipo FROM versioning_release WHERE soluzione = ? and versione like ? and id_versioning_release != ? ORDER BY soluzione desc, versione desc, release_tipo desc, numero_release_tipo desc
-->no limit

It's correct ??
Thanks for your time.
umberto.piccolo.lavoro
 
Posts: 3
Joined: Wed May 23, 2012 4:03 pm

Re: Question on getDataSetByQuery

Postby ROCLASI » Thu May 24, 2012 4:17 pm

Hi Umberto,

You are absolutely right and I was wrong. :oops:
I was thinking that the JDBC driver would add the LIMIT (or TOP) to the query but it doesn't.

What it seems to be doing is to send the query to the backend database as-is and then only retrieve the first x rows according to your max_returned_rows setting.
This is all fine but it does mean that in a lot of use-cases the database does need to process the whole resultset instead of the limited set.
So using LIMIT or TOP in your SQL will be more efficient on those cases.

To illustrate this I created a test table with 1 million records like so:
Code: Select all
create table test as
    select
        i as id,
        i || ' ' || repeat('padding', 2) as a,
        repeat('xxx', 500) as b
    from generate_series(1,1000000) i;

Created an index on it:
Code: Select all
create index test_id on test (id);

And made sure the query planner had all it's statistics:
Code: Select all
ANALYZE test;


Then I ran the following method:
Code: Select all
var _dStartTime = new Date(),
    _sQuery = "select * from test WHERE id > 1000 ORDER BY a",
    _ds = databaseManager.getDataSetByQuery("rss_main", _sQuery, null, 10);

// Time it took for the query with a JDBC limit
application.output("JDBC limit: " + utils.numberFormat( (new Date() - _dStartTime) / 1000, '#.###') + " seconds");

_dStartTime = new Date();
_sQuery = "select * from test WHERE id > 1000 ORDER BY a LIMIT 10";
_ds = databaseManager.getDataSetByQuery("rss_main", _sQuery, null, -1);
// Time it took for the query with a SQL limit
application.output("SQL limit: " + utils.numberFormat( (new Date() - _dStartTime) / 1000, '#.###') + " seconds");

_dStartTime = new Date();
_ds = databaseManager.getDataSetByQuery("rss_main", _sQuery, null, 10);
// Time it took for the query with a JDBC + SQL limit
application.output("JDBC + SQL limit: " + utils.numberFormat( (new Date() - _dStartTime) / 1000, '#.###') + " seconds");

I ran this method a few times to make sure all queries would use the same cached data (if any).
This is what it eventually resulted in:
Code: Select all
JDBC limit: 8.779 seconds
SQL limit: 1.864 seconds
JDBC + SQL limit: 1.841 seconds


So you see there is a penalty when using only the max_returned_rows limiter when using larger tables.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5332
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Question on getDataSetByQuery

Postby umberto.piccolo.lavoro » Fri May 25, 2012 9:17 am

Thank
You explained very well.

Regards
umberto.piccolo.lavoro
 
Posts: 3
Joined: Wed May 23, 2012 4:03 pm


Return to Servoy Client

Who is online

Users browsing this forum: No registered users and 2 guests