Stored Procedure e DROP esplicito delle tabelle temporanee

Article
08/07/2013

Nelle attività di performance tuning sono molti i “dettagli” che possono aiutare a guadagnare tempo prezioso.

Anche poco, d’accordo, ma sempre di un risparmio si tratta.

Un esempio: se trovo, all’interno di una stored procedure, una DROP di una tabella temporanea la elimino.

Sappiamo, o dovremmo sapere, che questi oggetti temporanei vengono distrutti, in maniera automatica, nel momento in cui la stored procedure termina le sue operazioni.

Questa sorta di “garbage collector” fa del lavoro per noi e lo fa più velocemente.

Vediamo un esempio costruendo tre stored procedure che, al loro interno, costruiscono trenta tabelle temporanee e:

la prima stored, al termine delle sue istruzioni, ne effettua la DROP con un’istruzione simile a IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
la seconda stored, come la precedente, effettua la DROP ma senza verificare la presenza dell’oggetto. Qualcosa come: DROP TABLE #t1
la terza, invece, costruisce gli oggetti temporanei e non si preoccupa di distruggerli

Questo il codice di test:

 USE tempdb; 
GO

CREATE PROCEDURE dbo.up_test_con_drop_if_exists
AS
    SET NOCOUNT ON;

    create table #t1 (col char(8000))
    create table #t2 (col char(8000))
    create table #t3 (col char(8000))
    create table #t4 (col char(8000))
    create table #t5 (col char(8000))
    create table #t6 (col char(8000))
    create table #t7 (col char(8000))
    create table #t8 (col char(8000))
    create table #t9 (col char(8000))
    create table #t10 (col char(8000))
    
    create table #t11 (col char(8000))
    create table #t12 (col char(8000))
    create table #t13 (col char(8000))
    create table #t14 (col char(8000))
    create table #t15 (col char(8000))
    create table #t16 (col char(8000))
    create table #t17 (col char(8000))
    create table #t18 (col char(8000))
    create table #t19 (col char(8000))
    create table #t20 (col char(8000))
    
    create table #t21 (col char(8000))
    create table #t22 (col char(8000))
    create table #t23 (col char(8000))
    create table #t24 (col char(8000))
    create table #t25 (col char(8000))
    create table #t26 (col char(8000))
    create table #t27 (col char(8000))
    create table #t28 (col char(8000))
    create table #t29 (col char(8000))
    create table #t30 (col char(8000))
    
    IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
    IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2
    IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3
    IF OBJECT_ID('tempdb..#t4') IS NOT NULL DROP TABLE #t4
    IF OBJECT_ID('tempdb..#t5') IS NOT NULL DROP TABLE #t5
    IF OBJECT_ID('tempdb..#t6') IS NOT NULL DROP TABLE #t6
    IF OBJECT_ID('tempdb..#t7') IS NOT NULL DROP TABLE #t7
    IF OBJECT_ID('tempdb..#t8') IS NOT NULL DROP TABLE #t8
    IF OBJECT_ID('tempdb..#t9') IS NOT NULL DROP TABLE #t9
    IF OBJECT_ID('tempdb..#t10') IS NOT NULL DROP TABLE #t10
    
    IF OBJECT_ID('tempdb..#t11') IS NOT NULL DROP TABLE #t11
    IF OBJECT_ID('tempdb..#t12') IS NOT NULL DROP TABLE #t12
    IF OBJECT_ID('tempdb..#t13') IS NOT NULL DROP TABLE #t13
    IF OBJECT_ID('tempdb..#t14') IS NOT NULL DROP TABLE #t14
    IF OBJECT_ID('tempdb..#t15') IS NOT NULL DROP TABLE #t15
    IF OBJECT_ID('tempdb..#t16') IS NOT NULL DROP TABLE #t16
    IF OBJECT_ID('tempdb..#t17') IS NOT NULL DROP TABLE #t17
    IF OBJECT_ID('tempdb..#t18') IS NOT NULL DROP TABLE #t18
    IF OBJECT_ID('tempdb..#t19') IS NOT NULL DROP TABLE #t19
    IF OBJECT_ID('tempdb..#t20') IS NOT NULL DROP TABLE #t20

    IF OBJECT_ID('tempdb..#t21') IS NOT NULL DROP TABLE #t21
    IF OBJECT_ID('tempdb..#t22') IS NOT NULL DROP TABLE #t22
    IF OBJECT_ID('tempdb..#t23') IS NOT NULL DROP TABLE #t23
    IF OBJECT_ID('tempdb..#t24') IS NOT NULL DROP TABLE #t24
    IF OBJECT_ID('tempdb..#t25') IS NOT NULL DROP TABLE #t25
    IF OBJECT_ID('tempdb..#t26') IS NOT NULL DROP TABLE #t26
    IF OBJECT_ID('tempdb..#t27') IS NOT NULL DROP TABLE #t27
    IF OBJECT_ID('tempdb..#t28') IS NOT NULL DROP TABLE #t28
    IF OBJECT_ID('tempdb..#t29') IS NOT NULL DROP TABLE #t29
    IF OBJECT_ID('tempdb..#t30') IS NOT NULL DROP TABLE #t30
  go


