Query hang problem - Delphi and MS Access

Hi all,

I am using Delphi 7 with an MS Access 2003 database. When running some
reports, the application hangs forever on the Query.Open command. The base
tables used by these reports contain between 10k - 40k records. But the
users generally apply filters before generating the report, so the actual
report will return about 100-500 records. But even when the result is
filtered, the query still hangs. I have run these queries directly in
Access, and these execute without any problems. In this particular
application, I have used BDE. But I've tried the same thing with ADO and the
result is the same. I exported the database to MS SQL Server, and this works
without any problem. I've tried repairing the database using the Jet Compact
utility application but the problem still persists. Any help with this is
greatly appreciated.

Thanks in advance.

Best regards,

Mihiri.
0
Mihiri
12/22/2008 9:37:24 AM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

7 Replies
936 Views

Similar Articles

[PageSpeed] 33

Mihiri,

On Mon, 22 Dec 2008 09:37:24 -0000, Mihiri Wijewansa  wrote:

> I am using Delphi 7 with an MS Access 2003 database. When running some
> reports, the application hangs forever on the Query.Open command. The  
> base tables used by these reports contain between 10k - 40k records. But  
> the
> users generally apply filters before generating the report, so the actual
> report will return about 100-500 records. But even when the result is
> filtered, the query still hangs. I have run these queries directly in
> Access, and these execute without any problems. In this particular
> application, I have used BDE. But I've tried the same thing with ADO and  
> the result is the same. I exported the database to MS SQL Server, and  
> this works without any problem. I've tried repairing the database using  
> the Jet Compact utility application but the problem still persists. Any  
> help with this is
> greatly appreciated.

We used to retrieve retrieve hundreds of rows from tables of up to 5  
million rows in Access 2002 databases using Delphi6 and ADO so a 40k row  
table shouldn't give any problems - *AS LONG AS* you keep your queries  
simple.

While Access can run queries incredibly fast, sometimes making only minor  
changes can cause performance to "fall off a cliff" - especially if you  
are turning a 1-d table into a 2-d report by left joins or are using  
sub-queries.

I would suggest simplifying your query to see if it is generally "slow".

And if you haven't already done so, I would also suggest that you try the  
same query from different PCs and/or move a networked file to the local PC  
- just in case you've got disk/network problems.

-- 
Paul Scott
Information Management Systems
Macclesfield, UK
0
Paul
12/22/2008 10:34:40 AM
You may apply a filter but the ADO dataset if it uses a clientside cursor will have to load all the records into memory before it can filter them.
The query you open directly in Access does not have to load all the records.
I suggest you move your filter logic to the SQL query so that just the records you need are included in the Adodataset.


> 
> I am using Delphi 7 with an MS Access 2003 database. When running some
> reports, the application hangs forever on the Query.Open command. The base
> tables used by these reports contain between 10k - 40k records. But the
> users generally apply filters before generating the report, so the actual
> report will return about 100-500 records. But even when the result is
> filtered, the query still hangs. I have run these queries directly in
> Access, and these execute without any problems. In this particular
> application, I have used BDE. But I've tried the same thing with ADO and the
> result is the same. I exported the database to MS SQL Server, and this works
> without any problem. I've tried repairing the database using the Jet Compact
> utility application but the problem still persists. Any help with this is
> greatly appreciated.
> 
> Thanks in advance.
> 
> Best regards,
> 
> Mihiri.
0
Brian
12/23/2008 12:17:03 AM
Paul and Brian,

Thanks for the responses.

I'm sorry, I should have been more clear when I specified the problem. When I mentioned "filter", I don't actually use the filter property in the dataset. I give it as a part of the SQL query in the WHERE clause. 

I'm trying to make the queries a bit more simpler as Paul suggested.

Thanks for the help.

Best regards,
Mihiri.
0
Mihiri
12/23/2008 3:28:53 AM
Mihiri,

On Tue, 23 Dec 2008 03:28:53 -0000, Mihiri Wijewansa  wrote:

