Refresh Records issue!

Hi,
I have a client (my biggest!) that updates the Sybase 10 back-end directly with customer details & orders from their web-site, as well as the db being used internally by Servoy.
When I first created the system (from a FileMaker 6 system), they weren’t as large & their volume of data wasn’t too big.
That was years ago and now they have grown considerably & so has the volume of data.
I had set the solution up so that when they went to view customers or orders, I did this call before showing records in a table view to make sure they are always seeing all the data…

//flushes the client data cache for all records in the current foundset 
databaseManager.refreshRecordFromDatabase(foundset,-1);

This is now a VEEEERRRRRYYY SLOOOOOOOOOOWWWWWW (& expensive!) operation & the client is thinking about replacing Servoy as the front end :shock:

My question is,
what is the quickest way for me to display ‘fresh’ data to the user in some kind of table view and allow them to click on something to take them to a record view?
I have not created HTML table forms with clickable links before, but thought they might be a solution along with a ‘rawSQL’ or ‘getDataSetByQuery’ call…
Any help would be greatly appreciated (will be at client tomorrow…).

Thanks,
Rafi

I don’t understand Rafi.

Isn’t that why we have databroadcasting?
When that does not work there is something in your code I guess…

IT2Be:
I don’t understand Rafi.

Isn’t that why we have databroadcasting?
When that does not work there is something in your code I guess…

Hi Marcel,
Databroadcasting isn’t any good as records are being added directly into the Sybase db back-end from an external source, not thru Servoy, so Servoy Application Server doesn’t know about them, which is why I have to do the 'refreshRecords.

Thanks.

Hi Rafi,

How big is the dataset you normally show to the users?

ROCLASI:
How big is the dataset you normally show to the users?

Hi,
it varies.

Now that I am at client, we are re-thinking how we work with the data & if we can make the refresh a manual process for when they need it…

Thanks

I suggest you have a look at the performance data page and look for the query generated by the refresh records command. Maybe you have a simple indexing problem? We use databases with loads of data and do not have that problem.

How often are records inserted by the website?
Maybe you can let the website make a call to a headless client that flushes the cache whenever a record is inserted. That way it is only slow when the data actually changed and not every time you show the form.

I don’t understand this being slow. If the form is shown, it has to fire the same query that is fired when you refresh it. So either it is always slow or something is strange here, no?

BTW: what is slow? How many records does that table have?

Wow!
The Servoy community really is so great!
Thanks for all the responses!
Let me try and answer each one…

patrick:
I don’t understand this being slow. If the form is shown, it has to fire the same query that is fired when you refresh it. So either it is always slow or something is strange here, no?
BTW: what is slow? How many records does that table have?

There are two main list views
contacts &
orders (which is linked to line_items of the order)

Here are the row counts (as of a day ago)
############################################
Table: contacts
Rows in table contacts: 504160
############################################
Table: line_items
Rows in table line_items: 3906250
############################################
Table: orders
Rows in table orders: 837971
############################################

I think the ‘slowness’ is due to Servoy having to throw away any data it has cached and then refill it freshly from the back-end.
I know it only loads records up in groups of 200, but we are talking about a difference of a 10-20 seconds (sometimes a lot longer, it seems to be going quicker today :? ) & 1 second when I’m not doing refresh!

joas:
How often are records inserted by the website?
Maybe you can let the website make a call to a headless client that flushes the cache whenever a record is inserted. That way it is only slow when the data actually changed and not every time you show the form.

Data is sent in to back-end every 10 minutes.
Doing it thru headless client might be an idea, I will have a look into it…

patrick:
I suggest you have a look at the performance data page and look for the query generated by the refresh records command. Maybe you have a simple indexing problem? We use databases with loads of data and do not have that problem.

I have been and there seem to be queries fired that I’m not sure why, so I’m looking into it more.
I’m also in the process of moving the Servoy system from one server to another & will be doing a DB-UNLOAD & RELOAD, so I’m looking into how I can optimise the data structure more (column order, indexes etc.) as well at the moment.

I really appreciate everyones help, thanks.

If anyone has a quick and simple way of displaying an HTML ‘table’ with a ‘link’ to click to move to a normal Servoy form to substitute for the standard Servoy ‘Table’ view that I could load with a SQL query instead, I’d be interested to see it (see attached screen show of my simple form)

Thanks.

Ooops
Should have scaled image…
Sorry.

I don’t see the point in playing with HTML. You have to load the data and that’s what appears to take some time. So what difference should it make if you fire the query in a getDataSetByQuery or have Servoy do it when loading a related foundset.

I suppose the foreign keys in orders and line_items are indexed?

patrick:
I don’t see the point in playing with HTML. You have to load the data and that’s what appears to take some time. So what difference should it make if you fire the query in a getDataSetByQuery or have Servoy do it when loading a related foundset.

True, I guess it was just to see if it might help or improve the UI…

I suppose the foreign keys in orders and line_items are indexed?

Yes, thanks, they are.

I have now moved the ‘refreshRecords…(-1)’ to a button so that they know they are going to have to wait for the updated data to come in, rather than forcing it to happen every-time they viewed the table view.
They haven’t complained yet, so fingers crossed…

Thanks again to everyone for your suggestions, they were very helpful.