CREATE PROCEDURE dbo.up_test_con_drop
AS
    SET NOCOUNT ON;

    create table #t1 (col char(8000))
    create table #t2 (col char(8000))
    create table #t3 (col char(8000))
    create table #t4 (col char(8000))
    create table #t5 (col char(8000))
    create table #t6 (col char(8000))
    create table #t7 (col char(8000))
    create table #t8 (col char(8000))
    create table #t9 (col char(8000))
    create table #t10 (col char(8000))
    
    create table #t11 (col char(8000))
    create table #t12 (col char(8000))
    create table #t13 (col char(8000))
    create table #t14 (col char(8000))
    create table #t15 (col char(8000))
    create table #t16 (col char(8000))
    create table #t17 (col char(8000))
    create table #t18 (col char(8000))
    create table #t19 (col char(8000))
    create table #t20 (col char(8000))
    
    create table #t21 (col char(8000))
    create table #t22 (col char(8000))
    create table #t23 (col char(8000))
    create table #t24 (col char(8000))
    create table #t25 (col char(8000))
    create table #t26 (col char(8000))
    create table #t27 (col char(8000))
    create table #t28 (col char(8000))
    create table #t29 (col char(8000))
    create table #t30 (col char(8000))
    
    DROP TABLE #t1
    DROP TABLE #t2
    DROP TABLE #t3
    DROP TABLE #t4
    DROP TABLE #t5
    DROP TABLE #t6
    DROP TABLE #t7
    DROP TABLE #t8
    DROP TABLE #t9
    DROP TABLE #t10
    
    DROP TABLE #t11
    DROP TABLE #t12
    DROP TABLE #t13
    DROP TABLE #t14
    DROP TABLE #t15
    DROP TABLE #t16
    DROP TABLE #t17
    DROP TABLE #t18
    DROP TABLE #t19
    DROP TABLE #t20

    DROP TABLE #t21
    DROP TABLE #t22
    DROP TABLE #t23
    DROP TABLE #t24
    DROP TABLE #t25
    DROP TABLE #t26
    DROP TABLE #t27
    DROP TABLE #t28
    DROP TABLE #t29
    DROP TABLE #t30
  go


CREATE PROCEDURE dbo.up_test_senza_drop
AS
    SET NOCOUNT ON;

    create table #t1 (col char(8000))
    create table #t2 (col char(8000))
    create table #t3 (col char(8000))
    create table #t4 (col char(8000))
    create table #t5 (col char(8000))
    create table #t6 (col char(8000))
    create table #t7 (col char(8000))
    create table #t8 (col char(8000))
    create table #t9 (col char(8000))
    create table #t10 (col char(8000))
    
    create table #t11 (col char(8000))
    create table #t12 (col char(8000))
    create table #t13 (col char(8000))
    create table #t14 (col char(8000))
    create table #t15 (col char(8000))
    create table #t16 (col char(8000))
    create table #t17 (col char(8000))
    create table #t18 (col char(8000))
    create table #t19 (col char(8000))
    create table #t20 (col char(8000))
    
    create table #t21 (col char(8000))
    create table #t22 (col char(8000))
    create table #t23 (col char(8000))
    create table #t24 (col char(8000))
    create table #t25 (col char(8000))
    create table #t26 (col char(8000))
    create table #t27 (col char(8000))
    create table #t28 (col char(8000))
    create table #t29 (col char(8000))
    create table #t30 (col char(8000))
  go


/* Test di performance su 1.000 chiamate */
declare @t datetime
declare @i smallint 

set @t = getdate()
  set @i = 1000
  while @i > 0
  begin
    exec dbo.up_test_con_drop_if_exists
    set @i -=1
  end
print 'Drop con IF EXISTS: ' + cast(datediff(millisecond, @t, current_timestamp) as varchar(10))


set @t = getdate()
  set @i = 1000
  while @i > 0
  begin
    exec dbo.up_test_con_drop
    set @i -=1
  end
print 'Drop senza IF EXISTS: ' + cast(datediff(millisecond, @t, current_timestamp) as varchar(10))


set @t = getdate()
  set @i = 1000
  while @i > 0
  begin
    exec dbo.up_test_senza_drop
    set @i -=1
  end

print 'Senza Drop: ' + cast(datediff(millisecond, @t, current_timestamp) as varchar(10))
GO

Questo il risultato (espresso in millisecondi):

Share via

Stored Procedure e DROP esplicito delle tabelle temporanee

Additional resources