SQL Caching in .NET - Increase SQL server performance.
Do you find that your SQL server is not performing as fast as it used to?
More often than not, your SQL server is doing the same repetitive task over and over, and
producing the same data to your visitors time and again. As your SQL server database grows in
size, the queries you run on it take longer, and ruin your user's experience.
This article proposes SQL server caching. - A technique, where the data returned from frequently executed SQL statements and stored procedures is cached locally for a period of time (default is 3 hours). The next time you run that SQL statement, it refers to the local copy in a flat-file, rather than querying your SQL server. This also helps load-balance between your SQL server and web server.
The best bit is, that you don't need to modify your code drastically to use this caching feature. Just replace occurances of "SqlDataAdapter" with "CachedSqlClient.SqlDataAdapter" with Find & Replace, and the caching will kick in immediately.
Caching works best when your SQL server is on a different machine to your webserver. So, for instance, if you specify your SQL server in your connection string with an IP address or URL, it probobly is on a different machine. Furthermore, if your queries return small sets of data that take a long time to generate, caching will really excel.
You do not need to have a second SQL server, as the caching is done in local files. Technical note: If you are running this on a ASP.NET website, you may need to ask your web-hoster to provide file-write access to the /bin folder where you copy
CachedSQLClient.DLL
Download SQL Cache for .NET
To give a simple example, if we wanted to fill a dataset with data pulled from SQL server,
we would use (C#):
SqlConnection DSN = new SqlConnection(connectionString);
DSN.Open();
SqlCommand Database = new SqlCommand(sql,DSN);
SqlDataAdapter Adapter = new SqlDataAdapter(Database);
Adapter.Fill(ds,"sql");
DSN.Close();
In order to modify this to support caching, you add a reference to
CachedSQLClient.DLL then type:
SqlConnection DSN = new SqlConnection(connectionString);
DSN.Open();
SqlCommand Database = new SqlCommand(sql,DSN);
CachedSqlClient.SqlDataAdapter Adapter = new CachedSqlClient.SqlDataAdapter(Database);
Adapter.Fill(ds,"sql");
DSN.Close();
Virtually identical!, simply do a find & replace (CTRL+H) and you can change this in an instant.
This code has been tested to work in ASP.NET, VB.NET, and also with parameterized stored procedure commands. Everywhere that you would use the .Fill() method.
Some benchmark tests showed the following:
Remote SQL query (select * from sysobjects) took 7.5 seconds without caching.
Remote SQL query (select * from sysobjects) took 1.9 seconds with caching.
- 3.9 times faster!.
Download the source code, documentation, and examples here.
