Docstoc

DAT405_ Transact-SQL Tips and Tricks - MSDN

Document Sample
DAT405_ Transact-SQL Tips and Tricks - MSDN Powered By Docstoc
					CREATE    FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
   WITH
   L0  AS(SELECT 1 AS c UNION ALL SELECT 1),
   L1  AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
   L2  AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
   L3  AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
   L4  AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
   L5  AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
   NumsAS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
          FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO
WITH Dates AS
(
   SELECT DATEADD(day, n-1, @from) AS dt
   FROM dbo.GetNums(DATEDIFF(DAY, @from, @to) + 1) AS Nums
),
Groups AS
(
   SELECT D.dt,
     DATEADD(day, -1*DENSE_RANK() OVER(ORDER BY D.dt), D.dt) AS grp
   FROM dbo.Projects AS P
     JOIN Dates AS D
      ON D.dt BETWEEN P.start_date AND P.end_date
)
SELECT MIN(dt) AS start_period, MAX(dt) AS end_period
FROM Groups
GROUP BY grp;
Default result set
  Best possible
    Default optimization performed by QO is for throughput
Cursor using Dynamic plan
  Not optimized for throughput
  May use a non-optimal plan
Cursor using Static plan
  Uses the same plan as the Default result set but always adds extra
  spooling operation at the end decreasing throughput
Can be optimized for low latency by changing query or using query hints


  It depends, did you get a “good” streaming plan?

Never as good as default result set
Always spools the entire result set
Experiences this problem
MARS?

Uses a restartable plan so does not use resources when not fetching rows
“The stability of result sets vary”

Example: (using default isolation level)
CREATE TABLE TestStability (RowNo INT NOT NULL PRIMARY KEY, Value INT NOT NULL);
      SELECT * FROM MyTable
      WHERE RowNo BETWEEN 1000 AND 4000
          May see changes that occurred after the first row was returned by the query
      SELECT * FROM TestStability
      WHERE RowNo BETWEEN 1000 AND 4000
      ORDER BY Value
          Will not be able to see changes that occurred after the first row was returned by the query
      SELECT Value, COUNT(*) FROM TestStability
      GROUP BY Value
          Will not be able to see changes that occurred after the first
          row was returned by the query
Default result set
  Unpredictable, may or may not be sensitive
      Because of network buffer
      Because of stop & go operators in exec. plan
Cursor using Dynamic plan
  Unpredictable, may or may not be sensitive
      Because of network buffer
      May degrade to static cursor
Cursor using Static plan
  Stable, always insensitive to data changes
  (as of full population of temp. table)
3
Run Time
(seconds)
      50
      45
      40
      35         Set-Based Original
      30
      25         Cursor-Based
      20
      15         Set-Based New 2
      10
       5
       0    Num Rows
Run Time    Effect of Partition Size
(seconds)
     120

     100
                                                Set-Based
                                                Set-Based
                                                Subquery/Join
      80                                        Subquery/Join
                                                T-SQL Cursor
      60
                                                T-SQL Cursor
      40                                        Windowing

      20

       0                               #Rows Per Partition
                                       (with 1000 partitions)
Run Time    Effect of Partition Size
(seconds)
     120

     100
                                               Set-Based
                                               Subquery/Join
      80
                                               T-SQL Cursor
      60

      40                                       CLR Data Reader

      20

       0                               #Rows Per Partition
                                       (with 1000 partitions)
www.microsoft.com/teched       www.microsoft.com/learning




http://microsoft.com/technet   http://microsoft.com/msdn
    Sign up for Tech·Ed 2011 and save $500
           starting June 8 – June 31st
http://northamerica.msteched.com/registration




             You can also register at the
    North America 2011 kiosk located at registration
             Join us in Atlanta next year

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:4/17/2013
language:Unknown
pages:39