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

Algorithm Interview questions Answers ( Microsoft, Google and Amazon )

1. Reverse a singly linked list // // iterative version // Node* ReverseList( Node ** List ) { Node *temp1 = *List; Node * temp2 = NULL; Node * temp3 = NULL; while ( temp1 ) { *List = temp1; //set the head to last node temp2= temp1->pNext; // save the next ptr in temp2 temp1->pNext = temp3; // change next to privous temp3 = temp1; temp1 = temp2; } return *List; } 2. Delete a node in double linked list void deleteNode(node *n) { node *np = n->prev; node *nn = n->next; np->next = n->next; nn->prev = n->prev; delete n; } 3. Sort a linked list //sorting in descending order struct node { int value; node* NEXT; } //Assume HEAD pointer denotes the first element in the //linked list // only change the values…don’t have to change the //pointers Sort( Node *Head) { node* first,second,temp; first= Head; while(first!=null) { second=first->NEXT; while(second!=null) { if(first->value value) { temp = new node(); temp->value=first->value; first-...
76 comments
Read more