> I'm trying to make the queries a bit more simpler as Paul suggested.

Rather than modifying the code in your application - and all its  
associated grids etc - and then getting it to compile, it can be much  
easier to play about with a query if you type it directly into Access

-- 
Paul Scott
Information Management Systems
Macclesfield, UK
0
Paul
12/23/2008 9:22:00 AM
Paul,

Making the queries simpler worked. I built them using Access itself without my writing them from scratch. In some cases, joining table with INNER JOIN worked out. But in some others, linking the tables had to be done as a part of the WHERE clause - table1.field1=table2.field2.

Thanks for the help.

Best regards,
Mihiri.
0
Mihiri
12/24/2008 8:04:14 AM
Mihiri,

On Wed, 24 Dec 2008 08:04:14 -0000, Mihiri Wijewansa  wrote:

> Making the queries simpler worked. I built them using Access itself  
> without my writing them from scratch. In some cases, joining table with  
> INNER JOIN worked out. But in some others, linking the tables had to be  
> done as a part of the WHERE clause - table1.field1=table2.field2.

I'm glad that it's working for you now.

I have found that keeping queries as simple as possible is always a good  
idea. As you've found, the problem with Access (and other RBDs) is that  
sometimes performance doesn't degrade gradually - a minor change can  
sometimes switch a statement from being incredibly fast to so slow that it  
appears to have hung.

(I find I can do a phenomenal amount of post-processing in Delphi in much  
less time than it takes to "join a table to itself")

-- 
Paul Scott
Information Management Systems
Macclesfield, UK
0
Paul
12/24/2008 10:00:10 AM
"Paul Scott" wrote
> On Wed, 24 Dec 2008 08:04:14 -0000, Mihiri Wijewansa  wrote:
>
>> Making the queries simpler worked. I built them using Access itself
>> without my writing them from scratch. In some cases, joining table with
>> INNER JOIN worked out. But in some others, linking the tables had to be
>> done as a part of the WHERE clause - table1.field1=table2.field2.
>
> I'm glad that it's working for you now.
>
> I have found that keeping queries as simple as possible is always a good
> idea. As you've found, the problem with Access (and other RBDs) is that
> sometimes performance doesn't degrade gradually - a minor change can
> sometimes switch a statement from being incredibly fast to so slow that it
> appears to have hung.
>
> (I find I can do a phenomenal amount of post-processing in Delphi in much
> less time than it takes to "join a table to itself")

What I have found the best procedure in many cases is to build a query in 
Access, try it out, display the query in SQL mode and then copy this query 
SQL text directly to my Delphi program source (copy and paste). And when I 
have anything but the simplest joins I have found this procedure the only 
possible that works.

regards Sven
0
Sven
12/24/2008 3:09:24 PM
Reply:

Similar Artilces:

MS access to MS access problem
I always used MS Access Database In my programs, now I want to use SQL SERVER database and so I started to design one. but it’s a little bit confusing ,I don’t know what can I use in change of these access Data type : MS access // MS access -------------- -------------------- Memo >>>>>>>> ?? AutoNumber >>>>> ?? * and if you think there is some difference between Ms access and SQL in D...

Problem accessing query in the design mode MS access
My problem is opening up a query in design mode, not working with forms. I'm not even trying to make a change - it's just opening up the query.This problem is only seems to happen when other people are using the datafile that the query is also looking at. The query is using the linked table but I didnt find any access permision  problem on the table beacuse the query actually do open finally but just after a long delay.   I really apricate any help on this issue Thanks Database might be locked when other people are using it . Remember to close each connection when ...

