Query slower in Servoy than PGAdmin. Why?

Classic Servoy
1

Hi all,

we have some performance problems with our Servoy system and are trying to track down the causes. We use databaseManager.getDataSetByQuery(some_query,some_array_of_arguments) a lot.

SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' || to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE document_number = ? OR document_customer_acc_no = ? ORDER BY document_date DESC OFFSET ? LIMIT 26

This one takes about 8 seconds on average according to ‘Performance Data’ in Servoy Admin. However, when I run the same Query in PGAdmin, it takes 100ms or less.
Any ideas of what may be going on?

Port used by RMI Registry: 1099
Uptime: 40 days 20 hours 55 minutes 58 seconds 

JVM Information
java.vm.name=Java HotSpot(TM) 64-Bit Server VM
java.version=1.6.0_33
java.vm.info=mixed mode
java.vm.vendor=Apple Inc. 

Operating System Information
os.name=Mac OS X
os.version=10.7.4
os.arch=x86_64 

System Information
Heap Space Memory: allocated=984828K, used=576653K, max=1294336K
Non-Heap Space Memory: allocated=153732K, used=105591K, max=180224K
2

Hi Christian,

which versions are we talking here?

3

Very sorry, left out one of the most vital pieces of information: Version 6.0.5

I have since noticed there is an entry in the release-notes for 6.0.7:

[fix] SVY-1860 Implicit joins are not treated well in Servoy 6 when used by loadRecords(Query, parms)

Is this a fix for what I’m experiencing?

4

No, I dont think so. I would think that foundset.loadRecords() is to Servoy quite a different ballgame then databaseManager.getDataSetByQuery()…
I don’t believe Servoy touches the SQL query handed over through databaseManager.getDataSetByQuery()

Regards,

5

One important difference in executing the query via Servoy or directly in PGAdmin is Servoy using prepared statements. (at least that’s what I understand from your post)
Note that PostgreSQL is not able to determine the same query plan for prepared statements as it can for fully written SQL.
Prepared statements contain variables where Postgres has to make an assumption of what to do (for example use a sequential scan vs. using indexed values)

So: SELECT myColumns FROM myTable WHERE pk IN (?,?,?,?,?) with params [1,2,3,4,5] can be much slower than the same query containing WHERE pk IN (1,2,3,4,5);

You could try to join the query and arguments in Servoy and pass it to the database.
That way you will have a one-on-one comparison.

6

Hi Christian,

Are you saying that you got this speed reduction when you moved to Servoy 6? Or did this issue just recently occurred on the same release.
Also how big is your acc_documents table? As Marc already pointed out when using prepared statements it can, in some use-cases, give slower queries just due to the fact that it can’t really apply the statistics it has on your data without knowing what you are exactly searching for (the params).
When you run the query in a test solution on the same data with a prepared statement do you see the same slow queries ?

7

Hi Robert,

the table in question has about 450,000 records. It has indexes on document_number and document_customer_acc_no. Searching by account number, i.e. a string, is much faster than by invoice number. I upgraded to 6.0.7 last night and the time dropped from about 8 seconds to 0.6 when searching numbers, about 0.1 when searching strings.

I will try to remove the parameters and send a full sql statement and see how much this reduces the time.

C.

8

We have had speed problems both on Servoy 5.2.x and 6.0.x.

9

Hi,

I have made quite a discovery, it is very important to specify the type of the parameters in Postgres if you write your own queries. Changing the query to:

SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' || to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE document_number = ?::integer OR document_customer_acc_no = ?::text ORDER BY document_date DESC OFFSET ?::integer LIMIT 26

speeds it up dramatically. Dramatically is the word.
From the Performance Data:

00:00:002	2	00:00:001	Custom	SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' || to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE document_number = ?::integer OR document_customer_acc_no = ?::text ORDER BY document_date DESC OFFSET ?::integer LIMIT 26

