Getting related record from 10million record table

Classic Servoy
1

I’m having huge performance issues trying to show related records in a table which has nearly 10 million records.
We are using the tablebean which shows chunks of 200 records at a time, no problem here.
I have another form using the same foundset as used in the tablebean.
When clicked on a record in the tablebean the other form is coming as a popup, the problem is that this takes a long time to show the record.
I have tried using relations, set index and find.
Just searching for the record is fastest (although this takes way too long).
Right now i’m kinda running out of ideas, I tried to use rawSQL, but i cannot find a way to load the result in my foundset?

2

Can you post the SQL queries generated by Servoy when you load the related records? You can retrieve it from the application server admin pages/ Performance Data.
It would be a good starting point. You can also enable full tracing there if needed for debugging.

3

which tablebean are you using?

4

ngervasi:
Can you post the SQL queries generated by Servoy when you load the related records? You can retrieve it from the application server admin pages/ Performance Data.
It would be a good starting point. You can also enable full tracing there if needed for debugging.

Here is the query servoy generated

Harjo:
which tablebean are you using?

This one: http://www.servoy-plugins.de/beans/tabl … -bean.html

5

17 seconds (on average) to fetch 61 records ? Ouch.
What datatype is kenmerk_code and do you have an index on this column ? Also what SQL vendor do you use ? Sybase ? PostgreSQL ?

6

ROCLASI:
17 seconds (on average) to fetch 61 records ? Ouch.
What datatype is kenmerk_code and do you have an index on this column ? Also what SQL vendor do you use ? Sybase ? PostgreSQL ?

It’s a varchar and it is indexable, database is MsSql 2005.

7

‘Indexable’…sounds like a FileMaker term…
You mean the column has an index. Right ?
You can check if it’s actually using that index or not by typing ‘SET SHOWPLAN_TEXT ON’ right before you send your SELECT TOP 61… query in MSSQL’s Query Analyzer.
Anyway, sounds like you need to do some tuning on that server. I am not that familiar with MSSQL tuning so perhaps someone else who knows MSSQL more intimate can chime in.