Problem with MS Access Query
Good Morning, Fairly new to .NET. I am trying to query an Access db once a user selects a month/yr from a dropdown list. Below is summary of my code: if drpdate.selecteditem.text = "JANUARY 04" then CurrentStrMth = "#1/1/2004#" CurrentEndMth = "#1/31/2004#" Dim CurrentStrMth as String Dim CurrentEndMth as String When I run the query below without passing any variables it works fine: cmdSelect1 = New OleDbCommand("SELECT Count(*) from db WHERE [Submit Date/Time]>=#1/1/2004# and [Submit Date/Time]<=...

Insert Query Problem in MS Access
Hi , I am using Asp.net(vb.net) and data base is MS Access. this query give error to me "Error in INSERT INTO Statemenet" i inserted break point,and copy the query text and run this query text at MS access, this runing fine....., but here it gives error.... i also add # sign with date .....like #01/01/2005 10:50:50 PM# , but it not working.... "INSERT INTO User_Info(User_Name,Password,User_Type,Last_Login,Is_Deleted,Is_Enabled,Created_By,Edited_By,Date_Created,Date_Edited) VALUES ('" & Me.UserName & "','" & Me.Passwords & "','" & Me.usertype & "'," & Me....

MS Access 2K Query problem and trace file
Hi all, I can't get a simple query to work in DBI 1.14 / DBD ADO 1.17 / MDAC 2.6 SP1. Records with specific test values can be manually added into the Access table no problem. Deleting these test records and then trying to have Perl / DBI insert the same records results in a syntax error and the trace below. This is such a simple query it has me stumped for the 'Syntax Error' returns. I have several other more complex INSERTs to other tables working without complaint. Deleting existing relationships, primary key (anchor + table unique) and other columns in the table ...

Query Analyzer / Query in MS Access
Hiiiiiiiiiiiiiiiiii, How can create query analyzer  / Query in MS Access using Vb.NEt 2003 (web base)... Can give me sample program for this... For Database i using MS Access 2003... Thanks...

Querying a MS Access Database results in a lock file (.ldb) problem
Hello Forum: Windows 2K SP4 Server with IIS 5.0 I have 5 asp pages.  They are all exactly the same except for one small change to the query in each page.  The problem I am having is that a lock file eventually appears for the database and no further queries work. I notice the problem when I view 1.asp, hit the browser's back button, then click on 2.asp, hit the browser's back button again, click on 3.asp.  This is where everything stops.  The web page will eventually time out(sometimes it doesn't) and give you an erro...

How to access MS OLAP or ADO MD query (MDX) with PB 7 ?
How to access MS OLAP or ADO MD query (MDX) with PB 7 ? Does anyone knows how to Access MS Olap Services. For an example a query like: select {[parts.members]} on columns , [time.year] on rows from cubesales. How ???? Does someone knows where is a document ??? ...

Can ADO.Net query/update a MS Access database that contains table and fields that contain blank spaces and characters such as "#" in their names?????
I've recently been assigned an asp.net/MS Access project. My code works fine; however, there are table and fields in the Access database that contain blank spaces and characters such as "#" in their names. For example there is a table named "Purchase Orders" and in this table there are a few column fields with names such as "PO#" and “Customer PO#". My question: Is there some sort of wrapping method that I need to implement to allow my ADO.net objects (particularly the "OleDbDataAdapter" and the "DataRow") to accept these naming...

ms access DB problem (or hosting providor problem) :(
Hi, Totally frustrated with my current host at the moment. I'm running three small sites of a DNN MS ACCESS install. Now because of this I am convinced in my mind the problem isn't with DNN and the size of the DB. This is my problem: Requests Time out Get the DotNetNuke Error could not connect to the DB Even had a service unavailable messgae once! also when I have gained access to the site e.g. it's loaded the log displays the below: ErrorCode: -2147467259 Errors: System.Data.OleDb.OleDbErrorCollection Message: Record is deleted. Source: Microsoft JET Database ...

MS Access Databind
I was able to export my GridView to Excel just fine when I was using an AccessDataSource to fill the GridView with the results of a stored procedure.  When I then tried to fill my GridView programmatically so that I could put some IF statements to handle a listbox selection, it will no longer export the GridView results to Excel, and it has disabled the sorting.  This seems to be related to how I have defined the datasource for the GridView programmatically, but I can't figure out the correct method.  Any thoughts are greatly appreciated.  Here is my source and code b...

Queries for MS-ACCESS
Hi, I am using MS-access as database. SQL syntax for Access is different than the SQL Server's SQL syntax. So If I want to query some record from my Application, do I need to follow the syntax of MS-Access SQL ?? or I can use the standard SQL syntax that is used for SQL Server?? If you are using an MS Access database, you must use compatible syntax for Access. Using parameters, there are many areas where the syntax can be identical, if you are careful. Things like IIF() and CASE are differences, but much SQL code avoids that.Starting with ASP.NET 2.0? Look at:Programming Microso...

MS Access problems
I'm working on a small internal application with PowerBuilder and MS Access as the backend. I've got the application developed and running very well through the IDE; however, running the EXE/PBDs yields different behavior. Some of the DataWindows do not display data. They're using fairly simply SELECTs; nothing fancy. Other DataWindows within the application are working fine during run-time. Has anyone else experienced this problem? If so, what was done to resolve it? TIA, Mark DeRosa PB 7.0.2 Build 8003 MS Access 2000 Windows NT 4.0, Windows 95 The PBTRACE.LO...

MS Access Problem
I have created a database in Access and ran the user level security wizard on it. It worked fine. I did this same procedure again for a different application with a different database and it does not work through PowerBuilder. I am using Access97 and PB 6.5. I can connect to the database and see the tables yet when I try to get information from them I get the error: COLUMNS INFORMATION: TABLE=i3002f03 OWNER=admin (10 MilliSeconds) Error 999 (rc -1) : Table "i3002f03" has no columns, possible invalid table format. I have since tried just about everything to rectify th...

MS Access Problems
I have created a database in Access and ran the user level security wizard on it. It worked fine. I did this same procedure again for a different application with a different database and it does not work through PowerBuilder. I am using Access97 and PB 6.5. I can connect to the database and see the tables yet when I try to get information from them I get the error: COLUMNS INFORMATION: TABLE=i3002f03 OWNER=admin (10 MilliSeconds) Error 999 (rc -1) : Table "i3002f03" has no columns, possible invalid table format. I have since tried just about everything to rectify th...

Web resources about - Query hang problem - Delphi and MS Access - embarcadero.delphi.ado

Is–ought problem - Wikipedia, the free encyclopedia
The is–ought problem in meta-ethics as articulated by Scottish philosopher and historian David Hume (1711–76) is that many writers make claims ...

Why we all have a problem with expensive drugs
We are all paying for the pharmaceutical industry's laws and arrangements that guarantee it profitability.

More to Slater & Gordon's problems than just the UK
As Slater &amp; Gordon comes under increasing pressure, questions are being asked when the company's problems actually began.

This one app could solve your most frustrating home Wi-Fi problems
So you've just set up your home Wi-Fi network but for some reason you're having trouble getting your PC to connect to the network. It could very ...

Merkel-Enhanced Migration Problem: Million Refugees Hit Europe, 80% Through Greece by Boat; Turkish Mafia ...
... made their way to Europe this year. Every country is complaining now, even Germany. So why isn't anything concrete being done? Why the Problem ...

Maps Minutes Hack #7: No signal, no problem. Navigate your holiday travels with Google Maps offline
Part of our series on how to save time this holiday season with Google Maps. This time of year, millions take a road trip home for the holidays ...

Chicago-Bound Southwest Flight Returns To Oakland With Landing Gear Problem
... flight headed to Chicago was forced to return to Oakland, California, and make an emergency landing on Wednesday, due to a landing gear problem. ...

Oakland Airport On Alert; Southwest Flight Encounters Problem
CBS Local Oakland Airport On Alert; Southwest Flight Encounters Problem CBS Local FORT LAUDERDALE, FL - SEPTEMBER 27: A Southwest airplane ...

Southwest flight reports gear problem, burns fuel before landing attempt
Crews at Oakland International Airport are standing by on "yellow alert" for return of Chicago-bound flight

Widespread access problems for EA
Electronic Arts experienced log in and connection issues for many of its titles on Thursday, the video game company said.

Resources last updated: 12/26/2015 4:24:24 AM