ADO SQL slow after idle for a while

[Marc Vanzegbroeck]

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]

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: Select all  Expand view

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]

>>>
otmp:open(SQLCommando,ADO_SQL_Connectionstring)

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

[Marc Vanzegbroeck]

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

[Rick Lipkin]

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: Select all  Expand view

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]

Rick,

Thank you, I will try it.