Exporting Large Record Sets?

[bubba]

Hi Folks:

I’m working in 2.2 and attempting to use as much of the “Built-In” functionality as possible, as this is what the majority of our end-users will have at their disposal if we continue down the road with Servoy.

We work with very transient and varied data and the(Mid level) End users will need to import and export large data sets themselves with minimal technical assistance. They do this just fine with FM7 now.

I have had fairly good luck wiht Imports (aside from some Stored Calculation Issues) and the speed is quite good.

However, Exports are proving to be quite horrid. I have a database with about 400,000 records in it. I am trying to export a set of about 5 records as text into Filemaker for some quick cross referencing with a Filemaker database. The export wizard gets to the step just before you are allowed to specify the File name for the export and goes off to “download” the records. In my case, I let it go for about 2 hours and it never got to the next step. Small sets do not seem to be a problem.

I have few issues with this:
1.) It is slow or doesn’t work.
2.) It is “buffering” the records in the client for the export which is completely unnecessary.
2a). I believe this buffering process is what is breaking the process and making it so slow.

I would like to see the “built-in” tools improved (hopefully soon), but in the mean time, is there a good way around this within Servoy, or are external tools the only way to go?

Thanks,
Lee Snover

[bubba]

Sorry,

Notice a funny typo. The export is about 5 FIELDS, with all 400,000 records. 5 Records doesn’t seem to be much of a problem.

Hopefully that corrects the context and the wonderful responses will all flood in now.

Regards,
Lee Snover

[Jan_Aleman]

Servoy’s current export is not suitable to export 400,000 records. To do that use the database tools of the database you use. In a three tier architecture it is simply not a good plan to export large amounts of data on the client going through all layers. We are looking into export support on the server, however this is not easy with all the different sql databases out there. Nevertheless until we get there: it’s fairly easy to write sql scripts for the database of your choice to export 400,000 rows.

Whilst we are on this topic: can you share a real life example on how this should ideally work on Servoy and why you can’t do it on the database side?

[bubba]

jaleman:
Servoy’s current export is not suitable to export 400,000 records. To do that use the database tools of the database you use. In a three tier architecture it is simply not a good plan to export large amounts of data on the client going through all layers. We are looking into export support on the server, however this is not easy with all the different sql databases out there. Nevertheless until we get there: it’s fairly easy to write sql scripts for the database of your choice to export 400,000 rows.

Whilst we are on this topic: can you share a real life example on how this should ideally work on Servoy and why you can’t do it on the database side?

Jan:

My job is to provide good tools for my end-users. My environment and end-users are more dynamic and varied then most. I have a “multi-tiered” user environment, you might say. The “middle-level” users are quite sophisticated and get around in Filemaker quite well. We deal with a wide variety of data that comes and goes just about daily. Most sets of data are relatively small, but we have some large ones too. We do a great deal of “on the fly” analysis of this data, because it is so varied. The “middle-level” users are not programmers, but they use and undertand the data in very sophisticated ways. They need to be able to do fast summarization and reporting on the data very quickly.

Filemaker has served them very well in most cases, but the sets are getting larger, and the program is requireing more checks and balances, so speed and reliability are getting to be an issue. The attractiveness of Servoy is the “blending” of the FM interface and “feeling” with a more sturdy database and a better delivery mechanism for wide area networked users. While the tools for the programmer (me) are very important and compelling, the tools for the end user must also be. They must be able to move data around at will, and be able to quickly assemble and change reports.

The import features in servoy work pretty well, but the export has been disappointing. I’ve worked on other “three tier” environments, so I understand the challenges. No, moving data through Servoy is not the most efficient, but, if I choose to implement Servoy, it will be the primary and most sophisticated tools my middle and low level users have access to. If it can not handle the basic functions, it will be a hard sell to the users. The middle-level users do not want to come running to me to import and export their data, nor should they have to.

I could certainly write some routines to handle this process in Servoy myself, perhaps with some plug-ins. But, the point is, that this should be BASIC functionality in my opinion. The bar has been set in some ways by Filemaker. I understand that Servoy IS NOT Filemaker, but IMHO, that just means that it should be SUPERIOR in every possible way.

