Solved

Subsequent starting and stopping dbsrv gives better performance

Posted on 2003-11-28
3
308 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Small DB [ Application with 20GB Storage Size Minimum ] 4 517
SyBaseSQL Select column if it exists 3 1,448
Sybase conversion 2 75
I need expert Sybase Sql help with a query 3 70
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Ransomware is a malware that is again in the list of security  concerns. Not only for companies, but also for Government security and  even at personal use. IT departments should be aware and have the right  knowledge to how to fight it.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

820 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