SQLStringConnect slow 2

[florindaniel]

Hello,

I need some advice, please:
I have an SQL Server based application, a point-of-sale, written in VFP; the connection is
established via Internet, using a VPN.

Each time there's a sale, I connect to the SQL server and after the bill is finished I disconnect.
It works fine with the exception of the connection phase being slow, some 1-1.5 secs wich is annoying.

Do you think it would be better to grab a connection handler in the morning and use it all day?
Does it work like that or there's some maximum lifetime for a connection?
How do you think I should manage this issue?

Thank you,
Daniel

 

[Mike Lewis]

Do you think it would be better to grab a connection handler in the morning and use it all day?

Essentially, it's a trade-off. On the one hand, keeping the connection open all day will save the overhead of constantly connecting and disconnecting. On the other hand, SQL Server generally imposes a restriction on the number of open connections. It's possible to configure this number quite high, but that in turn might mean trade-offs somewhere else.

My personal approach is to grab the connection at the start of the session, and then to keep it open. But I also set a time-out, so that if there is no activity for so-many minutes, it automatically disconnects.

I can't say for sure if this would be the best approach for you, but I suggest you try it and see what difference it makes. You should try first simply to keep the connection open the whole time. If that gives a worthwhile improvement in performance (and I suspect it will), then you can think about the time-out at that stage.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

As the connection is made via VPN, I'd also add a check the validity of the handle.

TRY
   llHandleValid = (ASQLHANDLES(laCurrentHandles, nHandleToCheck)>0)
CATCH TO oEx WHEN oEx.ErrorNo = 1466
   llHandleValid = .F.
FINALLY
   Release laCurrentHandles
ENDTRY

Also see thread184-1685241.

Bye, Olaf.

 

[craigber]

You've discovered why this type of connection is rarely done. The solution is to buffer data locally, then push it to the server in the background or during off-hours, say at night.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

The other solution is to do a remote session via VPN, so the database connection is not via VPN, but still a more stable LAN connection.
But this takes lot of RAM to enable enough user sessions with each large enough amount of session RAM and the corresponding number of client licenses.

But not for a POS system, that should enable fast customer servicing.

If your solution is still viable and stable enough with handle checking depends. Each transaction can be very short and small and what you have in the central server is at the central server. It just doesn't scale well for many shop locations. You have to use a disconnected solution with distributed data synced to a central server at some point of growth, if you want a fast local system.

At least think about separating the data you really need right away in the central database and data, that can wait for a nightly synchronization, eg protocol data.

Bye, Olaf.

 

[florindaniel]

Thank you all,

Olaf, that's what I'm doing right now, I mean: minimize the amount of data transferred.
I also keep a local table for the transactions and while the printer prints-out the bill
I upload on the server all the failed transactions.

It works fine, I still need to implement a trigger on the server to manage some
"last_update" timestamp for each table (SQL Server does not have a native support for that )
in order to optimise product table download from the server.

I now face another VPN problem: it disconnects every 5 minutes (( but it seems to be a
Windows Server issue.

Thank you again,
Daniel

 

[Mike Lewis]

I still need to implement a trigger on the server to manage some
"last_update" timestamp for each table (SQL Server does not have a native support for that )
in order to optimise product table download from the server.

Well, you could add a timestamp field to the table. SQL Server will then automatically update it on each UPDATE or INSERT. That will give you a database-wide unique number for each row. But it won't tell you the actual date or time of the update.

The other option is to create a new datetime (or, better, smalldatetime) field, and to update it with the current date and time from your trigger. That would be quite easy to do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, the timestamp field is automatically set and updated, you can't set a "default value or binding" for it, but it's not really a timestamp literally, it's just a binaray value turning out as an incrementing counter.

But you don't need to know the time, you can also take the value as reference of what you already synced and what changes need to sync with any higher timestamp than the last one at the begin of the last synchronization.

You might orientate with the discussion we had lately about a similar problem: thread184-1740547

Bye, Olaf.