In the case of exports, I believe the problem is more with how the export function has been implemented then the fact that it’s a “three-tier” architecture. Buffering small sets of data in the Client may make sense, but with large exports, the Client should only act as a conduit for the records as they are written out to a local file. The fact that the Client is attempting to buffer the entire data stream is where the problem lies. I can accept a speed decrease as a price to pay for using Servoy with a large data set. I can not accept the product/function not working at all.

I’m sure it’s just a matter of priorities, and perhaps for the bulk of your users, this is not a problem. Even for me it’s an occassional problem ( a few times a month), not a daily one. It is important though, and I do not think that you should artificially limit the capabilities of your program because there are other ways to solve the problem. You are competing with the Filemakers and Omnis type products of the world, and they can handle this problem relatively well.

I’d be happy to discuss in more detail. It’s difficult to try to explain in a short time frame typing. Let’s just say I have to work in a very challenging environment. Our “product” has to suit a wide variety of both internal and external users, and both are equally important. It’s a challenge at every level of the program, including the levels that I have to implement. The needs and goals of the different users are often quite different, almost opposing at times. It makes for some very stressfull days at the office.

Regards,
Lee Snover

[Harjo]

Hi Lee,

try this:

var maxReturnedRows = 400000;
var query = 'select * from yourtable'
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
var csv = dataset.getAsText(',','\n','"',true)

//Show a save open dialog and retrieve the file
var file = plugins.file.showFileSaveDialog('export.csv');
var success = plugins.file.writeTXTFile(file,csv);

In my case it exported 400.000 records in just 30 seconds.
be carefull though, it can cause outofmemory errors.

So Servoy-guys, maybe this can be optimized? So we can work with this?
Maybe it is possible to stream the data, right away to a file, without holding it in memory.

[ebrandt]

Would it be possible to trigger the execution of a SQL DTS package after creating the text file?

If this could be accomplished:

1. I could create a dynamic search method, providing end users the ability to add parameters to the query.

2. Perform the query, export the text file, execute the DTS package import. (one method)

This would solve an issue I have been working through exporting large data sets.

[Jan_Aleman]

ebrandt:
Would it be possible to trigger the execution of a SQL DTS package after creating the text file?

If this could be accomplished:

1. I could create a dynamic search method, providing end users the ability to add parameters to the query.

2. Perform the query, export the text file, execute the DTS package import. (one method)

This would solve an issue I have been working through exporting large data sets.

as you can trigger dts by a stored proc that sound doable to me.

[Jan_Aleman]

leesnover:
I could certainly write some routines to handle this process in Servoy myself, perhaps with some plug-ins. But, the point is, that this should be BASIC functionality in my opinion. The bar has been set in some ways by Filemaker. I understand that Servoy IS NOT Filemaker, but IMHO, that just means that it should be SUPERIOR in every possible way.

It is certainly not our goal to be superior in every possible way to every product out there. Servoy’s specifications are not lead by what other applications CAN do but what our customers want to do with it. So far bulk export of large sets of data has not been high on that list. Nevertheless there are a couple of solutions if you don’t feel like coding your own routines:

