Skip to main content

Parameter Sniffing and Reporting service through Visual studio

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 which is realy important

Comments

Anonymous said…
Hi Deepak - I have a project I would like to talk to you about
August 10, 2009 at 9:48 AM
Post a Comment

Popular posts from this blog

MS interview

Algorithm Collection Q1: How would you find a cycle in a linked list? Try to do it in O(n) time. Try it using constant amount of memory. Q2: Given a history of URLs, how would you determine if a particular URL had been seen before? Q3: Since pages can have multiple URLs pointing to them, how can you make sure you've never seen the same CONTENT before? Q4: Come up with the plan on how to traverse a graph, as well as to quickly determine if a given URL is one of the million or so you've previously seen. Q5: The Web can be modeled as a directed graph. Come up with a graph traversal algorithm. Make the algorithm non-recursive and breadth-first. Q6: Write a function to print the Fibonacci numbers Q7: Write a function to print all of the permutations of a string. Q8: Design a memory management scheme. Q9: Give a one-line C expression to test whether a number is a power of 2. Now implement it without using loops. Q10: How can I swap two integers in a single l...
111 comments
Read more

Amazon interview question Collection

General Questions and Comments [more] generic questions on software development -------------------------------------------------------------------------------- What is the complexity of this algorithm -------------------------------------------------------------------------------- What is your stronger language (Java or C#) -------------------------------------------------------------------------------- Lot of design questions and OOAD stuff 2 -------------------------------------------------------------------------------- Programming questions asked were more or less same as the ones listed at this site -------------------------------------------------------------------------------- more architectural view about solve problem capability. I think the intervier was more realistic than the other two . Not just because he recommend to 2nd interview, since I also have the experience with recuriting other employees in the past. I felt the potenial is more than anything in work. Coding is j...
184 comments
Read more

Few Programming Interview Tips and Tricks ( Amazon and MicroSoft )

Area Number One: Coding The candidate has to write some code. Give them a coding problem that requires writing a short, straightforward function. They can write it in whatever language they like, as long as they don't just call a library function that does it for them. It should be a trivial problem, one that even a slow candidate can answer in 5 minutes or less. (If the candidate seems insulted by the thought of having to get their hands dirty with a trivial coding question, after all their years of experience, patents, etc., tell them it's required procedure and ask them to humor you. If they refuse, tell them we only interview people who can demonstrate coding skills over the phone, thank them for their time, and end the call.) Give them a few minutes to write and hand-simulate the code. Tell them they need to make it syntactically correct and complete. Make them read the code to you over the phone. Copy down what they read back. Put it into your writeup. If th...
38 comments
Read more