Monthly Archives: November 2011

Parameter sniffing in Sql server

Hi All,

I work for a project which involves very huge data reporting using SSRS. Business users reported a bug which was very difficult to analyze.
Whenever there was a new report added to report server and a certain user requests report data for smaller territory like New Jersey to see his sales figures for his territory. The report renders very quick. But whenever a user requests for a bigger territory like Texas it takes more time almost 10 times (phew!).

when i started looking into the issue and found something interesting which i have only read in my academic days.

SP was like this.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @TerritoryId
END

Even though all looks fine, there is a catch here. @TerritoryId is the input parameter which is used directly into the where condition. So here is what happens with Sql server.

1. developer executes the stored proc. Sql server compiles the SP and save it.
2. User calls the SP with certain parameters (say TerritoryId which is smaller). it sure will have less data.
3. Sql server creates an execution plan which is optimized for this input parameter (smaller territory) and saves it.
4. Next time a user call the same SP for a bigger territory (more data). Sql server still uses the same execution plan which was saved earlier (optimized for small data). It takes unusually more time.

Solution to this problem (called Parameter sniffing) is very simple.

1. Assign the input parameter to a local variable.
2. use that local variable in the sql query.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
DECLARE @i_TerritoryId int

SET @i_TerritoryId = @TerritoryId

SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @i_TerritoryId
END

Even though it all looks one and the same thing, it surely make a difference in certain data intensive queries.

I hope it helps.

Praveen

Advertisements

Jquery event binding – jquery live api

Hi All,

Recently i faced an issue while developing a Asp.Net MVC3.0 application. On a link click i was trying to add new html to the existing DOM. For example on click of a link i was adding some new html tags to the existent DOM. Here is the code i wrote to make an ajax call on link click.


$(".editanchor").click(function () {

$.ajax({
// set the attributes and call an action method
});
});

It all worked fine but strangely when i click the link next time nothing happens. Then i realized the mistake i made while binding the click event to a handler.

As per jquery documentation Live() method should be used to bind the event to a callback if we are modifying the DOM structure during our page reload (partial or full) so that it would retain the binding next time you click the link.

Live(event, handler) : Attaches an event handler for all elements which match the current selector, now and in the future.

so the correct way to bind an event to a handler in such condition would be

$(".editanchor").live('click', function () {

$.ajax({
// set the attributes and call an action method
});
});

There are few performance considerations we should be aware of

  • Since all .live() events are attached at the document element, events take the longest and slowest possible path before they are handled.
  • jQuery attempts to retrieve the elements specified by the selector before calling the .live() method, which may be time-consuming on large documents.

EDIT: Here is the updated comparison of various flavors of binding an event.

$(selector).live(events, data, handler);                // jQuery 1.3+
$(document).delegate(selector, events, data, handler);  // jQuery 1.4.3+
$(document).on(events, selector, data, handler);        // jQuery 1.7+

I hope it helps.