• Hire a java developer to code the classes for you, shouldn’t be very difficult
• Join the ‘sponsor a feature’ program where you contribute to the costs of Servoy implementing this feature for you
• Start an opensource project and make other developers enthousiastic to do it (http://www.servoyforge.net)
  -or- search the internet for a set of java classes that can do jdbc–>csv and post them here, then our engineers will have a look to see if they can make that into a plugin.

[bubba]

jaleman:

leesnover:
I could certainly write some routines to handle this process in Servoy myself, perhaps with some plug-ins. But, the point is, that this should be BASIC functionality in my opinion. The bar has been set in some ways by Filemaker. I understand that Servoy IS NOT Filemaker, but IMHO, that just means that it should be SUPERIOR in every possible way.

It is certainly not our goal to be superior in every possible way to every product out there. Servoy’s specifications are not lead by what other applications CAN do but what our customers want to do with it. So far bulk export of large sets of data has not been high on that list. Nevertheless there are a couple of solutions if you don’t feel like coding your own routines:

• Hire a java developer to code the classes for you, shouldn’t be very difficult
• Join the ‘sponsor a feature’ program where you contribute to the costs of Servoy implementing this feature for you
• Start an opensource project and make other developers enthousiastic to do it (http://www.servoyforge.net)
  -or- search the internet for a set of java classes that can do jdbc–>csv and post them here, then our engineers will have a look to see if they can make that into a plugin.

Ahh, come on Jan, you can do better then that. Basic import and export tools should be standard on any product of this caliber, no matter who you list as the competition. You really should not have to go outside the product to accomplish this. Of course that’s just my opinion.

I’ve got to get past the basics first before I can advocate “sponsoring” new features. I would probably sponser “Multiple windows” when we get to that point. I am trying to setup a “test” server for a couple of users for the first time this week. Wish me luck. Import and Export performance for large sets has been one of the sticking points for FM7. I really hope we can do a better job in Servoy without resorting to having to higher an new programmer.

Thank you for the suggestions. My suggestion in return would be to fix Servoy to work properly in this instance. The feature, as implemented does not work. Otherwise, put a message up that says, “sorry, Servoy only supports exporting found sets smaller then X”, so the poor end user does not get frustrated trying to make the program do something it simply won’t do.

I’m not trying to be a smart ass or negative. I just think the basics do matter and should not be dismissed out of hand.

Thank you for your consideration!

Sincerely,
Lee

[Harry_Catharell]

Hi Lee,

did you try the advice given in the post by Harjo ?

try this:
Code:
var maxReturnedRows = 400000;
var query = ‘select * from yourtable’
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
var csv = dataset.getAsText(‘,’,‘\n’,‘"’,true)

//Show a save open dialog and retrieve the file
var file = plugins.file.showFileSaveDialog(‘export.csv’);
var success = plugins.file.writeTXTFile(file,csv);

In my case it exported 400.000 records in just 30 seconds.
be carefull though, it can cause outofmemory errors.

Interested to know whether this would suit in the interim ?

Cheers
Harry

[bubba]

Harry Catharell:
Hi Lee,

did you try the advice given in the post by Harjo ?

try this:
Code:
var maxReturnedRows = 400000;
var query = ‘select * from yourtable’
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
var csv = dataset.getAsText(‘,’,‘\n’,‘"’,true)

//Show a save open dialog and retrieve the file
var file = plugins.file.showFileSaveDialog(‘export.csv’);
var success = plugins.file.writeTXTFile(file,csv);

In my case it exported 400.000 records in just 30 seconds.
be carefull though, it can cause outofmemory errors.

Interested to know whether this would suit in the interim ?

Cheers
Harry

Harry:

Not yet, been working on getting server ready, and trying to track down some problems in FM7. Very hectic.

I am trying to get things in place to do some evaluations with a couple clients. Setting upa W2K Server from scratch is a good bit of work once you are done with all the patches, anti-virus, Oracle drivers, etc.

Regards,
Lee

[IT2Be]

Off course Harjo’s (HJK) solution will do when you want to export the result’s of a query ‘just like that’. I export data with related data and formatted data in it.

For that I create an array of data with custom headers (e.g. understandable to the enduser) and write them as a CSV file or XML file. I then hand the data over to a plugin that writes the file as an official ‘excel csv’ file or a xml file.

It would be possible to make this a publicly available plugin but… This time it is not going to be for free… So, if anybody is interested throw me an email/private message and make me an offer I can’ refuse

[Jan_Aleman]

leesnover:
I’m not trying to be a smart ass or negative. I just think the basics do matter and should not be dismissed out of hand.

a. I have not said that we are not going to support exports of high volumes of data and
b. I’m not sure why you consider exporting 400,000 rows as the ‘basics’ of a product. It is not the basics of Servoy. The concept of Servoy’s export was to move small sets of data in and out and not to bulk move data around. This does however not mean that we are not prepared to look into it.

[bubba]

jaleman:

leesnover:
I’m not trying to be a smart ass or negative. I just think the basics do matter and should not be dismissed out of hand.

a. I have not said that we are not going to support exports of high volumes of data and
b. I’m not sure why you consider exporting 400,000 rows as the ‘basics’ of a product. It is not the basics of Servoy. The concept of Servoy’s export was to move small sets of data in and out and not to bulk move data around. This does however not mean that we are not prepared to look into it.

Jan:

Don’t mind my rantings. I’m bordering on insane from trying to do too many things at once, and tend to fixate on certain pet peeves. Keep up the good work and I’ll try to tone it down until I can place my first big order!

Cheers!
Lee Snover