tables w/o primary

Discuss all feature requests you have for a new Servoy versions here. Make sure to be clear about what you want, provide an example and indicate how important the feature is for you

tables w/o primary

Postby lesouef » Sat May 29, 2010 8:34 pm

There are situations where I have to work on tables with no primay key. I couls use the row_id instead, but this assumes to have a field which is unique, and this is not always true, especially in system whihc do not require this (db2 among others).
Would it be technically possible to use 2 fields concatenated to be used as a the unique ID: for instance an order_lines table with no order_line_id where the unicity can be done with the order_id + the order_line_rank_id (number of the order_line for the given order, reset at every order). simply concatenating them with an intermediate character should be enough to get a unique string.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: tables w/o primary

Postby ROCLASI » Sat May 29, 2010 8:41 pm

Hi lesouef,

You can assign the row-identifier to multiple columns in Servoy to make it a multi-key row-identifier.
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: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: tables w/o primary

Postby lesouef » Sat May 29, 2010 9:17 pm

good news: in which order does it concatenate the fields? for instance:
I may have an order_id "1000" with 15 lines 1,2,...15 and an order_id "10001" with 5 lines, 1,2,3,4,5
concatenating these 2 fields does not always result in a unique code as:
"1000" + "15" is the same as "10001" + "5"
this is why I was asking for an intermediate character like "_" to make sure it was unique; or does servoy has a way to distinguish such cases?
In the current table I am considering, I then need 4 fields to make it unique if the above example it not handled. Is it serious to use this on a 6M lines table?
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: tables w/o primary

Postby lesouef » Sun May 30, 2010 11:48 am

after double checking, I found out it works with 2 fields, so obviously they have a way to use 2 fields as a row_id other than by using a simple concatenation, but building the project now takes ages.
that's seems to be the driver. for some reason, issuing the same query via servoy and the java utility from IBM (this is db2) is far different:
1s for the java query tool from IBM and 1mn55s for the servoy find on a table where the answer is only 2000 lines or so. for the same demand of course. In fact, I have 2 ways to connect to this db, and the odbc gives better results than the jdbc
but the IBM query tool uses the jdbc! so I stole the ibm client access jt400 driver to put it in servoy, and though the connection gives the tables list instantly, the queries are slower than a snail, almost 2 mn for get the 1st line...
anyway, thanks for the row_id thing, this does the job, but obivously some drivers don't work well with servoy.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: tables w/o primary

Postby ROCLASI » Sun May 30, 2010 11:56 am

Hi Lesouef,

When using multi-key tables you don't concatenate the keys together. In SQL it simply uses all keys as a criteria, be it in the WHERE clause or in a JOIN. So no concat character is needed.
As for the speed difference, can you see what query Servoy produces when you do the same find you do in your IBM Java query tool?
Also can you see if the IBM tool sets some specific parameters on the JDBC connection ? If so you might want to duplicate those in Servoy.
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: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: tables w/o primary

Postby lesouef » Sun May 30, 2010 12:46 pm

I checked the servoy query and simply pasted it in the IBM tool, so no difference here.
as for the ibm tool param, they have nothing special except: immediate validation, read-ahead on, compression on and a few format options as usual. putting these off does not influence results on simple queries which executes < 1s. and how could I guess how to specify this as a URL?

I still think the row_id is the problem as using the odbc driver instead of the jdbc which gives good results using sql queries from within servoy > dataset is making the same problem if I want to link this table to a form. so the jdbc driver seems to be misleading. something goes wrong when I link this db2 table to a form, and I don't have this problem on tables where the row_id is based on a single field.

do you know for instance what would happen if the row_id or row_ids (if several) would not turn out to be a unique record? would it be slow or just display the wrong record? to me, it should return several records, but not slow down, same as when you forget to use "distinct"...
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: tables w/o primary

Postby lesouef » Sun May 30, 2010 1:40 pm

I think I found what was wrong: the default query issued by servoy when loading the foundset is sorting by all row_id fields, and this very slow since the whole table has to be sorted before showing the first 200 recs: in my case, the table is > 6M records...
If the 1st field to be sorted is indexed properly, that works ok, but if the 1st field is always the same value (so badly indexed), the result is catastrophic as servoy generates its query by sorting according to the row_ids alphabetically. In the current situation, the first field is the order line rank, and all orders have a 1st order line, so something like 5M lines which '1' have to be sorted before showing the 1st 200! If I just invert the fields order, that goes down to 2s!
Ideally, I'd like to set the default query for the form load myself. I'll check if the default sort order of the form would have influence on this automatically generated query, and this seems to be ok as long as I don't use anything else that the row_ids. big tables is a specific sport.... more on this when this runs at proper speed.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: tables w/o primary

Postby lesouef » Sun May 30, 2010 3:14 pm

and that was it. it now pulls anything in seconds.
and the final fix is to define the default form order with row_ident fields only and in the right order to use db indexing the right way and prevent servoy to place them alphabetically in its query. in my case, row_idents were called sklig and sknum, but they must be sorted sknum first and sklig second. and the effect is from 3mn30s to 1 single second! Of course, further finds and queries are not guaranteed to be the same speed depending on the fields to be used, but then this is a db indexing problem only, not on servoy side...
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France


Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 1 guest