it now executes in 0.001 seconds instead of 0.500 seconds. A speed increase of 500 times. The cost of this is that I now have to pay a bit of attention to the parameters:

function acc_invoice_search(search_for, offset) {
	if (offset == null) offset = 0;

	var args = new Array();

	var query = "SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' ||  to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE ";
	query += " document_number = ?::integer OR document_customer_acc_no = ?::text ORDER BY document_date DESC OFFSET ?::integer LIMIT 26";
	if(isNaN(parseInt(search_for))) {
		args[0] = -99;
	} else {
		args[0] = parseInt(search_for);
	}
	args[1] = search_for.toUpperCase();
	args[2] = offset;

	var dataset = databaseManager.getDataSetByQuery('wildy_bookstore', query, args, 26);
	return dataset;
}

If the search string is not a number, I have to set it to something harmless, in my case I will never have an invoice with number -99.

10

Shocking ! Parameter type casting should happen only once in the query plan I would think…

Maybe a comment of our Postgresql guru Robert ?

Regards,

11

PostgreSQL has been more strict in recent versions on datatypes and normally gives errors if you pass a number for a varchar for instance. In older versions it did implicit type casting.
The fact that when you cast it to it’s proper datatypes makes it faster is kinda shocking. One would think it would OR cast it correctly OR give an error.
What version of PostgreSQL is this? Perhaps this is already fixed in newer releases, if not then this would be a nice use-case to file a bug report.

12

This is not the PostgreSQL that ships with Servoy.
I compile and install using the homebrew package manager on Mac OS X. It is relatively easy to do. I stopped using the PostgreSQL installer from http://www.postgresql.org because it causes issues with Ruby-on-Rails. I like to use the same database for everything everywhere. Beware that on Lion and Mountan Lion, Apple have already installed PostgreSQL which can lead to some confusion, using Google you can find articles on how to deal with it.

My development server has:
PostgreSQL 9.1.1 on x86_64-apple-darwin11.2.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2335.15.00), 64-bit

In Production we use:
PostgreSQL 9.0.4 on x86_64-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot 3), 64-bit

I see similar speedup when manually typing the parameters on both. It kind of makes sense since JavaScript does not really have the concept of variable types… But Servoy has…

13

ROCLASI:
PostgreSQL has been more strict in recent versions on datatypes and normally gives errors if you pass a number for a varchar for instance. In older versions it did implicit type casting.
The fact that when you cast it to it’s proper datatypes makes it faster is kinda shocking. One would think it would OR cast it correctly OR give an error.
What version of PostgreSQL is this? Perhaps this is already fixed in newer releases, if not then this would be a nice use-case to file a bug report.

When upgrading PostgreSQL, I have found custom queries in Servoy failing, but in almost all cases a simple typecast does the trick. Each version seems more strict.

14

Hi Christian,

Could supply me with a query plan for both types of queries (EXPLAIN ANALYZE SELECT…) and the params?
May also be sent in PM if you prefer that.

Also do realize that the 9.1 branch is at 9.1.5 and the 9.0 branch is at 9.0.9.

Thanks!

15

Yes, I’m behind on the minor updates. These databases are not accessible to the outside world.

Servoy 6.0.x will not let me do a EXPLAIN ANALYSE… and pgAdmin does not exhibit the slowness.

16 
 EXPLAIN ANALYSE SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' ||  to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE 
 document_number = 1210001 OR document_customer_acc_no = '1210001' ORDER BY document_date DESC OFFSET 0 LIMIT 26

