|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Queries to the Bill table take a long time |
| Author |
Message |
| Alex |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-10-02 |
|
| Profile |
|
| Posted : 2008-10-02 07:19:21 |
I wonder if you could help me. I'm finding that simple queries to the Bill table are taking an etremely long time to execute (upwards of 5 hours). An example of the type of expression I've been having trouble with is:
SELECT * FROM Bill WHERE TimeModified > {ts'2008-09-30 11:58:32'}
I've also tried the above expression with just a limited list of fields This field should only return a few rows (a dozen or so at most). I've also been having problems (again very long-running queries) on simple select statements which use the jumpin field TxnID (SELECT * FROM Bill WHERE TxnID = 'some txn id').
My optimiser is set up as follows:
- Use Optimiser: Checked
- Keep my data up to date as of: The start of every new connection (with "Load Updated Data" first)
- For balances and stock quantities: NULLs
- Optimise data after an insert or Update: Unchecked
- Multi-table Sync: Unchecked
I've this problem on multiple machines. Any help you could profer would be greatly appreciated.
Thanks!
Alex |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-02 09:05:51 |
|
|
|
| Alex |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-10-02 |
|
| Profile |
|
| Posted : 2008-10-02 23:20:54 |
Thanks for your prompt response!
I've just let the statement complete, total time 21 hours.
We have around 16,000 bills in the Bills table. Running the query unoptimised still takes an unacceptably long time, however running the query optimised now takes only a few seconds. Strange, as I'd hit the Load Updated Data button to optimise (and left it overnight), I wonder if it failed to complete or was interupted, this raises a couple of further questions:
- Is there some way of telling whether the optimisation process completed successfully (a log entry or some such)
- Is there any way of determining programatically whether a given table has or has not been optimised, as hitting a 22 hour query is unacceptable in the production environment.
Thanks!
Alex |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-03 08:01:36 |
The log files are found in the Messages Tab in the QODBC Setup Screen. QODBC will automactically update optimized tables, and optimize tables you haven't used only when you actually use them.
Load Updated Data updates all the tables. Just let QODBC work. |
|
|
|
|