Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
Available now!
Buy at Amazon US or
Buy at Amazon UK



Articles

» Windows API reference
» Webcam streaming in VB.NET
» Remoting with firewalls
» RSA from first principles
» Key & MouseLogger in .NET
» Networking Resource Kit for .NET
» Migrating VB6 Winsock to VB.NET
» Migrating C++ sockets to C#
» RFC Reference guide
» COM Reference guide
» WMI Reference guide
» SQL stored procedures
» TCP & UDP port reference
» NET Framework reference
» Ethernet Type codes
» MAC address assignments
» DLL entry point reference
» Boost SQL performance
» Free SMS UK
» Free SMS Ireland
» Free SMS South Africa
» Internet Explorer

Contact us

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.



Google

Copyright 2015 Open Merchant Account Ltd.