“Limit (cost=119.99…120.06 rows=26 width=41) (actual time=0.063…0.064 rows=1 loops=1)”
" → Sort (cost=119.99…120.06 rows=28 width=41) (actual time=0.062…0.062 rows=1 loops=1)"
" Sort Key: document_date"
" Sort Method: quicksort Memory: 25kB"
" → Bitmap Heap Scan on acc_documents (cost=8.82…119.32 rows=28 width=41) (actual time=0.056…0.056 rows=1 loops=1)"
" Recheck Cond: ((document_number = 1210001) OR ((document_customer_acc_no)::text = ‘1210001’::text))"
" → BitmapOr (cost=8.82…8.82 rows=28 width=0) (actual time=0.037…0.037 rows=0 loops=1)"
" → Bitmap Index Scan on acc_documents2_document_number_ndx (cost=0.00…4.33 rows=1 width=0) (actual time=0.012…0.012 rows=1 loops=1)"
" Index Cond: (document_number = 1210001)"
" → Bitmap Index Scan on document_customer_account_number_ndx (cost=0.00…4.48 rows=27 width=0) (actual time=0.024…0.024 rows=0 loops=1)"
" Index Cond: ((document_customer_acc_no)::text = ‘1210001’::text)"
“Total runtime: 0.091 ms”

Without the Explain Analyse it runs in 17ms.

17

I’d be interested to see what results you get setting the JDBC driver to use the V2 protocol instead of the default V3. I think adding “?protocolVersion=2” to the URL string would do the trick.

As an aside, also note that Servoy isn’t shipping with the latest JDBC driver for the db versions you’re running. I assume you switch out the drivers accordingly.

18

david:
I’d be interested to see what results you get setting the JDBC driver to use the V2 protocol instead of the default V3. I think adding “?protocolVersion=2” to the URL string would do the trick.

I have been using the driver that ships with Servoy, I install each major version of Servoy into a new directory. So for 6.0.x, I have been using the driver that Servoy supplies.

I can confirm that setting ```
“?protocolVersion=2”

The average time goes from

00:00:012 2 00:00:006 Custom SELECT COALESCE(to_char(document_number,‘99999999’),‘n/a’) || ’ ’ || to_char(document_date,‘dd/MM/YY’) || ’ ’ || COALESCE(document_customer_acc_no,‘’) || ’ ’ || COALESCE(document_customer_name,‘’), document_id FROM acc_documents WHERE document_number = ?::integer OR document_customer_acc_no = ?::text ORDER BY document_date DESC OFFSET ?::integer LIMIT 26


for the typed query to

00:00:002 2 00:00:001 Custom SELECT COALESCE(to_char(document_number,‘99999999’),‘n/a’) || ’ ’ || to_char(document_date,‘dd/MM/YY’) || ’ ’ || COALESCE(document_customer_acc_no,‘’) || ’ ’ || COALESCE(document_customer_name,‘’), document_id FROM acc_documents WHERE document_number = ? OR document_customer_acc_no = ? ORDER BY document_date DESC OFFSET ? LIMIT 26


for the untyped.

C.
19

david:
I’d be interested to see what results you get setting the JDBC driver to use the V2 protocol instead of the default V3. I think adding “?protocolVersion=2” to the URL string would do the trick.

As an aside, also note that Servoy isn’t shipping with the latest JDBC driver for the db versions you’re running. I assume you switch out the drivers accordingly.

I install each new major release of in afresh folder, so I use whatever driver Servoy currently supply.

"?protocolVersion=2"

I can confirm that adding this makes the slowness go away for the untyped query. It executes in 0.001 Secs.

20

Good news for you – from 9.2 what’s new wiki:

Prepared statements used to be optimized once, without any knowledge of the parameters’ values. With 9.2, the planner will use specific plans regarding to the parameters sent (the query will be planned at execution), except if the query is executed several times and the planner decides that the generic plan is not too much more expensive than the specific plans.

You can actually test prepared statements in PGAdmin:

PREPARE test (text) AS
    select * from nut_data where nutr_no = $1;
explain analyze EXECUTE test('262');

I suspect that Servoy via the JDBC driver isn’t passing type at all:

PREPARE test AS
    select * from nut_data where nutr_no = $1;
explain analyze EXECUTE test('262');

Your cool trick does the same thing as the first block of code and I think is more along the lines of:

PREPARE test AS
    select * from nut_data where nutr_no = $1::text;
explain analyze EXECUTE test('262');