topic-24920

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

SKIP in tMySQL slower then in DBF?
Posted: Sun Sep 30, 2012 08:44 AM

Hi,

When I run this code after a query with about 500 records

      oQry:GoTop()
      DO WHILE !oQry:eof()
           oQry:Skip(1)
      ENDDO

it take about 0.52seconds

When I run this in a DBF-file with the same records

Code (fw): Select all Collapse

Go Top
      DO WHILE !eof()
           Skip
      ENDDO

it take about 0.1 seconds

Is there a faster way to read the result of a query?

Thanks
Marc

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sun Sep 30, 2012 08:55 AM

I don't know what oQry is but have you tried using ADO?

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 01:42 PM

Enrico Maria Giordano wrote:I don't know what oQry is but have you tried using ADO?

EMG

Enrico,

I did a test with ADO and there the skip took only 0.03 seconds instead of 0.5 in tMySQL!!!
So ADO it's much faster then tMySQL if you only skips a recordset.

Marc

carlos vargas

Posts: 1789

Joined: Tue Oct 11, 2005 05:01 PM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 01:56 PM

depend of type of recordset, exist 3 type,
please search in google.
ones is only for skip from first to last record and is very fast.
but no update.

salu2
carlos vargas

Salu2

Carlos Vargas

Desde Managua, Nicaragua (CA)

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 02:02 PM

carlos vargas wrote:depend of type of recordset, exist 3 type,
please search in google.
ones is only for skip from first to last record and is very fast.
but no update.

salu2
carlos vargas

Carlos,

I use Option=3 in the connection-string.
The one I use skipped all records, because I also added a counter after each skip, and at the end the counter had a result of the records in the recordset.

Code (fw): Select all Collapse

t = 0
DO While !oQry:eof()
    oQry:movenext()
    t++
ENDDO
? t

Regards,
Marc

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 02:30 PM

Marc

I like ADO .. the Class and methods are standard whether you use Sql Server, Oracle, dB2 or Access.

Using ADO you can craft your recordset to cache your SQL results to the local client's memory ( which I think you mentioned in your post ) and once that fetch of records are on the client .. browses are fast and seamless .. data manipulation is very fast ..

There is more than one way to open and create a recordset .. FW has a set of functions that accomplish those tasks .. but I like to do it the ole fashioned way so I can assign my own parameters :

Code (fw): Select all Collapse

oRsLog := TOleAuto():New( "ADODB.Recordset" )
oRsLog:CursorType     := 1        // opendkeyset
oRsLog:CursorLocation := 3        // local cache
oRsLog:LockType       := 3        // lockoportunistic

Notice the CursorLocation parameter .. Ms Sql, Oracle, Access support these parameters ..

Rick Lipkin

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 02:38 PM

Rick,

Thank you for your reply.

I'm still testing the ADO-SQL-connection.
Now I use the CreateObject("ADODB.Connection") that is also on the very usefull page http://wiki.fivetechsoft.com/doku.php?id=ado-related_stuffs

I will also try the OLE-way...

Regards,
Marc

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 02:42 PM

Marc

Forgive my choice of words and humor .. 'ole fashioned' translates into "Old Fashioned" :lol: :lol:

You are on the right track and the link to the Wiki is just what I would suggest..

Rick Lipkin

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 02:48 PM

Rick

I found the link from a reply from you in this topic http://forums.fivetechsupport.com/viewtopic.php?f=3&t=24957

Thanks,
Marc

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: SKIP in tMySQL slower then in DBF?
Posted: Sat Oct 06, 2012 02:54 PM

Marc

YES ..

In my opinion, the ( free ) Ms Sql Express is great for creating very robust Sql applications...

http://www.microsoft.com/en-us/download ... x?id=29062

Ms Access will do nicely as well for situations where you just need a local table .. please review AdoRick.prg in the \samples folder showing how to create, manipulate Ms Access and very fast incremental searches using Filters.

I got started using the full Enterprise version of Sql Server years ago and I never looked back... especially attractive to me was the OLEDB provider is native to every modern Windows OS.. you do not have to distribute anything .. just your executable.

Rick

FiveTech Support Forums

Continue the discussion