Solved

Select Statement Very Slow For DAO to Sybase

Posted on 2000-04-14
16
381 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

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 70

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run code from text file in vb 1 65
Access 2013 combo box not working 3 44
using web browser with BING 40 121
Automatic Email Reminder 4 38
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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