hab eine tolle Möglichkeit für (zumindest ein paar Anwendungsfälle) das direkte Insert (ohne vorheriges Create #TempTable) aus einer Stored Procedure heraus gefunden:
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure

select * into #TempTable
from OPENROWSET ('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'Exec dbo.USP_TestProc')

legt automatisch einen Temptable an mit den Spalten aus Ergebnis von exec dbo.USP_TestProc

allerdings einen Nachteil hat diese Sache; man kann keine Parameter übergben - bei Parametern bleib einem also nichts anderes übrig, als

create #TempTable ( spalten ....)
insert into #TempTable
exec dbo.USP_TestProc

 

 

Scalar Valued Functions sind ja super toll zu handhaben, aber ein Problem haben sie. Es ist keine parallele Verarbeitung mehr möglich. - Leider; der SQL Server schaltet sofort auf 1 Prozessor, sobald eine Scalar Valued Function beteiligt ist.

In der Folge werde ich zeigen, dass unter Umständen eine Inline Tabled Valued Function als Nested Loop oder mittels cross apply verwendet besser und schneller ist, als die Scalar Valued Function.

Aber selbstverständlich hat die Scalar Valued Function auch ihre Daseinsberechtigung! - ich will diese nicht schlecht machen.

Als Beispiel folgende 2 Aufrufe - SVF/TVF findet man hier

Verwendung von Scalar Valued Function

select SalesOrderID, dbo.SVF_Ostersonntag (a.ModifiedDate) as Ostern
from Sales.SalesOrderDetail a
inner join Sales.SpecialOffer b on a.SpecialOfferID = b.SpecialOfferID
inner hash /* hier hash, damit SQL-Server gezwungen wird parallel zu arbeiten */ join
  Production.Product d on a.ProductID = d.ProductID


Verwendung von Table Valued Function

 

select SalesOrderID, (Select Ostersonntag from dbo.TVF_Ostersonntag (a.ModifiedDate)) as Ostern
from Sales.SalesOrderDetail a
inner join Sales.SpecialOffer b on a.SpecialOfferID = b.SpecialOfferID
inner hash join Production.Product d on a.ProductID = d.ProductID

bzw.

select SalesOrderID, c.Ostersonntag as Ostern
from Sales.SalesOrderDetail a
inner join Sales.SpecialOffer b on a.SpecialOfferID = b.SpecialOfferID
inner hash join Production.Product d on a.ProductionID = d.ProductID
cross apply dbo.TVF_Ostersonntag (a.ModifiedDate) c

und der Erfolg:

CPU time = 5657 ms,  elapsed time = 6162 ms.

CPU time = 4467 ms,  elapsed time = 4312 ms.

und dann noch die beiden Execution Pläne, die es noch deutlicher zeigen

 

 

 und hier folgt selbstverständlich noch der Aufruf der SVF im Execution Plan (aber das ist ja nicht weiter interessant, da es hier um die nicht vorhandene Parallelität geht)

 

 

 hier zu sehen, die Parallelverarbeitung

Die Erklärung dafür (zumindest, warum es bei einer TVF noch parallel geht), ist, dass der SQL-Server die TVF komplett auflöst und den Syntax als ganz "banales" Select hinschreibt. Mittels set showplan_text on mache ich mir das sichtbar:

|--Compute Scalar(DEFINE:([Expr1019]=CASE WHEN ((((21)+((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))-((((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))/(29)+((((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))/(28)-(((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))/(29))*((datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))/(11))))+((7)-((((21)+((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))-((((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))/(29)+((((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))/(28)-(((19)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))+(((15)+((3)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(3))/(4))-((8)*(datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))/(100))+(13))/(25)))%(30))/(29))*((datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a].[ModifiedDate],0))%(19))/(11))))-((7)-((datepart(year,CONVERT_IMPLICIT(date,[AdventureWorks].[Sales].[SalesOrderDetail].[ModifiedDate] as [a..


|--Parallelism(Gather Streams)

 

Für eine Feiertagsermittlung ist nun einmal der Ostersonntag das Um und Auf. Doch wie kommt man auf den?

Wikipedia schreibt dazu:

Das Osterdatum ist das Datum für den Ostersonntag im christlichen Jahreskreis. Auf Grund neutestamentlicher Überlieferung wurde der Ostertermin festgelegt als der erste Sonntag nach dem Frühlingsvollmond. Vereinbartes Datum für den frühesten Frühlingsvollmond ist der 21. März, so dass der früheste Ostersonntag auf den 22. März fällt. Spätestes Osterdatum ist der 25. April.
Quelle: http://de.wikipedia.org/wiki/Osterdatum

Ok, das hilft nicht viel, wenn man nicht weiß, wie man den Frühlingsvollmond berechnet. - Braucht man aber nicht, denn es gibt von Gauß hat 1816 eine Formel aufgestellt, welche recht einfach den Ostertermin bestimmt (leider gibt es ein paar Ausnahmen, welche er zwar benannt hat, aber nicht in die Formel einpflegte [Anmerkung: so wie ich es verstanden hab]). Doch ein Herr Lichtenberg hat dann 1997 die Formel dahingegend adaptiert, die da lautet:

1. die Säkularzahl:

K(X) = X div 100

2. die säkulare Mondschaltung

M(K) = 15 + (3K + 3) div 4 - (8K + 13) div 25

3. die säkulare Sonnenschaltung

S(K) = 2 - (3K + 3) div 4

4. den Mondparameter

A(X) = X mod 19

5. den Keim für den ersten Vollmond im Frühling

D(A,M) = (19A + M) mod 30

6. die kalendarische Korrekturgröße

R(D,A) = D div 29 + (D div 28 - D div 29) (A div 11)

7. die Ostergrenze

OG(D,R) = 21 + D - R

8. den ersten Sonntag im März

SZ(x,S) = 7 - (X + X div 4 + S) mod 7

9. die Entfernung des Ostersonntags  von der Ostergrenze (Osterentfernung in Tagen)

OE(OG,SZ) = 7 - (OG - SZ) mod 7

10. das Datum des Ostersonntags als Märzdatum (32. März = 1. April usw.)

OS = OG + OE

Soviel zur Theorie. [Anmerkung: wichtig ist, immer in ganzen Zahlen zu rechnen - INT]
... und daraus entsteht dann folgende SVF bzw. TVF

create function dbo.TVF_Ostersonntag (@datum as date)
returns table
as
return
(
select case when OS < 32
            then cast (right('000' + cast (X as varchar(4),4) + '-03-' + right('0'+cast (OS as varchar(2)),2) as date)
            else cast (right('000' + cast (X as varchar(4)),4) + '-04-' + right('0'+cast (OS-31 as varchar(2)),2) as date)
       end Ostersonntag
from (
  select  OS = OG + OE
         ,X
  from (
    select  OE = cast (7 - (OG-SZ) % 7 as int)
           ,X
           ,OG
    from (
      select  SZ = cast (7 - (X + X / 4 + S) % 7 as int)
             ,X
             ,OG = cast (21 + D -R as int)
      from (
        select  R = cast (D / 29 + (D / 28 - D / 29) * (A / 11) as int)
               ,X
               ,S
               ,D
        from (
          select  D = cast ((19*A + M) % 30 as int)
                 ,X
                 ,S
                 ,A
          from (
            select  A= cast (X % 19 as int)
                   ,S= cast (2 - (3 * K + 3) / 4 as int)
                   ,M= cast (15 + (3 * K + 3) / 4 - (8 * K + 13) / 25 as int)
                   ,X
            from (
              select  K = cast (X/100 as int)
                     ,X
              from (
                select  cast (year (@datum) as int) X
                   ) a
                 ) a
               ) a
             ) a
           ) a
         ) a
       ) a
     ) a
)
GO

und das Ganze noch als Scalar Valued Function:

CREATE FUNCTION [dbo].[SVF_Ostersonntag](@datum as date)RETURNS date
AS
BEGIN
  declare @Ostersonntag date
 
  declare @A int , @K int , @M int , @D int , @S int , @R int , @OG int , @SZ int , @OE int , @OS int
  declare @X int
  set @X = cast (year (@datum) as int)


  set @K = @X/100
  set @M = 15 + (3 * @K + 3) / 4 - (8 * @K + 13) / 25
  set @S = 2 - (3 * @K + 3) / 4
  set @A = @X % 19
  set @D = (19 * @A + @M) % 30
  set @R = @D / 29 + (@D / 28 - @D / 29)*(@A / 11)
  set @OG = 21 + @D - @R
  set @SZ = 7 - (@X + @X / 4 + @S) % 7
  set @OE = 7 - (@OG - @SZ) % 7
  set @OS = (@OG + @OE)


  if @OS < 32
  begin
    set @Ostersonntag = right('000' + cast (@X as varchar(4)),4) + '-03-' + right('0'+cast (@OS as varchar(2)),2)
  end
  else begin
    set @OS = @OS - 31 
    set @Ostersonntag = right('000' + cast (@X as varchar(4)),4) + '-04-' + right('0'+cast (@OS as varchar(2)),2)
  end


  return (@Ostersonntag)
end
GO

Wen es jetzt interessiert, warum ich eine TVF und eine SVF gemacht hab, hier kannst du genaueres darüber erfahren.

 

 

Durchlaufzeit von SQL Abfragen hängen massgeblich von aktuellen Statistiken zusammen. Sind diese alt und somit falsch, kann es zu unerwarteten Zugriffen kommen (z.B. Nested Loop)

Da das Aufbauen von Statistiken mitunter (große Tabellen) auch sehr lange brauchen kann:
Idee: 2 Tables mit Union (1. beinhaltet "alte" Daten mit optimalen Statistiken vorhanden, 2. die "aktuellen" Daten -> kleiner Table, immer wieder update Statistics machen braucht wenig Zeit)

Wie schaut es in einem solchen Fall mit dem Zugriff aus?

hier einige Links, die ich im Zusammenhang mit diesem Thema besucht habe:
http://sqlcat.com/top10lists/archive/2008/02/06/top-10-best-practices-for-building-a-large-scale-relational-data-warehouse.aspx
http://msdn.microsoft.com/en-us/library/ms345599.aspx
http://blogs.msdn.com/b/craigfr/archive/2008/07/15/partitioned-tables-in-sql-server-2008.aspx
http://msdn.microsoft.com/en-us/library/ms187348(v=sql.90).aspx
http://www.developer.com/db/article.php/3622881/Basics-of-Statistics-in-SQL-Server-2005.htm

 

http://www.sqlservercentral.com/Forums/Topic697800-149-1.aspx