Solved

Select Statement Very Slow For DAO to Sybase

Posted on 2000-04-14
16
374 Views
Last Modified: 2008-03-17
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
thanks
AIdan

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
  ListView1.ListItems.Clear
   
  'Add in the Park Details
  Parknamed = Trim(rst!ArticleNum)
  ParkNames (Parknamed)
  ParkDate = rst!ParkDate
  ParkPrice = rst!ParkPrice
  QTY = rst!Quantity


0
Comment
Question by:aidan_gill
  • 3
  • 3
  • 2
  • +5
16 Comments
 
LVL 15

Expert Comment

by:ameba
ID: 2716887
'OrderNbr' and 'PostCode' fields are indexed?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2716891
Do you have an index on your table by OrderNbr and PostCode?
0
 
LVL 15

Expert Comment

by:ameba
ID: 2716904
:)
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2716910
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.

0
 
LVL 15

Expert Comment

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

Author Comment

by:aidan_gill
ID: 2716968
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
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2717000
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
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2717010
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>.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 32

Expert Comment

by:bhess1
ID: 2717031
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
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2717035
What is your database engine and which ODBC driver are you using?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2717040
In your Sybase database you would have to create an index for the OrderNbr and PostCode fields/columns.. <smile>.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 2717093
Use an ODBCDirect workspace like the example given at http://support.microsoft.com/support/kb/articles/Q177/5/16.ASP?LNG=ENG&SA=ALLKB&FR=0
0
 
LVL 2

Expert Comment

by:JetScootr
ID: 2725209
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.
0
 

Expert Comment

by:shabbirab
ID: 2766056
The DAO Technology in VB is pretty slow. You may get better results with ADO.
0
 

Expert Comment

by:shabbirab
ID: 2766200
The DAO Technology in VB is pretty slow. You may get better results with ADO.
0
 
LVL 2

Author Comment

by:aidan_gill
ID: 2787790
Cheers that i changed it around and it worked fine
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now