lizm
asked on
SQLAnywhere Performance Problem
We have an application that has been live for the past 4 months. Last month creating invoice records (getting arrears, Debits and Credits) took 5 hours to run. This month the same program took 40 Hours (Yes that is forty!!) to run. Invoice records consist of two files, header and detail. The header file contains from 4000 - 4500 records and the detail consists of 2-3 times as many records. There were no changes to any of these programs in the past month. The only thing I add were two view over the detail records to capture tax and usage records. The header record has 3 views over it and the detail has the two new views previously described. If anyone can help with this I would greatly appreciate it. if you need any more information please let me know.
Thanks for any response ahead of time!!
Thanks for any response ahead of time!!
ASKER
I have looked for the UPDATE STATISTICS command, but I cannot find it. I am using Sybase SQLAnywhere 5.5. Perhaps I am not looking in the correct place. Could this command be run in ISQL. I tried it and it returned an error. Could you please clarify your answer.
Do you have any indexes on header_id for both the header and detail table?
I'm sorry about the other reponse, it works on Sybase databases but not SQL Anywhere.
I'm sorry about the other reponse, it works on Sybase databases but not SQL Anywhere.
ASKER
I do not have any indexes connecting the two. The program that is running so slow is simply reading through the header and checking for any outstanding A/R or Credits for each account in the header. If any are found, these are added to detail. At first I thought my problem was due to the fact that A/R Detail and Payment Detail had gotten so large, but posting to these files has not slowed down,so I don't think that is the problem. Do you think putting an index would help in this situation.
I would like to thank you for your help, and I am sorry that I did not answer this sooner, but I was out of town yesterday.
I would like to thank you for your help, and I am sorry that I did not answer this sooner, but I was out of town yesterday.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if the answer doesnt solve your problem please reject it
have you tried dbunload and reload of your db file?
have you tried dbunload and reload of your db file?
ASKER
Your answer is correct. I had a problem with indexes, but it was something the application I use calls virtuals. The application does an automatic join on two files to bring in the virtuals. I removed these virtuals, so in effect, I removed an index and my problem is solved. Thanks for all your help. I am sorry I did not respond sooner, I did not realise there was an update. I did not receive any email showing there had been an update,(or I overlooked it).
I am from South Louisiana.
I am from South Louisiana.
Do this by running update statistics on your tables such as
update statistics invoice_header
If you are using stored procedures you should have them recompiled by running sp_recompile in your tables
sp_recompile invoice_header
This will recompile all procs that use invoice_header.
This should fix your problem assuming you have CORRECT indexes on your tables. If you don't then that is your problem. This is not a big database and should not take that long to do anything.