Very Slow Valuelist

Classic Servoy
1

very simple question …
servoy 3.1.6
sybase 7 and 9.1.2

I have 5 million records in a 2G db. The text column in question is Char (6), indexed, with 91 distinct values

If I create a valuelist on this column, the form locks up for 30 seconds on first view

in sybase central, I see that “select distinct” also takes over 30 seconds. Finds on specific values are instant

what is going on? Fortunately I need only one other value list, on a much smaller table, that is fast

thanks,
greg

2

Hi Greg,

To really know what is happening is to see how Sybase is planning the query. I.e. how it fetches the data. It sounds like it’s not using the index at all when it gets a distinct.
If my google-skills don’t deceive me you need to use the following command to show the query plan:

set showplan on;

And then run your query.
Paste the result back on the forum so we can look into it.
To be clear you do all this in isql.

Also have you tried a GROUP BY instead of a DISTINCT. I have had situations when a GROUP BY was faster than a DISTINCT.

Hope this helps.

3

dbisql shows Plan: Tablename (indexname)
so it seems to use the index

select distinct & group by are equally slow. Not sure which Servoy actually uses to get a valuelist

is this an expected SQL thing, or is something missing? I am SO spoiled by FileMaker, which protects me from this level of detail

thanks,
greg

4

Hi Greg,

Can you paste the whole output of the query plan?

5

select distinct’s plan is this:
( Plan [ Total Cost Estimate: 51139.95502 ]
( OrderedDistinct
( IndexScan Data network_code )

but, select using group by is this:
( Plan [ Total Cost Estimate: 219.148704 ]
( WorkTable
( HashGroupBy
( TableScan Data )

both are slow

I did find a post on dbforums that claims in order to use an index, you can not allow nulls. So, I turned “allow null” off, but no difference. Sybase online books offers this “Many statements take significantly longer to execute when DISTINCT is specified”

greg

6

Hi Greg,

Quite amazing that scanning an index takes this long. Have you tried to reindex the column?
Also another option would be to move the data out of that table and relate it back from a seperate table. This way you have a table with 91 values that are loaded up in no time.

Hope this helps.

7

as a quick fix, I did export the data into a separate table for the valuelist

but, is this SQL workaround required, or expected? has anyone successfully created a valuelist from a “large” table?

also, surprising that Servoy generates the valuelist when the form opens, so it locks up, rather than on combobox entry

greg

8

gdurniak:
as a quick fix, I did export the data into a separate table for the valuelist

but, is this SQL workaround required, or expected? has anyone successfully created a valuelist from a “large” table?

Well data-modeling wise it’s proper to put in a separate table and relate it (by ID). So the the answer to that would be Yes.

gdurniak:
also, surprising that Servoy generates the valuelist when the form opens, so it locks up, rather than on combobox entry

It does that because it also uses the valuelist for displaying the value. This is of course a requirement when you have a real and display value in your valuelist.

Hope this helps.

9

I did manage to confirm this on the sybase.public.sqlanywhere.general newsgroup. Select distinct or “group by” on 5 million records will be somewhat slow, so generating a valuelist directly is out of the question.

Sybase claims that SQL Anywhere 11.0 may have this feature

In the mean time, I will be forced to maintain & update a separate “valuelist” table. The is data sent to us, so we have no control

greg

10

A related question:
How can I see the actual valuelist SQL query that Servoy generates?

Is there a servoy query log, or would I need to turn on the Sybase server -z logging?

I don’t see it in the Servoy App Server Performance statistics …

thanks,
greg

11

Hi Greg,

gdurniak:
I don’t see it in the Servoy App Server Performance statistics …

They do show up for me though.
What version of Servoy are you using ? And also how do you load your valuelists?
By way of a method (application.setValueListItems()) or by letting Servoy fetch the data?

12

yes, I do see it now, when I open Servoy Client

it appears that the Server “Performance statistics” only shows activity from Servoy Client, not from Servoy Developer (which makes sense)

greg