Select Statement Very Slow For DAO to Sybase

'OrderNbr' and 'PostCode' fields are indexed?

Do you have an index on your table by OrderNbr and PostCode?

:)

How many records are coming in?

Perhaps to isolate the problem you may want to try this..

From MSDN:
--------------------------------------
Measuring Performance

Determining the best algorithm for a given situation isn’t always obvious. Sometimes you’ll want to test your hypotheses; this can be easily done by creating a simple application to measure performance, as shown below. The Optimize.vbp sample application also contains examples of several different test scenarios.

To create a performance testing application:

1. Open a new .exe project.

2. Create a form with two command buttons: Command1 and Command2.

3. In the Command1_Click Event add the following code:

Private Sub Command1_Click()
Dim dblStart As Double
Dim dblEnd As Double
Dim i as Long

dblStart = Timer        ' Get the start time.

For i = 0 To 9999
Procedure to test    ' Enter your procedure here.
Next

dblEnd = Timer            ' Get the end time.

Debug.Print dblEnd - dblStart    ' Display the
' elapsed time.
End Sub

3. Add the same code to the Command2_Click event, substituting the second version of your procedure inside the loop.

4. Run the application and monitor the results in the Immediate window.

This example uses the default property of Visual Basic’s Timer class to time the execution of the procedure within the loop. By placing your code inside the loop for each command button, you can quickly compare the performance of two algorithms. The code can be within the loop or can be a call to other procedures.

You may need to experiment with different values for the upper bounds of the loop counter, especially for fast routines. Make sure that you run each version several times to get an average; results can vary from one run to the next.

>For i = 0 To 9999
You'll need about 2 months for this test :-)

WSH2

Thanks but I only have 3 records in my test Db at the moment.


Ameba,Bhess1,

Excuse my ignorance but how do I know?? Is it a primary key you mean, I have once but not on these fields

cheers
aidan

without an index retrieval will be very slow since the files are unordered the query must search through every record until it finds its match.  if there is some sort of index or access structure, like a hash index or a b+ tree index then retrieval of such records is pretty much instantaneous

Ameba:
"Gateway sells a 650 mhz screamer for $999".. <LOL> and a <wink>. If you will note.. I copied and pasted from MSDN.. I trust the questioner's discretion in trusting Bill Gaters or not.. <smile>.

It doesn't have to be a primary key - I don't know SyBase, but you should be able to look up the indexes for the tables in whatever manager you use to create/modify the tables.

However, with only three records in the DB, that shouldn't be the cause.  Is the Sybase DB local or (more likely) networked?  

Try executing the query twice in the same piece of code:  Does the second query run faster?   This could indicate that some sort of communications initialization is happening the first time.

-----------
  Set dbs = OpenDatabase("WeltBild", dbDriverNoPrompt, True, "ODBC;DATABASE=WeltBildDb;UID=davese;PWD=ciaran;DSN=WeltBild")
  Set rst = dbs.OpenRecordset(sSql)
  Set rst2 = dbs.OpenRecordset(sSql) ' Does this second call execute in the same time, or significantly faster?

-------
Finally, if you haven't already gone there, visit the Databases/SyBase area of EE - they'll more likely have experience with the problem

What is your database engine and which ODBC driver are you using?

In your Sybase database you would have to create an index for the OrderNbr and PostCode fields/columns.. <smile>.

I agree with others that in all likelihood, it's spending time initializing the session, not running your query, however, regarding indexes, let me throw in my two cents:
Just indexing the fields in question may not help any at all, depending on the way they're set up.  If the two fields combined do not form a unique index, then their indexes may not be used at all when performing your query.  Example:
Field        description
EmpID     Unique, (Access "autonumber")  index will be used
Sex         'M', 'F', or 'Other'
DOB        date of birth
  The last two fields do not actually form a unique combination on the employees (except perhaps when sex = "Other"), so most database engines would ignore the indexes (if any) on these two fields when running your query. Time would be spent, however, updating those indexes when writing to the table, so those two fields are actually better NOT indexed.

The DAO Technology in VB is pretty slow. You may get better results with ADO.

The DAO Technology in VB is pretty slow. You may get better results with ADO.

Cheers that i changed it around and it worked fine