topic-33014

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

ADO SQL slow after idle for a while
Posted: Fri Sep 30, 2016 05:07 PM

Hi,

I'm using FWH ADO on MariaDB.

Everything is working fine.
Only one client have a problem on 2 PC's

The program is a cash desk.
If the products are scanned, everything is fast.
If there is no client for 5 minutes, I take 8 seconds to show the product on the screen after scanning.
Then everything is working fast again.

First I thought that it was the server that when to sleep-mode, but when testing it with an other (older) PC, it keeps being fast.

Does anyone know what the problem can be.
The PC's are running Windows 10. The server is a Synology NAS with MariaDB on it.

Thanks

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: ADO SQL slow after idle for a while
Posted: Thu Nov 03, 2016 09:30 AM

Hi,

Does anyone else have also this problem?
I've been testing it and have change some settings in the database-server, but no result.

The delay is not very long (0.8s), but the problem is that it is used in a shop with a barcodescanner, and then it is long if they have to wait

I run a simple query

Code (fw): Select all Collapse

otmp = CREATEOBJECT( "ADODB.Recordset" )
otmp:cursortype :=1
otmp:cursorlocation :=3
otmp:locktype := 3
otmp:open(SQLCommando,ADO_SQL_Connectionstring)

It only lake 0.03s to run, but after waiting a while (1-2minutes) it take 0.8s, and then 0.03s again...
I have been change the query_cache_size, but no luck

I have been thinking to run a timer that call a query each 20sec, but I think there must be another solution.

The strange thing is that they als have al old XP-PC, and there is no delay, only on the Windows 10 pc's

I just tried to connect my laptop to the network, and on my PC it also keeps running fine (Windows 10 Pro)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO SQL slow after idle for a while
Posted: Fri Nov 04, 2016 09:11 AM

>>>
otmp:open(SQLCommando,ADO_SQL_Connectionstring)

>>>
If you want speed, do not use connection strings, use connection object which is already open.

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: ADO SQL slow after idle for a while
Posted: Fri Nov 04, 2016 10:16 AM

Thank you, but how can I the run each time a different query to receive arecordsset?

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: ADO SQL slow after idle for a while
Posted: Fri Nov 04, 2016 02:11 PM

Marc

Here is a Sql Server example on how to create a Global Connection (oCn ) .. Once you have made your connection .. just pass oCn instead of your connection string. There are other ways you can do the same thing using the FWAdo wrappers .. Rao can help you there.

Rick Lipkin

Code (fw): Select all Collapse

Local xProvider,xSource,xCatalog,xPassword,cString,oRs
Public oCn


xPROVIDER := "SQLOLEDB"    // specific to Sql Server .. ole
xSOURCE   := "YourServer"
xCatalog  := "YourDatabase"
xUserId   := "XXXXXXX"
xPASSWORD := "XXXXXXX"


cString := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD

// global connection string
oCn := CREATEOBJECT( "ADODB.Connection" )

TRY
   oCn:Open( cString )
CATCH oErr
   Saying := "Could not open a Global Connection to Database "+xSource
   MsgInfo( Saying )
   RETURN(.F.)
END TRY

// open a recordset with global connection
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSQL := "SELECT * from YourTable"

TRY
  oRS:Open(cSQL,oCn )
CATCH oErr
   MsgInfo( "Error in Opening YourTable )
      Return(.f.)
END TRY

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: ADO SQL slow after idle for a while
Posted: Fri Nov 04, 2016 03:01 PM

Rick,

Thank you, I will try it.

FiveTech Support Forums

Continue the discussion