Optimize performance on a SQL Database

[Eber]

Hi,
I have a a wide Database created using My SQL. I am currently trying to run queries by linking multiple database with a single query. This make the result extremely slow. Many times I need to restart my application to get thing normal. I worry weather this can kill my data. Is there a way by which I can optimize the performance of my Database. Here there is extremely large database which comprise of product related details like sales, purchase, etc. Employee information, customer list, etc. Help needed. Thanks.

[kelfro]

Databases the more extensive require greater attention to organizing their contents. When working with tables of thousands or tens of thousands of records of a data search can be a lengthy process. It is therefore important to consider a number of points necessary. The indices are arbitrarily chosen fields by the builder of the a database that allow searches from the field at a rate substantially higher. However, this advantage is offset by the fact occupy much more memory (twice more or less) and require for your insert. Obviously we can not index all the fields in a large table and we doubled the size of the Database. Similarly, therefore it uses much the index all the fields on a small table and the selections can be made quickly anyway.

[Zecho]

A case in which indexes can be very useful is when we make requests simultaneously on multiple tables. In this case, the selection process can be speedup up significantly if we index the fields that serve as a link between the two tables. In the example of our virtual library and these fields would id_article customer_id. Indexes can be counterproductive if introduced on trivial fields from which no make any request and that, besides the aforementioned memory problem, we are slowing down other tasks such as database editing, inserting and deleted. That's why it's worth thinking twice before indexing a field that serves as a criterion for searching the Internet and is used infrequently for maintenance.

[Reegan]

The link between tables is one of the most tricky and can lead to the absolute slowdown in database because "small" details that prove fatal. Imagine working with a small Database consisting of two tables of 1000 records each. Now imagine a simultaneous selection in which we impose the condition that the value of the first field is equal to a second, which is done frequently. In such cases, the Database will read and compare each field value of a field value with each other. This would represent a million readings. This could get worse if we consult a third table at the same time and could become catastrophic if we consider that the Database is being accessed by several Internet at the same time.

[opaper]

Management of the fields, the choice of appropriate field for each case can also help to optimize the size and speed of our database. The questions that must be made in choosing the nature and dimensions of the field are - What kind of data I store in the country? Numbers, text, date ... or What is the maximum size that hopefully you can achieve any of the records of the field. Keep in mind that the more room we give the maximum value of the field, the higher the size of our database and longer it takes to perform searches. Furthermore, the size factor may be compounded if we are defining an indexed field, for which the space occupied is approximately doubled. One tip is that the dates are stored in date format as this allows us to reduce the space occupied in memory of more than double and on the other hand, we can exploit the benefits that SQL Server and our language offers us.