April 6, 2009 at 6:35 pm
Matt Miller (4/6/2009)
With you two posting minor articles for responses, there's no way to adequately quote anything.
Without sarcasm tags or smileys, I'm not sure quite what to make of that Matt...?
Matt Miller (4/6/2009)
You pretty much covered the big topics. On the remaining topic of how a function might interfere with index handling: Even if the function returns a scalar, a UDF can pose a lot of challenges to the query engine, which may mean it is not capable of discerning whether a UDF is deterministic or not. Knowing whether something is deterministic is a big deal with UDF's since it can result in the outer query being slowed down a LOT when the query engine decides evaluate the function per row (if it doesn't think it's deterministic). The solution there is to force the compiler to treat deterministic UDFs as deterministic by specifying them to be schema-binding.
Good point - I missed the whole schemabinding / deterministic thing in my mini-article before 😉
Cheers,
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 8:06 pm
You pretty much covered the big topics.
What? We didn't so much as mention RBAR. I know that for a fact or otherwise Jeff Moden would have joined in by now. 😛
Looking back quite seriously, it's amazing how much you can talk about from such a short example. Just goes to show that if you want a question answered thoroughly SSC is the place to ask it. 😉 I am still impressed by how deep some discussions go, long after the OP has gotten his answer.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 7, 2009 at 7:35 am
Related to "parameter sniffing", query optimization, OPTION (RECOMPILE) and this type of "catch-all" query parameterization, check out Erland Sommarskog's "connect" entry at: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244298
Another good reason to upgrade to SQL Server 2008.
April 7, 2009 at 7:44 am
Paul White (4/6/2009)
Matt Miller (4/6/2009)
With you two posting minor articles for responses, there's no way to adequately quote anything.Without sarcasm tags or smileys, I'm not sure quite what to make of that Matt...?
Sorry - I'm a bit ragged these days with the hours I'm putting in at work combined with some school work I'm doing. I will emote more next time....:)
:w00t::cool::hehe::-P:-P:-D:-)
Better?:Whistling:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2009 at 3:05 pm
Matt Miller (4/7/2009)
Better?:Whistling:
Ooo! Much!! :smooooth:
Don't work so hard! 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2009 at 3:08 pm
JohnG (4/7/2009)
Related to "parameter sniffing", query optimization, OPTION (RECOMPILE) and this type of "catch-all" query parameterization, check out Erland Sommarskog's "connect" entry at: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244298Another good reason to upgrade to SQL Server 2008.
Agreed. In fairness though, I should point out that, in my personal experience, RECOMPILE is usually sufficient to produce a decent plan - better than without the hint anyway. I say usually advisedly, given Erland's example.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply