?
Solved

SQLAnywhere Performance Problem

Posted on 1998-08-05
7
Medium Priority
?
438 Views
Last Modified: 2008-02-01
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!!
0
Comment
Question by:lizm
[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
7 Comments
 
LVL 3

Expert Comment

by:gmoriak
ID: 1098180
It sounds like you need to update your index statistics.

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.

0
 

Author Comment

by:lizm
ID: 1098181
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.
0
 
LVL 3

Expert Comment

by:gmoriak
ID: 1098182
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.
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:lizm
ID: 1098183
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.

0
 
LVL 3

Accepted Solution

by:
gmoriak earned 400 total points
ID: 1098184
If figuring out if there is outstanding A/R or a credit for a header requires reading detail, then adding an index absolutely will help.  Not knowing your table layout, but guessing from past experience - you should add an index to invoice_header on header_id and add on to invoice_detail on header_id

If you figure out A/R or credit just based on a column in invoice header, then you should put an index on that column so they can be found immediately without scanning the table.

If you don't have any indexes, then I'm sure your application could be made to run much, much, much faster by tuning the database with correct indexes.  However, wrong indexes can be worse than not having any at all.

Where are you from?
0
 
LVL 1

Expert Comment

by:spacebrain
ID: 1098185
if the answer doesnt solve your problem please reject it
have you tried dbunload and reload of your db file?
0
 

Author Comment

by:lizm
ID: 1098186
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.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
Learn how to use the free Acronis True Image app to easily transfer data between iPhones and Android phones.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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