sql server - SQL Parameter sniffing is it possible that recompile does not help but local variables do -


i using sp_executesql pass complicated selection few parameters. slower doing way taking out of stored procedure , declaring variables.

i have seen many questions sql parameter sniffing , scenario sounds case. after calling dbcc freeproccache or amending outer select option (recompile) still uses different , inefficent execution plan compared writing same query outside stored procedure.

however still using stored procedure setting copies of parameters local variables use efficent execution plan.

does scenario rule out sql parameter sniffing cause? becuase recompile query surely there no pre existing execution plan uses. if possible other reasons behaviour?

just give idea of sql query can see below (messy generated via entity framework). fast query when put sp_executesql proc variable taken out , put in parameters generates inefficient execution plan 

 declare    @p__linq__0 int = 2032 ,@p__linq__1 uniqueidentifier = '8cc161fc-b8de-4746-ba4f-62fa55df26de' ,@p__linq__2 uniqueidentifier= '8cc161fc-b8de-4746-ba4f-62fa55df26de', @p__linq__3 uniqueidentifier= '8cc161fc-b8de-4746-ba4f-62fa55df26de', @p__linq__4 uniqueidentifier= '8cc161fc-b8de-4746-ba4f-62fa55df26de', @p__linq__5 int = 6771  select  [limit1].[useridvalue] [useridvalue]   (select [extent1].[id] [id]     [dbo].[request] [extent1]     ([extent1].[instanceid] = @p__linq__0) , ([extent1].[deleteddate] null) , (( exists (select          1 [c1]         ( select              [extent2].[teamid] [teamid],              [extent2].[hasupdateaccess] [hasupdateaccess]             [dbo].[requesttypeteam] [extent2]             [extent1].[requesttypeid] = [extent2].[requesttypeid]         )  [project1]         ([project1].[hasupdateaccess] = 1) , ( exists (select              1 [c1]             [dbo].[userteam] [extent3]             ([project1].[teamid] = [extent3].[teamid]) , ([extent3].[userid] = @p__linq__1)         ))     )) or (([extent1].[insertuserid] = @p__linq__2) , ( exists (select          1 [c1]         ( select              [extent4].[teamid] [teamid],              [extent4].[hascreatoraccess] [hascreatoraccess]             [dbo].[requesttypeteam] [extent4]             [extent1].[requesttypeid] = [extent4].[requesttypeid]         )  [project4]         ([project4].[hascreatoraccess] = 1) , ( exists (select              1 [c1]             [dbo].[userteam] [extent5]             ([project4].[teamid] = [extent5].[teamid]) , ([extent5].[userid] = @p__linq__3)         ))     ))) or ( exists (select          1 [c1]         [dbo].[requestteam] [extent6]         ([extent1].[id] = [extent6].[requestid]) , ([extent6].[teamid] in (3147, 3165))     )) or ( exists (select          1 [c1]         ( select              (select                  count(1) [a1]                 [dbo].[requestcontroldata] [extent8]                 ([project8].[id] = [extent8].[controlid]) , ([extent8].[requestid] = [extent1].[id]) , ([extent8].[useridvalue] = @p__linq__4)) [c1]             ( select                  [extent7].[id] [id]                 [dbo].[control] [extent7]                 ([extent7].[requesttypeid] not null) , ([extent1].[requesttypeid] = [extent7].[requesttypeid]) , ([extent7].[deleteddate] null) , ([extent7].[isauthorisation] = 1)             )  [project8]         )  [project9]         [project9].[c1] > 0     ))) , ( not ([extent1].[statusid] in (1071))) , ( not ([extent1].[requesttypeid] in (1215)))) [filter11]  outer apply  (select top (1)      [extent9].[controlid] [controlid],      [extent9].[useridvalue] [useridvalue],      [extent10].[id] [id],      [extent10].[sharedcontrolid] [sharedcontrolid]      [dbo].[requestcontroldata] [extent9]     inner join [dbo].[control] [extent10] on [extent9].[controlid] = [extent10].[id]     ([filter11].[id] = [extent9].[requestid]) , (([extent10].[sharedcontrolid] = @p__linq__5) or (([extent10].[sharedcontrolid] null) , (@p__linq__5 null     )))      )      [limit1]

i think problem design rather data, multiple sub-queries more expensive or operator

i rather use multiple table variables , left outer joins


-

Comments

Post a Comment

Popular posts from this blog

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more