Writing OPtimized Stored Procedure

Alot of good info from: http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

Extreme Performance with Indexes

We have a table that keeps track of access to a particular Application. Just few days back, the web app is reported to be slow, I did a check via (SQL Profiler) and a number of SQL commands that were slow, but one that caught my eye was one that called an SP, it Took 17sec to execute!

A check on the table shows, table has 2,000,000++ records, and also the table has no primary key or index.

So this is what we did,
1. create an identical table
2. declare primary key on this table
3. create index (for columns that are mentioned in the sp's where clause)
4. import data from old table to new table
5. rename old table to _old
6. rename new table to the table name

Performance! improvement?

for a start, just before we did the change, it was taking 1,000 - 4,500 ms to execute, when we did the change the time taken to execute drastically dropped to (0-5ms)! thats HUGE improvement over performance! Im Shocked! of how of a big difference index makes to insert/update/select.

Apprantly LINQ to SQL (is not Good!)

LINQ to SQL issues for high demand aspNET website
http://omaralzabir.com/solving_common_problems_with_compiled_queries_in_linq_to_sql_for_high_demand_asp_net_websites/

LINQ to SQL Compiled Queries
http://linqinaction.net/blogs/jwooley/archive/2007/09/04/linq-to-sql-compiled-queries.aspx

Linq to Sql Surprise Performance Hit
http://www.jdconley.com/blog/archive/2007/11/28/linq-to-sql-surprise-performance-hit.aspx

Deadlock in DB
http://www.codinghorror.com/blog/2008/08/deadlocked.html

SQL Server Identity Inserts (when doing batch import)

When importing mutliple tables from one DB to another and you have tables with 'identity' defination, when you try to import, you would face error, or if no error its event worst, the impor will screw up your identity or reset it, so below article tell you how to solve it, simple remember to check 'enable identity insert' in the wizard

http://blog.sqlauthority.com/2010/04/04/sql-server-enable-identity-insert-import-expert-wizard

Generate SQL Inserts

Found this quite useful when we need to generate insert statement on MS SQL, Below is a simple and easy version.

http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx

This is much more Extensive:-
http://brantinteractive.com/2005/11/13/generate-insert-statement-for-sql-server-tables/

LINQtoSQL SQL Open Connection Issue

I have seems iDataReader, not properly closed, hogging up SQL Connection pool and not releasing the connection cause my app to run out of connection poll (when it hits 100). But I have never had the problem with LinqToSQL, im not that technical, so with an optimistic attitude, assumed that, its a new Data Access Function, thus connection.close is automatically done.

Sadly i was Wrong, While running OPENSTA stress Test on the App, with 200 users, my app stopped responding at the 100th user. (my connection poll is Maxed out at 100 user). I was puzzled, what could be the cause of the problem?! Couldnt be the LINQtoSQL.

But the reality, IT was my LINQtoSQL statement that didnt close the SQL Connection!

Solution Seems to be

using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = from c in ctx.Customers
                    select c;

        var enumerator = query.GetEnumerator();
      }

This will Properly 'DISPOSE' the datacontent and ultimately CLOSE the connection.

Got it from Mike Taulty's Blog

As the post Says: "I like simple things so I'm going to stick with my basic law of "If it's disposable, dispose of it" :-)"

Also FAQ on LINQToSQL Connections
Linq to SQL DataContext Lifetime Management

SQL Joins (with diagrams)

Was looking for how to do  Left (inner join) excluding the intersection part, and i find that 'Coding Horror' Blog has the best explanation and Solution for me

Coding Horror: A Visual Explanation of SQL Joins

MS SQL: How to get the System Date

Yep, totally forgot about this, wish they could have the same function as .NET (datetime.now) but they dont,
but simply its just: GETDATE()

Few other ways are mentioned in this Blog entry: Journey to SQL Authority with Pinal Dave

SQL : Attached a mdf file (without log file)

Yep, i have a mdf file (from sample code) that i need to attach to my SQL Svr 05, but i cant as i do not have log file,
seems the easiest way to settle this is by this SQL command:-

USE [master]
GO
CREATE DATABASE [pagingTest] ON
( FILENAME = N'g:\SampleCode\CustomPaging\GridView Paging\App_Data\Database.mdf' )
FOR ATTACH
GO