Adam Machanic has posted a month ago an article about SqlDataReader performance tips. One about using an indexer with the ordinal position, a second one about avoiding the Get<datatype> methods and a third about opening and closing the reader.

Especially the last one is something we all sometimes forget. Thanks to Adam for these tips! :)

Tip 1: Use an indexer with the ordinal position:

Instead of:

          
            while
(reader.read()) object x = reader.GetValue("ColX");
          
        

or

          
            while
(reader.read()) object x = reader["ColX"];
          
        

do:

          
            int
ColX = reader.GetOrdinal("ColX"); while (reader.read()) object x = reader[ColX];
          
        

Tip 2: Avoid the Get<datatype> (e.g. GetInt32) methods at all costs, and use static casts instead of converts:

Bad:

          
            int
x = reader.GetInt32(ColX);
          
        

Better:

          
            int
x = Convert.ToInt32(reader[ColX]);
          
        

Best:

          
            int
x = (int)reader[ColX];
          
        

For maximal performance return the SQL Server equivalent of whatever datatype you'll be casting to (so that you can avoid the Convert methods.) And avoid NULLs so that you don't have to check for DBNull on the client.

Tip 3: "open late and close early"

Finally, just like in classic ADO, when using a SqlDataReader, "open late and close early" is the way to go. Open your connection at the last possible moment and close it as soon as you're done reading the data -- this will maximize connection pool availability.

you can find all the tips also at his blog by following this link:

http://sqljunkies.com/WebLog/amachanic/archive/2005/04/06/10462.aspx

As we know every database is build from two files on disk. The first file contains all data and the second file contains the log of the database. The log file grows and grows and grows...

Yesterday i had to backup a database from server A and restore it back onto server B. When i tried to restore the database i got the message that an enormous amount of disk space was needed. I discovered that my log file of the database was almost 145 Gb big. During the restoring the system tries to reserve the same amount of space which was not there.

Thanks to Bob from Logica CMG i found out that you have to do the following to shrink your database files. Open your properties of the database and go to the tab called "Options". There you will find a dropdown box for the recovery model which you have to set to "Simple".

Open the Query Analyser and execute the following command:

DBCC SHRINKDATABASE(database_name) where database_name is the name of the database.

Both files for the database are shrunk to a minimal size. Now i could easily backup my database from server A and restore it on server B.

Thanks to the blog of Dave Donaldson i found a link to a website about connection strings for any type of database. The link to the website is:

www.connectionstrings.com