FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Open recordset with ADO slow with large tables?
Marc Vanzegbroeck
Posts: 1195
Joined: Mon Oct 17, 2005 05:41 AM
Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 08:57 AM
Hi,

I have create a test-table with 120000 records.
I open the recordset with ADO to browse the records.
It take about 13seconds to display the browse. I first though that the browse was slow, but after debugging the code I found out that opening the recordset is slow.

I use
Code (fw): Select all Collapse
t = seconds()
oRs = CREATEOBJECT( "ADODB.Recordset" )
oRs:cursortype :=1
oRs:cursorlocation :=3
oRs:locktype := 3 
oRs:open("SELECT * FROM factui ORDER BY upper(factuurnr) DESC",ADO_SQL_Connectionstring)
msginfo(seconds()-t)


Does anyone else have the same problem with large tables. I'm converting to MySQL because I thougt it's more stable and faster, but opening DBF is faster :-)
Regards,

Marc



FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 09:21 AM

Showing a whole SQL table requires to get all the records. It can't be faster, especially with big tables, than showing a DBF table which requires to get only the visible records.

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: Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 11:43 AM
Hi,

I just did an other test.
I have a table with 2400000 records.
I open a recordset of 20 records, and that take 6 seconds. Setting a scope on a DBF is faster.

Code (fw): Select all Collapse
SELECT * FROM factuit WHERE factuurnr = 20120001


I was hoping, that this was faster in MySQL... :-)
Regards,

Marc



FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 11:47 AM

Did you try both on the same network?

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: Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 11:59 AM

Those 2 test are done on a MySQL (MariaDB) that is locally installed on my laptop.
This evening I will try it on my network, maybe this will go much faster since then the server will run a different machine.
Now I'm with a client.

Regards,

Marc



FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
Posts: 1195
Joined: Mon Oct 17, 2005 05:41 AM
Re: Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 02:12 PM
Marc Vanzegbroeck wrote:Hi,

I just did an other test.
I have a table with 2400000 records.
I open a recordset of 20 records, and that take 6 seconds. Setting a scope on a DBF is faster.

Code (fw): Select all Collapse
SELECT * FROM factuit WHERE factuurnr = 20120001


I was hoping, that this was faster in MySQL... :-)


Please ignore this message. This was my mistake. I forgot to creating an index on factuurnr.
Now it's very fast only selecting some records.

The only thing that is still slow is get a recordset with a lot of records. Mayby I have to limit it with 1000, and get the next 1000 when I am at the last row in my browse()
Regards,

Marc



FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Rick Lipkin
Posts: 2706
Joined: Fri Oct 07, 2005 01:50 PM
Re: Open recordset with ADO slow with large tables?
Posted: Mon Nov 26, 2012 03:14 PM
Marc

Your queries should not be that slow especially if you are crafting your Sql statement to only return 100 or so rows.

Also, as Enrico mentioned .. Select * from any table is expensive and the only reason I Select * from any table is if I need to edit the record.

There are ways you can enhance your performance .. consider this psudo code if you use a browse to edit coding cycle ..

// use this for your browse to select a record to edit where [field1] is the unique key
// primary key and only select fields that need to be viewed in a listbox


Code (fw): Select all Collapse
cSql := "Select [field1],[field2],[field3] from [My Table] order by [field1]" // only fields we
                                                                                                 // need to see 
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

TRY
   oRs:Open( cSQL, Your_Connection_String )
CATCH oErr
      Saying := "Error in Opening table"
      MsgInfo( Saying )
      RETURN(.F.)
END TRY

// user selects record from listbox and it returns the unique primary key cKey

// here is the performance trick
// you do not have to reload the object

oRs:CLose()

cSql := "Select * from [Table] where [Field1] = '"+cKey+"'"
TRY
   oRs:Open( cSQL, Your_Connection_String )
CATCH oErr
    Saying := "Error in Opening table to edit record"
     MsgInfo( Saying )
     RETURN(.F.)
END TRY

// edit your record

oRs:CLose()
oRs := Nil


The point I am suggesting is to only Select the minimum [Fields] in your Sql statement, then close and reopen the row you want to edit.

Hope that helps
Rick Lipkin
Marc Vanzegbroeck
Posts: 1195
Joined: Mon Oct 17, 2005 05:41 AM
Re: Open recordset with ADO slow with large tables?
Posted: Tue Nov 27, 2012 09:27 AM

Rick,

I know it's better to return less records and only select the fields I want to show.

This part of the program is only to show all the items of a client that he sell. He want to browse his complete list.
The recordset is only a read, because if he want to change something, I create a recordsset of only 1 record with the ID-number he selected in the list.
Are the oRs:CursorType, oRs:CursorLocation and oRs:LockType I used the fasted for only read?

Regards,

Marc



FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Rick Lipkin
Posts: 2706
Joined: Fri Oct 07, 2005 01:50 PM
Re: Open recordset with ADO slow with large tables?
Posted: Tue Nov 27, 2012 02:18 PM

Marc

I think the most important component is the cursor location being set to the local machine ( 3 ). I think the other parameter choices are just fine and will have little effect on performance.

I found this on the Microsoft site on the cursor definitions.

http://msdn.microsoft.com/en-us/library ... 85%29.aspx

To be honest, I have never used MySql and did not realize you could use indexes on that platform. Ms Sql Server does not support those type of indexes and the only thing I do is make sure I have at least one 'primary' unique per table.

Rick Lipkin

Continue the discussion

Static archive · New replies & topics via GitHub Discussions