MySQL - reducing access of database??? - Asked By avula on 11-Nov-11 07:09 AM


Hi Every One...

all end users accessing same tables, and loading thousands of records at any point of time,inserting ,fetching many records at same time due to this operations my application is not responding and my goadday server is goes down when i requesting any information to server..

please help me how can i solve this problem..?? and  is there any problem in my database design ..?? how should i increase my application performance is there any execution plans in MSSQL, please help me ???

waiting for your kind responce???

thanking you all...









 
Jitendra Faye replied to avula on 11-Nov-11 07:14 AM
You can use  MySQL Query Cache

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

Follow this link-
http://dev.mysql.com/doc/refman/5.5/en/query-cache.html

Hope this will help you.
dipa ahuja replied to avula on 11-Nov-11 07:14 AM
Untitled document
Tips For Optimizing Sql Server 2005 Query Performance
 
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
 
Kirtan Patel replied to avula on 11-Nov-11 07:18 AM
to improve database performance 

Create Indexing in database that will result in fast response of query 

To Improve Website performance and Reduced load to server 

use asp.net caching which will return cached content when same content is retrieved for more than one time .
Suchit shah replied to avula on 11-Nov-11 07:29 AM

One way by which you improve your performance is by determining whether to use DataSet or DataReader. Both of them has advantages & disadvantages, one can say Dataset as a Temperary Database for each client, filtered by that client on the server working on a disconnected architecture. Where as Datareader is similar to a Recordset of VB 6.0 or Clasical ASP with Forwardonly cursor. While working with Dataset Connection is closed where as in Datareader Connection is maintained.


When to consider using a DataReader:
•The DataReader is a better choice for applications that require optimized read-only and forward-only access to data such as binding to a DataGrid control. The sooner the data is off-loaded from the DataReader and the connection is closed the better the application performance.
•The DataReader is a better choice when you are planning to make repeated calls to retrieve small amounts of information or the data you are retrieving must be as up to date as possible each time it is used.


When to consider using a DataSet:
•The DataSet is a better choice for applications that will not off-load the query result immediately, or when there is extensive processing such as complex business logic involved between data accesses. The DataSet will retrieve the data, off-load the data into memory and return the database connection to the connection pool, where as a DataReader would keep the connection locked open until processing is complete. This could easily cause a high traffic application to run out of available database connections.
•The DataSet is a better choice when you need to navigate through the data more than once. For example, if you have multiple controls you need to build off the same data, then a DataSet is the better answer because a DataReader can only be read once so it can only be bound to a single control and would require the data to be retrieved for use with each control.
•The DataSet is a better choice when the data does not change frequently enough to warrant always retrieving it from the database or is specific to the user requesting the data. A DataSet can be stored in Session or Application variables or cached through the System.Web.Caching.Cache class to improve application performance by not having to retrieve the data from the database each time it is needed.
•The DataSet is a better choice when building a Web service that will return the retrieved data. Since a DataSet is serializable it can serve as the return value. Since a DataReader requires a persistent database connection, it cannot be used as a return type from a Web service.


Some more tips (extracted from http://www.sql-server-performance.com/tips/asp_net_performance_p1.aspx) hav ea look at this site to get more details, real good one!


Take advantage of the SQL Server .NET data provider to access SQL Server data using ADO.NET, as it offers the best overall performance (as compared to previous technologies).


Whenever accessing SQL Server data, consider always using a stored procedure if you want maximum performance. This is true whether you are selecting, inserting, updating, or deleting data. Avoid using ADO.NET's methods to access SQL Server data, as they will always be slower than accessing SQL Server data directly with a stored procedure.


Take full advantage of connection pooling



from the Patterns & Practices "Improving .NET Application Performance and Scalability" guide:
Checklist: SQL Server Performance - http://msdn2.microsoft.com/en-us/library/ms979169.aspx
Checklist: ASP.NET Performance -
http://msdn2.microsoft.com/en-us/library/ms998596.aspx