Printing next 5 Sunday, Part-2

Read Prev Post : Printing next 5 Sunday, Part-1

Today I got a mail from my community friend Pathik, saying the alternative way to find next 5 sunday.”

I would like to share the same.

Problem Statement was: “I want a list of next five Sunday, how I can achieve this through T-SQL”

His approach towards solution was through CTE, let see the solution

declare @dt datetime

declare @howMany int = 5

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

with    cte

          as ( select   0 as number

               union all

               select   number + 1

               from     cte

               where    number < (@howMany - 1)

             )

    select  DATEADD(d, 7 * number, @dt) as [date],

            DATENAME(dw, DATEADD(d, 7 * number, @dt)) as Weekdays

    from    cte

OPTION  ( MAXRECURSION 1000 )

date                    Weekdays

----------------------- ------------------------------

2014-07-06 00:00:00.000 Sunday

2014-07-13 00:00:00.000 Sunday

2014-07-20 00:00:00.000 Sunday

2014-07-27 00:00:00.000 Sunday

2014-08-03 00:00:00.000 Sunday

Lets dissect the code and understand each statement.

I am dividing the compete query into 3 part

Part 1 :

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

Here if you see, this piece of code assigns date of upcoming Sunday. We are bifurcating the line

select datediff(wk, 0, getdate())

-- this will give the total number of week from 1900-01-01 to todays date.

Then he added those number of week to the first date of datetime that is it add number of week to 1900-01-01.

So now the date is first day of this week.

select dateadd(wk, datediff(wk, 0, getdate()), 0)

-- this will gives the first day of the current week

Then we are adding 6 to the date we got in previous step, for finding the first Sunday.

Select   DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0))

--it gives the upcoming sunday of current week

Part2:

Now I am taking the CTE written to get the series of incremental numbers starting from 0 to number passed as parameter

with    cte

          as ( select   0 as number

               union all

               select   number + 1

               from     cte

               where    number < (@howMany - 1)

             )

It’s a Recursive CTE written, where he is trying to generate the series of 0,1,2,3…..@howMany.

Invocation of Anchor: select   0 as number This statement will execute once only and act as a anchor for 1st iteration of execution.

Recursive innovation of routine:

select   number + 1

               from     cte

               where    number < (@howMany - 1)

And each time it executes the result form a anchor for next iteration.

Termination: The iteration will go till the condition matches, i.e  where    number < (@howMany - 1)

This is the termination condition.

Now if you query this CTE it will produce the result

declare @howMany int = 5;

with    cte

          as ( select   0 as number

               union all

               select   number + 1

               from     cte

               where    number < (@howMany - 1)

             )

select * from cte

number

-----------

0

1

2

3

4

Part 3

select  DATEADD(d, 7 * number, @dt) as [date],

            DATENAME(dw, DATEADD(d, 7 * number, @dt)) as Weekdays

    from    cte     

So it is just just adding 7 days to the first Sunday which we derive in the first step and displaying the name of the day.

Why coders are happy; because they are ignorant creature! J

 

Post Reference: Vikram Aristocratic Elfin Share

Parameter Caching: Parameter Sniffing with Adhoc Select Query

Read Previous Post: Plan Caching VI - Forced Parameterization in Adhoc Queries

Here we will see, how a query makes a difference in Execution plan when it has Parameter passed in where predicate. Whenever you are firing any query the plan is generated and Cached in Memory, but it doest not store the context of the execution i.e (plan based on Parameter passed).

Sometime when we use parameter to pass in where condition, the actual value of parameter is not known at compile time due to which the optimal Execution plan is not selected by SQL Optimizer, this is because query is compiled before actual value of parameter is known.

Lets check out with a small practical

create table ParameterSniffingDemoTB

(id int identity(1,1) primary key,

col2 datetime,

col3 numeric(28,10) not null)  

Command(s) completed successfully.

Lets insert some rows in the table

declare @i int = 0

begin tran

while @i < 50000

begin  insert into ParameterSniffingDemo_TB(col2,col3)

     select '20140616', 1000*rand()

     set @i=@i+1

end  

set @i=0

while @i < 5

begin

     insert into ParameterSniffingDemo_TB(col2,col3)

     select '20140617', 1000*rand()

     set @i=@i+1

end

commit tran 

Now since our table is populated with data, Lets create a non clustered index on col2

create nonclustered index [ix_col2]

on [ParameterSniffingDemo_TB] ([col2] asc)

Command(s) completed successfully.

Non Clustered Index is on place now. Lets query on the basis of col2 and see whether Non Clustered index participate in Execution plan.

select SUM(col3) FROM ParameterSniffingDemo_TB WHERE col2='20140617'

Here we can see Non cluster Index participated in the execution plan.

Now lets declare a parameter variable and check whether it will use the existing Non Clustered index.

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate

Here we can see the Non clustered index is not taken up by Optimizer while creating Plan for the Query.

Lets check the comparative cost of the query, for this we are running both query in a batch

--First Query with out Parameter Variable

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2='20140617'

--Second Query with local Parameter Variable

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate  --option(recompile)

Here we can see the Cost of query with value as parameter choose optimal plan with just 6% of total to execute whereas Query with Parameter variable choose non optimal plan.  

This problem is called Parameter Sniffing.

Workaround Solution :- Option (Recompile) Hint

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate  option(recompile)

It’s your smile which makes me smile, let me smile and do my duty towards my code, Keep smiling my dear! J

 

Post Reference: Vikram Aristocratic Elfin Share

RID Lookup with HEAP

RID Lookup is a physical Query Execution operator which operates on HEAP Table i.e A table without Clustered Index on it. It comes into picture when non clustered index does not contain all the data requested by a query, so to get the addition data it goes to heap (only in case when you don’t have clustered index on your table) .  RID Lookup always accompanied by Nested Loop Join operator.

Lets stimulate the RID lookup operator in Execution plan.

Here we are creating a table with col2 as datatime datatype which we will use it in where predicate.

CREATE TABLE RIDLookup_Demo_TB

(

    id   INT  IDENTITY (1, 1),

    col2 DATETIME        ,

    col3 NUMERIC (28, 10) NOT NULL

);

Command(s) completed successfully.

Our table is ready to get some data into it. Here we are trying to insert 5 Lkh data where date column i.e. Col2 has value 16June2014 and 5 record with col2 value 16June2014 values.

DECLARE @i AS INT = 0;

BEGIN TRANSACTION;

WHILE @i < 500000   

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140617',

               1000 * rand();

        SET @i = @i + 1;

    END

COMMIT TRANSACTION;

Now since our data are inserted into table, lets query the table to see the plan

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

Here we saw, since the table has no clustered index (Primary Key), Table Scan aka Heap Scan comes into picture, but we are concern with RID Lookup. Which is associated with Non Clustered index on table which has no Clustered index define.

it’s time to create Non Clustered Index on col2 of table.

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Now since NonClustered Index is in place, lets fire the same query to see the RID Lookup in the Execution Plan.

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

Work around solution

When I see RID Lookup, I always think, why the table has no clustered index on it. So the solution is create Clustered Index on the table.

If you have clustered index on table, the RID lookup will changed to Key Lookup, again when I saw Key Lookup in execution, I think why the non clustered index not covered by required column asked in select clause.

Blend my love with code, and you will find me happy! J

 

Post Reference: Vikram Aristocratic Elfin Share