June 29, 2011 at 5:17 am
cpreager (6/29/2011)
So I think it could be an anomoly of the optimization, given I can't repeat the performance improvement in simpler conditions.
The optimizer does contain rules that match on >= and <= but not > and < in some cases. For example, using >= and <= can result in an on-the-fly index (spool) which may or may not help performance in different circumstances. So, yes, some logical equivalences can optimize differently. Sometimes your plan will run faster, sometimes slower. I would never recommend starting with tricks like this, but sometimes they can be necessary - especially with difficult to optimize queries. By the way, I don't necessarily fault the optimizer here, people write all sorts of rubbish against poor designs, fail to provide good information (e.g. statistics) to the optimizer, and *still* complain when the query plan executes slowly.
In parallel I have also found that these sort of calculations on getdate() are treated as deterministic when wrapped in a user-defined function, (UDF), (so calculated once for the query), but non-deterministic when explicitly used as above...
The optimizer is usually pretty good at picking up 'runtime constant' expressions, but there are, again, occasions where using a function or variable does happen to produce a better result. Gains usually come from a non-deterministic expression being evaluated once - which is not the same thing as saying the expression is treated deterministically - you are just obfuscating things so the optimizer sees a value as a constant. It is usually possible to rewrite the query using supported constructions to achieve the same result, so I am not a fan of using functions in this way. I'm not getting into a debate about it here, just sharing my opinion. I do hope your code documentation is extremely thorough - otherwise I pity future developers that have to maintain the code!
I know it's not what you meant to say, but I do want to emphasise that intrinsics like GETDATE are never deterministic - you were just saving a value and reusing it. To convince any doubters, there are three functions below, all non-deterministic.
CREATE FUNCTION dbo.F() RETURNS DATETIME WITH SCHEMABINDING
AS BEGIN RETURN GETDATE() END;
GO
CREATE FUNCTION dbo.ILF() RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT dt = GETDATE()
GO
CREATE FUNCTION dbo.MSF() RETURNS @T TABLE (dt DATETIME NOT NULL) WITH SCHEMABINDING
AS BEGIN INSERT @T (dt) VALUES (GETDATE()) RETURN END
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.F', N'FN'), 'IsDeterministic');
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ILF', N'IF'), 'IsDeterministic');
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.MSF', N'TF'), 'IsDeterministic');
GO
SELECT dbo.F();
SELECT * FROM dbo.ILF() AS i;
SELECT * FROM dbo.MSF() AS m;
GO
DROP FUNCTION dbo.F, dbo.ILF, dbo.MSF;
June 29, 2011 at 6:33 am
The optimizer does contain rules that match on >= and <= but not > and < in some cases.
Brilliant, thanks. Is there anywhere this is documented? That would be incredibly useful.
intrinsics like GETDATE are never deterministic ... you are just obfuscating things so the optimizer sees a value as a constant. It is usually possible to rewrite the query using supported constructions to achieve the same result, so I am not a fan of using functions in this way.
Agree completely and am equally uncomfortable with it, because eg, performance could collapse with a server update that tweaks the optimizer, hence looking for an explanation. Unfortunately the SQL is used in Business Objects filters, which means we can't declare variables, as we're just giving BOBJ chunks of SQL to stick together itself. Do you know of any other way to make it genuinely deterministic?
In almost all our complex queries we use values derived from datetime but requiring day granularity, (or month or even just year for YTD filters), so this would be fantastic.
Thanks enormously for your posts - they're an education
June 29, 2011 at 6:56 am
Unfortunately, no. Not much of the internal workings of the optimizer get documented - I guess part of the reason for that is that future changes would involve a deprecation cycle. This topic is a good one - I'll give some thought to a blog post on the topic and post a link here if/when I get to it.
June 29, 2011 at 11:23 am
That would be very useful indeed thank you. I understand the reticence for documenting this, but for those of us having to employ the techniques the information would be very useful in implementing them in a less supersticious manner.
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply