Hi to all,

We experience some problems by some of our customers with the use of ElevateDB 2.06 Build 1.

Scenario:
- Customer(s) have a Windows SBS2003/2008/2011 server(s), with ElevateDB installed as a Service. (logon as local system account)
- On the same machine there is an catalog/database installed. (which can be seen/viewed/opened via the EdbMgr.exe tool)
- We have an own created (client) import tool (called: PABImporter) which is connecting to the above described ElevateDB Server.
- The connection from the client to the database server is made by setting the following settings:
* Session.SessionType := stRemote;
* Session.RemoteHost := '192.168.1.2';
* Session.RemoteTimeout := 180;

Our import tool is importing some 'large' (the file size is varying from 10MB to 1600MB!) ASCII files into the database/tables, this is accomplished via an DDL/SQL-statement called like this "'IMPORT TABLE %s FROM "%s" IN STORE importfiles (%s) DELIMITER CHAR #9 QUOTE CHAR #14 DATE FORMAT ''yyyy-mm-dd'' ';".

In the past we almost have never problems with importing this kind of files, but now a days we experience on a frequently base the follow ElevateDB error: ":ElevateDB Error #1101 The connection to the server at '192.168.1.2' has been lost ('Timed out while receiving stream')".

When we received the error message for the first time (a couple of months ago), from our customers we made an adjustment to our import tool by supporting the Session.RemoteTimeout setting. We can modify this value via an .ini file. the default value from ElevateDB is 180seconds.

With some of our customers we adjust it to 360 and the import ran without any problem(s), so we thought we have tackled the problem but unfortunately i have experienced the same problem again, but now i have set the timeout to 600seconds, 10 minutes!!! but without any luck  The ElevateDB error is mostly raised during the import of the ~ 1500MB ASCII file.

Can you explain some more (in detail?) about the above error message, i think i don't really understand why or what is causing that problem.
Also if anyone have some pointers, or things to try/checkout for fixing the above problem im glad to hear them.

I'm almost reached a state/point which is called... i'm 'desperate'

If you need some more 'detailed' info just ask, i'm gladly willing to supply the missing info.

So concrete and/or shortly put together:
Using Elevate 206 on a server, client is also running local on that server, connected via the 'remote' type. importing a bunch of large ascii files via the IMPORT TABLE statement is causing an ":ElevateDB Error #1101 The connection to the server at '192.168.1.2' has been lost ('Timed out while receiving stream')" error.



Thanks in advance for looking at this problem.


With kind regards,

Richard Lichtendahl
ENK Software BV - The Netherlands, Europe (+1 GMT)

Richard,

I have had a similar error message, in a similar situation. The problem is not desperate for me as I rarely transfer such large files. I also increase the Timeout value, and this helps.

Honestly multi-gigabyte files are fairly hard for Windows to handle well ... even if EDB was totally fool-proof. Over my own internal network copying such files would fail from time to time.

* Newer versions of Windows server handle these larger files better. You mention Win 2003 as 1 server ... have you checked whether your problems are concentrated on that build?

* The most recent version of EDB is 2.07 ... that might include improvements ... its worth trying.

* Is there any way at all that you can segment your imported files to make them smaller? My guess would be that anything in the range of 100 - 500 megabytes would probably be handled smoothly ... if you could ensure files were in that size range you would probably be OK.

Adam

Adam,

First of all, thank you for your reply.


Multi-gigabyte files are hard to handle, i know. but mostly in our cases we are talking about one 800MB...1.5GB file and the other files are much smaller. The customers have a varying range of servers, from SBS2003 to SBS2011. When the timeout problems occur it looks that this is mostly happing on the 2008/2011 variant. But, so now and there it also occurs on a SBS2003 machine.

Good point about the ElevateDB 2.07, I'll hope that Tim can shine a light (give his opinion) on this point. It's not so easy for us to upgrade the ElevateDB version, we need to rebuild our framework and core applications with the new version. And we also need to deploy/upgrade the customer(s) with our new software before we can "try" this (very time consuming path). When the new version does not offer the solution for our problem we have to rollback everything. which is also a very time consuming path, and a lot of downtime for our customer. So if there is no need to upgrade we would like to save this point for the last.

Segmented files.... mm m this sounds like a plausible solution.
Here again... Other input (Tim?) wanted, can segmented files be the resolution for the timeout problem? i really don't know, because i hardly understand what the real problem is here (and what is causing it), also what the meaning is about the Remote-timeout setting (what does this setting exactly?).





Thanks in advance,

Richard Lichtendahl
ENK Software BV - The Netherlands, Europe (+1 GMT)

Richard


I don't use C/S but a few thoughts occur. Is the STORE on the server machine or the client. If the latter, since its an intermittent fault, it could be related to network traffic. You don't say what speed the LAN is your customers are using but transferring a 1.5GB file could well take over 10 minutes on a 100Mb LAN with a reasonable degree of traffic.

Roy Lambert

Hi Roy,

The ElevateDB Server (Service) and Database are located at an server, the client is also running on that same machine.


With kind regards,

Richard Lichtendahl
ENK Software BV - The Netherlands, Europe (+1 GMT)

Richard

If they're both on the same physical machine why not just use fileserver for the import process?


Roy Lambert

Hi Roy,

What do you mean with "why not use fileserver"?  is this an feature from Elevate? How does it work?

An other reason of "why" is, the end-user can make the decision that he/she wants to run the import tool from an work-station, thats why the tool is build with the Client/Server principle.
(We all know and be aware that such import's from an workstation is WAY inefficient, performance drop at the maximum so we almost never give this advice).


With kind regards,

Richard Lichtendahl
ENK Software BV - The Netherlands, Europe (+1 GMT)

>>It's not so easy for us to upgrade the ElevateDB version, we need to rebuild our framework and core applications >>with the new version. And we also need to deploy/upgrade the customer(s) with our new software before we can >>"try" this (very time consuming path). When the new version does not offer the solution for our problem we have to >>rollback everything. which is also a very time consuming path, and a lot of downtime for our customer.

I feel your pain ... upgrades are a pain!

Honestly I think it is doubtful that the switch between 2.06 and 2.07 will make too much of a difference (unless Tim says otherwise)

>>Segmented files.... mm m this sounds like a plausible solution.

I assume that your gigabyte file is XML or CSV. If this is the case it would surely be possible to generate it instead as a series of files, of a certain maximum size for each, based on something fairly simple to start with such as a certain maximum number of table-rows on the EXPORT side.

So long as your import process then iterated through these files in order you would be OK.

Note that you can use EDB's SET FILE STORE command and then

SELECT Name, CreatedOn FROM Information.Files ORDER BY CreatedOn

to generate a CURSOR to a list of IMPORT files in the store ... so it can all be managed by a JOB.

Richard


FileServer is just standard ElevateDB - its how I, and quite a few others, work. Rather than connecting to a server the engine just talks to the database. You can keep the server running as well because ElevateDB, like DBISAM before it, will happily work with both approaches.

The main difference is using a local rather than a remote session and its just a few settings on te engine/session component. The rest shoul be transparent.

Wether or not it will make any difference I don't know.

Roy Lambert

Richard,

If you can use the local session like Roy suggested it should really help - it really is very straightforward to switch between local and remote session so you can have both options available for users.

The local session does not have timeout problems since we're accessing tables direct.

I did some very basic benchmarking while ago internally (for loading a CSV file) and local session in our case performed about 2-3 times faster (this of course is dependent on many factors so you should run own comparison for your scenario). If you can open tables in exclusive mode for the load it's even faster.

Raul


<<
Roy Lambert wrote:

FileServer is just standard ElevateDB - its how I, and quite a few others, work. Rather than connecting to a server the engine just talks to the database. You can keep the server running as well because ElevateDB, like DBISAM before it, will happily work with both approaches.

Wether or not it will make any difference I don't know.

>>