Today I had a very bad day. My friend left me a job that he estimated for 2 hours and Now I need to finish that ..There is no chance that i can write multiple pages sql Stored procedures /query and create report in 2 hours ..
It is going to take me at least 15 hours for everything .. Ok that is not the point .. the point .. I spent 5 hours to write all the query .. tested in Sql query manager and ran great ... Thought I was really good .. created a report layout .. added few dataset and ran the report using VS 2005 IDE .. Now the drama begins .. It took 5-10 mins to render report by VS 2005. I went to sql server machine.. checked for task manager .. CPU uses was more than 50% for that 10 mins time .WOW .. When I ran the same query in query manager .. it took less than 5 seconds .. I was upset .. restarted VS .. restarted computer .. nothing happened .. recompiled code .. redeployed .. Nothing ..Nothing at all.. I googled about it .. I am not only one.. If u have same problem ..
Google about "Parameter Sniffing " ..U will see many article about it .. In short the solution was: Suppose I have :
create proc newProc { @name varchar(200) }
as select name from nametable where name=@name
.. go
everytime u call it from outside with even NULL value or some value not equal to varchar(200) SQl server tried to create a brand new execution plan ..
and it takes forever to run the script if u have pretty complex sql..
Sol Very simple .
create proc newProc
{ @name varchar(200) }
@declare @newName varchar(200)
set @newname=@name as
select name from nametable where name=@newname .. go
done !! SQL just ignores to create execution plan and runs the last used plan ..
Ok ..so lesson of today:
There are lot of thing u dont know about .