1

Topic: Sectioning and variable in request

Good evening!
SQL 2008 r2. There is big partitioned on quarters (field DDATE) a table fcStock without indexes
If to write request deleting the last 10 days for example
delete from fcStock where ddate> ' 20171002 ' all is processed for seconds and there is a reversal to the necessary section
If beforehand to declare a variable:
DECLARE @DT DATETIME
SET @DT = CONVERT (VARCHAR (8), GETDATE ()-10, 112)
delete from fcStock where ddate>@DT handling goes some minutes and there is a scanning of all table.
In what here there can be a dirty trick?

2

Re: Sectioning and variable in request

mishgan1;
The dirty trick in that at usage of a variable the optimizer not at all does not know, what amount of the data gets under a condition for at the moment of compilation of request variable value is not known also the server builds the plan "for all occasions", calculating thus for "the worst" variant - "all records get to handling".
At constant instructions - the optimizer quite to itself understands that the data necessary to it - in one section and for obtaining of the right answer it is absolutely optional to it to scan all table, it is possible will be restricted  one section.
..
About "peeping" of variables I will not speak, for I do not remember from what version they appeared.

3

Re: Sectioning and variable in request

Kind  - Eh;
And how ? To study ?))

4

Re: Sectioning and variable in request

option (recompile) still it is possible to study

5

Re: Sectioning and variable in request

mishgan1;

DECLARE @DT DATETIME
SET @DT = CONVERT (VARCHAR (8), GETDATE ()-10, 112)
delete from fcStock where ddate>@DT option (recompile)

Or option (optimize for unknown) - in a case if instruction recompilation will superimpose costs for productivity.

6

Re: Sectioning and variable in request

mishgan1 wrote:

And how ?

delete from fcStock where ddate>@DT option (recompile)

7

Re: Sectioning and variable in request

The full scanning happens, if types of function argument of sectioning and a variable do not coincide. Check up just in case.

8

Re: Sectioning and variable in request

Vladislav Kolosov;
Yes, this remark partly solved a problem. All thanks!