Open recordset with ADO slow with large tables?

[Marc Vanzegbroeck]

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

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

[Enrico Maria Giordano]

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

[Marc Vanzegbroeck]

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

SELECT * FROM factuit WHERE factuurnr = 20120001

I was hoping, that this was faster in MySQL...

[Enrico Maria Giordano]

Did you try both on the same network?

EMG

[Marc Vanzegbroeck]

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.

[Marc Vanzegbroeck]

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

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()

[Rick Lipkin]

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

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]

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?

[Rick Lipkin]

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