May 27, 2009 at 9:14 am
Max (5/27/2009)
3. Gail, I think RECOMPILE was available in 2005 and it would have been possible in 2k to use:CREATE PROC dbo.sp_TestProc @intPara1WITH RECOMPILE
AS...
Yes it was and yes it it. However, it would make not the slightest bit of difference.
In 2005, no matter now much you recompile a 'catch-all' query it will get a similar plan. Generic, would work no matter what params were passed and generally very poor. In 2008 the optimiser was tweaked and now it recognises that, if there's a 'catch-all' query with the recompile option, it can generate a plan that's optimal for that particular set of parameters.
2000 I believe is the same, though I don't have a 2000 server lying around to test on. I have tested on 2005 and 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2009 at 9:48 am
Max (5/27/2009)
There's shouldn't be any need to validate the second part of the OR clause when the first condition has been met. I would be perplexed if this was the case and the second part of the OR clause would negate the true condition, but that's what the AND predicate is for.
The answer is "it depends". Shortcircuiting for logical operators is a feature available in some programming language (Java, C, C++, C# and many others) and completely unknown in some others (Visual Basic and Pascal, for instance).
If you read the entire thread, you will see that generally speaking it's not predictable what will happen on shortcircuiting, except for "tautological" expressions or expressions that can be evaluated before the query is executed, that means at compile time.
For expressions that have to be verified against data, the optimizer can choose paths that require the evaluation of both conditions independently, or decide to evaluate the second condition instead of the first one.
-- Gianluca Sartori
May 27, 2009 at 9:55 am
Max (5/27/2009)
Sometimes it is the job of the dba to make sure that production servers run smoothly and that code being introduced to prod doesn't adversely effect the server or performance and they then have to re-write perfectly good code.
I see your point. Challenging job, isn't it?
-- Gianluca Sartori
May 27, 2009 at 10:07 am
Short-circuiting in this case has nothing to do with a programming language but rather how the sql query optimizer was coded. If it was designed/programmed to do short-circuiting then it will. If it wasn't, then it won't. Then you can throw in the myriad of situations where boolean can be short circuited and each of them could be coded differently in the optimizer (which I think may be the case - some do, most don't).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 27, 2009 at 2:07 pm
Topical and timely words to the wise from Bart Duncan (someone who REALLY knows what is going on with SQL Server) and isn't just a pretender like myself! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 27, 2009 at 6:57 pm
TheSQLGuru (5/27/2009)
Like the style. Have also used IF blocks to define logic based on parameter conditions but generally stopped as all non-cached statements appear to be optimised, beats labels though.
GilaMonster (5/27/2009)
Yes it was and yes it it. However, it would make not the slightest bit of difference.In 2005, no matter now much you recompile a 'catch-all' query it will get a similar plan. Generic, would work no matter what params were passed and generally very poor. In 2008 the optimiser was tweaked and now it recognises that, if there's a 'catch-all' query with the recompile option, it can generate a plan that's optimal for that particular set of parameters.
2000 I believe is the same, though I don't have a 2000 server lying around to test on. I have tested on 2005 and 2008.
Recompile query hint in 2005 and 2008 looks like a winner.
Max
May 27, 2009 at 8:47 pm
Max (5/27/2009)
...
GilaMonster (5/27/2009)
Yes it was and yes it it. However, it would make not the slightest bit of difference.In 2005, no matter now much you recompile a 'catch-all' query it will get a similar plan. Generic, would work no matter what params were passed and generally very poor. In 2008 the optimiser was tweaked and now it recognises that, if there's a 'catch-all' query with the recompile option, it can generate a plan that's optimal for that particular set of parameters.
2000 I believe is the same, though I don't have a 2000 server lying around to test on. I have tested on 2005 and 2008.
[/quote-0]
Recompile query hint in 2005 and 2008 looks like a winner.
No. What Gail is trying to tell you is that the RECOMPILE hint does not have the desired effect for this problem in SQL Server 2005. Even forcibly recompiling it yourself will not work. SQl Server 2008 fixes that problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2009 at 1:16 am
TheSQLGuru (5/27/2009)
Thanks for sharing this resource, I found it very valuable. It also has the plus of treating the topic without "religion war" positions, focusing on what really matters.
He also states:
The only situations where you should use queries like either of those shown above is when (a) you don't care about the performance of the query, or (b) you can guarantee that the table will only contain a handful of rows so that a table scan-based plan will never be noticeably slower than a seek-based plan.
that is exactly what I usually do when I have to deal with "dynamic search conditions" (sorry Gail, I dropped your "catch-all").
Basically this is the concept I (poorly) tried to express with this post.
Interesting read.
-- Gianluca Sartori
May 29, 2009 at 6:28 am
RBarryYoung (5/27/2009)
Max (5/27/2009)
...
GilaMonster (5/27/2009)
In 2005, no matter now much you recompile a 'catch-all' query it will get a similar plan. Generic, would work no matter what params were passed and generally very poor. In 2008 the optimiser was tweaked and now it recognises that, if there's a 'catch-all' query with the recompile option, it can generate a plan that's optimal for that particular set of parameters.
2000 I believe is the same, though I don't have a 2000 server lying around to test on. I have tested on 2005 and 2008.
[/quote-0]
Thanks Barry, I was referring to my statement above on the usefulness of query hints in not recompiling the entire proc as WITH RECOMPILE in 2k (especially in relation to large IF blocks). Thanks for the clarification anyway.
Max
June 1, 2009 at 4:00 am
RBarryYoung (5/27/2009)
SQl Server 2008 fixes that problem.
It won't fix it any more: take a look at this entry on connect.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=386810
It looks like Microsoft has announced that for SQL 2008 SP1 they will revert to the old behaviour and maybe they will fix it in SP2.
-- Gianluca Sartori
June 1, 2009 at 4:04 am
I got this info from the most complete article on the "catch-all" topic I had the chance to come across.
I think you might be interested in taking a look:
http://www.sommarskog.se/dyn-search-2005.html
-- Gianluca Sartori
June 1, 2009 at 10:44 am
Hmm. Nice catch Gianluca.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 1, 2009 at 5:12 pm
RBarryYoung (6/1/2009)
Hmm. Nice catch Gianluca.
No pun intended, right Barry? 😉
Erlands' study reminds me of constructing sql statments in VBA before ADO based on user parameters and the ever increasing desire to "search for it all" in one location. The logical succession, with the inclusion of extended properties, was to define user entities (non system meta) in the database which would be compiled to return the data, with the aid of customised procs making extensive use of meta objects and the heirarchical mapping of fk and pk constraints, to represent the query entered by the user from the translated user-friendly catalogue. But that would take a "special" type of developer.
Max
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply