PervasiveSQL2000 slow data retrieving

[emilioantonio]

hi folks,

i'm developing a VB6 real-time 3d graphic application that retrieves, every 10 seconds, data from a Pervasive.SQL2000 SP2 server database through the following DSNless connection code:

Dim adoConn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
dim curdb as string

curdb = "N:\database\db1" - This is the remote database path

With adoConn

    .Provider = "MSDASQL"
    .ConnectionString = "driver={Pervasive ODBC Client Interface};ServerName=;ServerDSN=;dbq=" & curdb
    .Open
    
End With

If adoConn.State = adStateOpen Then

rst1.Open "select " & Chr$(34) & "ref measured depth" & Chr$(34) & " from " & Chr$(34) & "drilling data" & Chr$(34) & " order by " & Chr$(34) & "ref measured depth" & Chr$(34), adoConn

 rst1.Close
    adoConn.Close

The problem is that looping trough 10000 to 25000 records to retrieve new data to refresh plot it's a very slow process and takes much longer the 10 seconds.

I heard that using bitrieve API to get data it's much faster, but i really have no idea on how to convert
my odbc connection code into betrieve api connection code.

Any help would be very welcomed

Thanks

emilioantonio

 

[TomKane]

Post your query to this form forum321

It's all about Pervasive

 

[zemp]

The pervasive SQL forum, forum318, may also be helpful.

zemp

 

[abaldwin]

Is it the returning of data that takes the time or your "looping through" all the records and processing them that takes the time.

Depending on what you are doing with the data after retrieving (you did not post that part), you may be able to speed things up by executing sql update queries rather than loop through the data and make row changes.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[emilioantonio]

hi abaldwin

Actually i realized that the more consuming time process is "order by" clause in the select statement. In fact without this clause, the query runs significantly faster (but, however, i need to order data)
Looping through as well takes its time.

At the moment i do not have the second part of code available (my collegue is working on it)but i will briefly describe what the application is supposed to do:

1 - retrieve every 10 seconds last record in the field "ref measured depth" of table "drilling data" from The Pervasive database (on a remote server N:\database.....)
2 - append the selected record into the table "mydepth" on local disk resident MS Access database (c:\mydb.mdb) where some computations are finally performed
3 - Refresh plot with new calculated data

How can i speed up the "order by" process?
Can you suggest to me what would be the most performing code to append data to MS Access DB?


Thanks a lot to all of you


emilioantonio

 

[SBerthold]

Yes, an "ORDER BY" is always a killer.

What type of field is "ref measured depth", and is it unique and indexed?

 

[zemp]

You may want to try reindexing your table. That should make ordering faster (If you select the correct field of course).

zemp

 

[abaldwin]

If you are just

retrieve every 10 seconds last record in the field "ref measured depth" of table "drilling data"

Then I would suggest looking at a Max function on a record counter or other unique field to only retrieve the last record.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[SBerthold]

That was what I would have suggested, once feed back to my question was there.

The Max alone should speed it up considerably, and if indexed and especially unique, it should go super fast. Therefore my question.

emilioantonio, If applies, then: "Select MAX(TheField)" without the ORDER BY
Also, change to use a Forward Only cursor, or run the recordset off of the connection's Execute command.