FiveTech Software tech support forums

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

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

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...

Did you try both on the same network?

EMG

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

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

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?

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