Solved

Subsequent starting and stopping dbsrv gives better performance

Posted on 2003-11-28
3
306 Views
Last Modified: 2012-08-13
I have a situation where some queries have a poor performance. (ASA6)
I discovered that if I start and stop the database 5 times, the performance is very good.
This trick does not work with two times, no, 5 times is needed to get the good performance.

How can this be explained?
0
Comment
Question by:jvv
3 Comments
 
LVL 6

Accepted Solution

by:
DaniPro earned 250 total points
ID: 9848929
This is a strange situation, I'm suggesting you of verify the plan of your query when the database start for frist time and when the database start for tifth time.
After you can try to drop the database statistics [ DROP OPTIMIZER STATISTICS ].

Occasionally, the statistics may become inaccurate or relevant statistics may be unavailable. This condition is most likely to arise when few queries have been executed since a large amount of data was added, updated, or deleted.

In this situation, the best approach is to execute statements that allow Adaptive Server Anywhere to scan the new rows sequentially. The internal statistics are automatically updated as a side effect, allowing the optimizer to choose better plans for subsequent queries.

The DROP OPTIMIZER STATISTICS statement deletes all internal statistical data from the column statistics registry. This drastic step leaves the optimizer with no access to essential statistical information. Without these statistics, the optimizer may generate very inefficient data access plans, causing poor database performance.

This statement should be used only during problem determination or when reloading data into a database that differs substantially from the original data.


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now