Solved

Query Freezing - Why?

Posted on 2004-04-01
8
711 Views
Last Modified: 2008-03-04
I am running a query that takes a large volume of information(over 2 million records), and does a lot of counting, sorting and cross-referencing. Understandably, this is taxing on the system and takes a while. When I run query on an old PII-300Mhz running Win98, it waits for about 25 min, that gives a message "This action cannot be carried out at this time". When I run it on a PIV-2.7Ghz running WinXP, it takes about 25-30min and gives me results. Occassionally it will say "ODBC call failed", but it usually works. I can understand why the query runs slower on the PII, but why does it freeze all together. Can someone help me understand what exactly is going on when I run the query between the access interface, querying the Oracle database on the central server and doing all the processing (is that done locally?). Any links to sites that explain this process would be helpful too. Thanks.
0
Comment
Question by:SG072297
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10735712
Because you're running it on an old PII-300Mhz with Win98.  :)

If you want the Oracle server to run the query, designate it as a pass-through query.  This means the remote server takes the uninterpreted SQL for the query, parses it, counts/sorts/cross-references, then returns only a recordset to the calling sub.  If you don't tell it to use a pass-through query, you are having the local (calling) system open the entire table, send it over the network, then do all the record-gathering locally.  With over 2 million records, that old PII will cough and die, and that's probably on a good day.
0
 

Author Comment

by:SG072297
ID: 10743615
Ok I will try and avoid the PII-300. Do you know of some sites/links that will have some technical details into what happens when a query is excuted. Reason being, in order to avoid the PII-300, I think I will have to make a pitch to management, so I'll need some backup. Thanks.
0
 

Author Comment

by:SG072297
ID: 10743627
In terms of the sites, maybe like a DB primer or something. My knowledge is fairly limited.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 51

Expert Comment

by:Steve Bink
ID: 10746107
Even with the PII-300, you can still have the Oracle server do the grunt work.  It involves a query type called a "pass-through query".  It is the equivalent of going to a BK drive-thru instead of staying at home and cooking...hrrmm...must be hungry again.  BTW, if the query is taking 25-30 minutes on 2.7ghz box, you should consider changing it to pass-through to begin with.  The Oracle server should not take NEARLY as long to get that done.

Unrelated to your problem....

<PERSONAL_RANT>
If your management needs CONVINCING to replace a PII-300, don't approach them about it until you hear the loud pop that signifies their collective heads have been pulled from their collective arses, and you can tell them I said that.  As a tech, nothing infuriates me more than some ignorant manager claiming they can't afford a new computer system to replace some broken down heap of slag.  I can build an up-to-date office workstation (meaning 1.8ghz CPU w/512m mem) for less than $500 using premium parts, and an upgrade like that pays for itself before the ink on the check dries.  Between hours of productivity lost on waiting for the machine to decide to work and the money they will undoubtedly be paying some poor schmuck (read as: the guy they blame when it breaks) to keep it working, they are spending more than that just to hold on to out-dated hardware.  Yes, you CAN start a fire with two sticks...that doesn't mean you SHOULD.
</PERSONAL_RANT>
0
 

Author Comment

by:SG072297
ID: 10756893
Do you know how I can change it to a pass-through query? thanks.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 10759186
According to the Access/VBA help files, you can only use SQL pass-through with JET Snapshot-type recordsets.  This means a 'snapshot' is taken of the data, and the recordset's data will not change with the underlying table until you requery.  It should be what you need.  Below is an example from the VB help file, as well as some additions for opening the recordset.  This example assumes you will use a DSN for the connection...you also have the option of building your own ODBC connection string.

Dim wrkODBC as Workspace
Dim cn as DAO.Connection
Dim rs as DAO.Recordset

   ' Create ODBCDirect Workspace object and open Connection  objects.
   Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
     
   Set cn = wrkODBC.OpenConnection("Connection1", , , "ODBC;DATABASE=pubs;DSN=Publishers")      

   Set rs = cn.OpenRecordset ("<YOUR SQL QUERY>", dbOpenSnapshot, dbSQLPassThrough)

Opens a snapshot recordset------------------------------------^^^^^^^
Tells Access to use a pass-through query------------------------------------------^^^^^^^^^
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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