Select Statement Very Slow For DAO to Sybase

Hi all

I am having a majot problem with my app here. I am looking to retrieve a record based on the criteria I give it, postcode and reservation number using DAO to Sybase, when I set the rst it takes 20 seconds to do, I have a 100mbit connection to the server.

Is there any problem with my code structure do you reckon??

Code is below

sSql = "Select ArticleNum, Quantity, ParkDate, ParkPrice,"
  sSql = sSql & " ArticleNum2, Quantity2, ParkDate2, ParkPrice2,"
  sSql = sSql & " ArticleNum3, Quantity3, ParkDate3, ParkPrice3,"
  sSql = sSql & " ArticleNum4, Quantity4, ParkDate4, ParkPrice4"
  sSql = sSql & " From WeltBildTb WHERE OrderNbr = 1034 AND PostCode = 'AG123'"
  Set dbs = OpenDatabase("WeltBild", dbDriverNoPrompt, True, "ODBC;DATABASE=WeltBildDb;UID=davese;PWD=ciaran;DSN=WeltBild")
  Set rst = dbs.OpenRecordset(sSql)
  ' Set the default width of each column (user can change this at run time)
  TheWidth = 2000
  ' Set the style of the ListView so it will have columns
  ListView1.View = lvwReport
  ' Setup the columns
  ListView1.ColumnHeaders.Add , , "ParkName", TheWidth
  ListView1.ColumnHeaders.Add , , "ParkDate", TheWidth
  ListView1.ColumnHeaders.Add , , "ParkPrice", TheWidth
  ListView1.ColumnHeaders.Add , , "QTY", TheWidth
  ' Clear the list by default
  'Add in the Park Details
  Parknamed = Trim(rst!ArticleNum)
  ParkNames (Parknamed)
  ParkDate = rst!ParkDate
  ParkPrice = rst!ParkPrice
  QTY = rst!Quantity

Who is Participating?
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
Use an ODBCDirect workspace like the example given at
'OrderNbr' and 'PostCode' fields are indexed?
Brendt HessSenior DBACommented:
Do you have an index on your table by OrderNbr and PostCode?
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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.

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 :-)
aidan_gillAuthor Commented:

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


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

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
"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>.
Brendt HessSenior DBACommented:
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
Éric MoreauSenior .Net ConsultantCommented:
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.
aidan_gillAuthor Commented:
Cheers that i changed it around and it worked fine
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.