How can I interrupt a BDE/Paradox query?

[FrankThynne]

Is there any way to interrupt a BDE query on Paradox tables between opening it and completing it or returning the first row?

During development I accidentally invoked a half-written and very sub-optimal multi-join query, and the result was a 30-minute wait during which all Delphi activity was blocked!

 

[StevenK]

I'd suggest either using Task Manager (or the likes) to stop your program (and the query) if you're outside the Delphi IDE.
Alternatively if you're using the Delphi IDE then you may need to select Run --> Stop off the menu to stop the program (and it's query).
Hope this helps.
Steve

 

[Robertio]

I just use the same method as Steve, you do manually have to go in and delete the lock files out afterwards though.

 

[FrankThynne]

Thanks StevenK and Robertio for your responses. They confirm the view that "you can't" by using Delphi. Run-Stop doesn't work because the app doesn't respond to the request to terminate while the query is opening. If you open the query at design time (to check grid columns widths, say) then the IDE is blocked completely.

Forcibly terminating the task from Windows isn't really good enough because of unsaved work and lock file problems. Sometimes the executable isn't properly closed which means you can't recompile and replace it without rebooting.

Database desktop has the same problem; a long running SQL query causes the application to hang.

So, it's a bug in Paradox or the BDE or Delphi, possibly all three, then?

 

[svanels]

Most likely the query Steven van Els
SAvanEls@cq-link.sr

 

[footpad]

Frank,

Um...let's step back a moment.

I'm not entirely sure it's fair to say that there's a bug in Delphi, BDE, Paradox, Windows, or even your query without any idea of the query, the versions of your tools, the data, and so forth.

You can interrupt some queries from Delphi by registering a BDE callback. Search the BDE Function Reference for dbiRegisterCallback for details and an example of how to use it.

Generally dbiRegisterCallback is used to report the progress of a long SQL operation, however, it can be used to cancel Sybase queries as well. (At least that's what the Help file says; I have no idea if it works with other servers or formats.)

Now, as far as a long process appearing to make your application hang, that really depends on the nature of the long process. First off, the fact your query is taking half an hour suggests there might be a more optimal way to implement this.

While there are queries that will take this amount of time against Paradox tables, you can usually reduce these dramatically by a) using multiple queries (e.g. select matches in one and then do the joins in a second) or b) using index-based operations (e.g. ranges) instead of non-indexed ones (e.g. queries and filters).

Now, having said all that, you may also wish to consider using the dbiRegisterCallback function to provide your application with the opportunity to process messages. Since dbiRegisterCallback does periodically trigger a message in your callback routine, you can use this opportunity to update a form that reports progress and to include a yield (Application.processMessages).

Finally, I would strongly caution against terminating a local table query against any format. If you accidentally start a long process, it really is best to let it complete. If you do, then you risk corrupting the underlying file(s) and resulting data loss. This affects Paradox, dBASE, Access and other local formats.

If you cannot find a way to reduce the processing time to something acceptable, due to the amount of data you're working with, then it's probably time to consider moving up to a database server format, such as InterBase, Firebird, MySQL, and the others.

Hope this helps...

-- Lance

 

[FrankThynne]

Once again, many thanks to those who have responded.

S van Els - Yes, the query is at fault by being seriously inefficient, but such a shortcoming should not have such a serious effect. The Database Desktop exhibits the same behaviour.

footpad - your response looks promising, but I can't find any mention of the BDE Function Reference in the Delphi documentation - so I can't see how to use the function you suggest. And shouldn't it be exposed as an event in TQuery?

To both of you - the issue here is the product's response to something legitimate but inefficient. One of my tasks involves data conversion and normalisation in a Paradox environment, and some of the queries are convoluted. You will not be surprised to learn that I don't always get them right first time! But the cost of running an incorrect or inefficient query is unreasonably high, especially when using Paradox.

 

[footpad]

Frank,

Ah, sorry about the missing link for more info; I usually try to include one and simply blipped it.

IAE, dbi functions are BDE API functions and are documented in a different Help file than the main ones linked into your Delphi help. They're actually documented in \Program Files\Common Files\Borland Shared\BDE\BDE32.HLP. The topic for dbiRegisterCallback contains a Delphi example.

While I understand your point about legitimate but inefficient, the problem is that this is a case of "It hurts when I do this."

Having gone through some hairy conversions of my own, I understand that it's necessary to study the tables and to find the proper links between the various sources. In this case, much depends on the type of environment you're working in, the design of the source files, the actual location of the data wrt to your workstation, and the configuration you're working with.

Example: In one Paradox project, a client was working with tables on a WAN. There were a few hundred thousand records and he was using a simple SELECT * FROM Table WHERE ID=SomeValue type of query to locate one record. It would take seven minutes.

We noticed that ID did not have a secondary index, so we added on and then created a form that used a TTable and setRange to locate the target record. This took less than one second.

We used similar techniques to reduce a query-based processing system that took 40 hours to run with one that ran in less than three.

High performance applications with large Paradox datasets are possible, but they take longer to develop and debug.

You might think about breaking your queries into separate operations. For example, instead of combining pattern match selection with joins, using multiple queries can frequently improve overall performance (e.g. match the pattern and then do the joins on the result set with a separate query).

This provides a nice upgrade path, for it's generally pretty easy to replace selection queries with setRanges, which in turn provide even better performance.

Finally, you can also try tuning your BDE, though I've personally not needed to do this in my applications. However,

http://bdesupport.com/perform.htm

offers some interesting advice, so YMMV.

Hope this helps...

-- Lance

 

[FrankThynne]

Many thanks, footpad, for your latest. This looks like a very promising line to take.

Your points on optimising queries are well taken. I suppose I am still feeling the pain of some unforeseen slow queries. Still, they did help to make coffee breaks!

Thanks again,
Frank