# Query slower in Servoy than PGAdmin. Why?

**URL:** <https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419>\
**Category:** Classic Servoy\
**Created:** [September 3, 2012, 2:34pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419 "2012-09-03T14:34:27Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 3, 2012, 2:34pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/1 "2012-09-03T14:34:27Z")

</div>

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.

```auto
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?

```auto
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

```

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [September 3, 2012, 7:57pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/2 "2012-09-03T19:57:30Z")

</div>

Hi Christian,

which versions are we talking here?

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 3, 2012, 9:19pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/3 "2012-09-03T21:19:47Z")

</div>

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:

```auto
[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?

---

<div class="post-metadata">

**Author:** ![lwjwillemsen](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lwjwillemsen/32/4987_2.png) [@lwjwillemsen](https://forum.servoy.com/u/lwjwillemsen)\
**Post date:** [September 3, 2012, 10:10pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/4 "2012-09-03T22:10:35Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [September 3, 2012, 10:32pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/5 "2012-09-03T22:32:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 4, 2012, 6:31am UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/6 "2012-09-04T06:31:06Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 4, 2012, 10:40am UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/7 "2012-09-04T10:40:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 4, 2012, 10:41am UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/8 "2012-09-04T10:41:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 4, 2012, 12:45pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/9 "2012-09-04T12:45:03Z")

</div>

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:

```auto
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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![lwjwillemsen](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lwjwillemsen/32/4987_2.png) [@lwjwillemsen](https://forum.servoy.com/u/lwjwillemsen)\
**Post date:** [September 4, 2012, 5:26pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/10 "2012-09-04T17:26:31Z")

</div>

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

Maybe a comment of our Postgresql guru Robert ?

Regards,

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 4, 2012, 7:55pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/11 "2012-09-04T19:55:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 4, 2012, 8:36pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/12 "2012-09-04T20:36:57Z")

</div>

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](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…

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 4, 2012, 8:41pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/13 "2012-09-04T20:41:50Z")

</div>

> 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.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 4, 2012, 8:53pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/14 "2012-09-04T20:53:00Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 5, 2012, 10:47am UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/15 "2012-09-05T10:47:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 5, 2012, 12:49pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/16 "2012-09-05T12:49:27Z")

</div>

```auto
 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.

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [September 5, 2012, 1:39pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/17 "2012-09-05T13:39:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 6, 2012, 12:21pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/18 "2012-09-06T12:21:38Z")

</div>

> 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”

```cures
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

```auto

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

```auto

for the untyped.

C.
```

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [September 6, 2012, 12:42pm UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/19 "2012-09-06T12:42:36Z")

</div>

> 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.

```auto
"?protocolVersion=2" 

```

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

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [September 7, 2012, 1:46am UTC](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419/20 "2012-09-07T01:46:04Z")

</div>

Good news for you – from [9.2 what’s new wiki](http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#Performance_improvements):

> 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:

```auto
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:

```auto
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:

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

```

[Next page](https://forum.servoy.com/t/query-slower-in-servoy-than-pgadmin-why/16419.md?page=2)