[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2003 - Select Query Fast  But Append or Make Table Query slow

Posted on 2006-06-06
22
Medium Priority
?
887 Views
Last Modified: 2007-12-19
I have a select query that uses ODBC to retrieve from a Sybase database.  The select query completes in about 5 seconds.  There are about 3000 records.  When I run the same select query as an append query or a make table query (to a local Access table),  the progress bar moves halfway in about 5 seconds (probably indicating that the query has completed) but the query hangs at that point.  The 3000 records never make it into the destination table.  Any ideas?
0
Comment
Question by:glentek
  • 7
  • 7
  • 5
  • +2
21 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16844745
Using linked tables to the Sybase database?
And the Append query also fails?

Is it the number of records - or the action of appending that fails?

(i.e. if you select only the top 1 record does it still fail?)
0
 

Author Comment

by:glentek
ID: 16844859
Yes, I am using linked Sybase tables.  The select query works fine,  The 3000 are returned in about 5 seconds and I can scroll through them. When I take the select query and change it to an append query, or a make table query, it appears as if the query completes on the Sybase server (after about 5 seconds the progress meter jumps halfway), but the results never make it to the local destination Access table.  The progress meter sits at the halfway point.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16844934
Yeh - sounds like Jet doing some weird cleanup that doesn't finish.

As I was saying though - have you tried appending fewer records?
Just to see if it's the amount that is struggling.

SELECT TOP 1 * INTO LocalTable FROM Linkedtable

And then the same for the append?
Are you able to make a passthrough query for Sybase?  (Don't use it - so can't know for sure).
Should be quicker.

Alternatively - you could perhaps fill the records manually - using a recordset.
(But more faff - but if it isn't working and you have no choice then we do what we must :-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:glentek
ID: 16845107
When I narrow the search so 1 record is returned, the append query displays a prompt asking whether I want to add the record to the local table, and completes.  It should not take so long to add 3000 records....
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16845160
I'll make a bet that the table has a lot of fields across as well.  And the destination database is on the network not your local machine.

What is probably happening is that the select is going quick, but the write/commit is where it's hanging.  If you bring up your task manager and look at the networking tab you'll see steady traffic across it.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16845279
And it *could* even be a problem / limitation of the ODBC provider.
A sequence of smaller appends might be the most efficient way.

I'd have imagined the append is to a local table (especially in your make table ;-)
0
 

Author Comment

by:glentek
ID: 16845513
The destination table and the retrieved records have 7 fields.  The destination table is an Access table on the local machine.

Here is new info:  I progressively expanded the search criteria to allow more and more records.  The append query works for any number of retrieved records up to and including 50.  If the number of retrieved records is 51 (or greater) the query appears to complete on the Sybase server (the progress bar jumps halfway), but the hourglass cursor continues, the popup asking whether I want to add the rows doesn't appear, and the thing never finishes.

Any ideas now?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16845622
Do you have the query properties set to "Use Transaction" Yes or No?
0
 
LVL 3

Expert Comment

by:kenspencer
ID: 16845657
Hi,
Seems like a low number for this 'straw', but is there some kind of record-locking limit on the server?  I had an instance where the network guys quietly pushed out an upgrade that caused some of my stuff to fail because they had left a default record-locking limit on.

Ken
0
 

Author Comment

by:glentek
ID: 16846099
jimpen - Use Transaction is set to Yes.
0
 

Author Comment

by:glentek
ID: 16846110
kenspencer - I haven't heard other users complaining about this kind of problem...
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16846373
>>  Use Transaction is set to Yes.

Try setting it to No
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16846956
Deja Vu (as for many of us I'm sure).
Have heard of this as an ODBC restriction and a Network issue and a simple bug (fixed by something like recreating the table I think was it :-S)
So any of what's been suggested above may yet suddenly sort it.

Have you also determined if a passthrough works any better?
(As linked tables could be adding another possible obstacle to the issue).

And if all fails - a series of smaller appends.
0
 

Author Comment

by:glentek
ID: 16847114
jimpen - Tried that.  No help.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16847150
Can you monitor the server performance?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16847233
This is the messy way to go about it but what happens with something like this?

Note this is AirCode
--------------------------------------------------------
Public Function Load_Sybase_To_Local()

Dim DB As Database
Dim RS As Recordset
Dim SQL As String


SQL = "SELECT * " & _
    "FROM MySybaseTable " & _
    "WHERE MyFields = MyCriteria"
   
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
Else
    MsgBox "No Data", vbExclamation, "Exiting Fuction"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If

Do Until RS.EOF
    SQL = "INSERT INTO MyLocalTable (fld1, fld2,....fldx) " & _
        "VALUES( '" & RS!fld1 & "," & RS!fld2 & ",........" & RS!fldx & ")"
    DB.Execute SQL
    RS.MoveNext
Loop

Set RS = Nothing
Set DB = Nothing

End Function
0
 

Author Comment

by:glentek
ID: 16847276
I found the problem.  The Sybase ODBC driver (syodase.dll version 4.20.0.15) has a config parameter on the ODBC Adminstrator "Performance" tab called "Select Method".  The default is "1 - Direct" which, in this situation, causes Access 2003 to choke when trying to "append table" or "make table" with a query that returns more than the "Fetch Array Size" which defaults to 50 (another one of the "Performance" tab parameters).  Setting the "Select Method" to "0 - Cursor" fixes the #$&@#&*!! problem.  Can I award the points to myself?  Thanks to everyone here for trying.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16847327
Crap -- we're using the DBODBC8.DLL (the ASA8 drivers).  We have similar problems but not the same tabs.

Post to community support with a link to have the question PAQed/Refunded.
http://www.experts-exchange.com/Community_Support/
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16872516
PAQ - refund
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16872634
Agreed - PAQ
0
 
LVL 1

Accepted Solution

by:
kodiakbear earned 0 total points
ID: 16927295
Closed, 500 points refunded.
kb
Experts Exchange Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

834 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