[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

using table variable

Roy Goldhammer

3/27/2007 3:40:00 PM

Hello there

I have some process that i can't run on single sql query.

In order to run it i must split the huge query to 3 temp tables and run it.

The problem is that i can't use temp tables on function but only on store
procedure. Also if

So i thought using table variables. I used it this way:
DECLARE @tbl Table (table decleration ...)

INSERT @tbl
SELECT .......

this process takes 30 seconds while

select *
INTO #tmp

took 15 seconds.

Here is my DDL:

DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
OccType tinyint,
ForDate datetime)

INSERT @ProgDet
SELECT LPP.SerialNumber, PPD.OccType,
max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
--INTO #Tmp2
from V_LastProgramPlan LPP
JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
from V_LastProgramPlan LPP1
JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
WHERE LPP.SerialNumber = LPP1.SerialNumber
AND PPD.OccType = PPD1.OccType)
GROUP BY LPP.SerialNumber, PPD.OccType
ORDER BY 1,2

--CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)

SELECT LPP.SerialNumber, ForMonth, ForYear,
PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
case when PPD.OccType = 1 then EmployerName
else '' end EmployerName,
start_Date, End_Date,
PPD.*
from V_LastProgramPlan LPP
JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
left join nvqs NV ON PPD.nvq_id = NV.nvq_id
left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
JOIN @ProgDet PP
ON LPP.SerialNumber = PP.SerialNumber
AND PPD.OccType = PP.OccType
AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate

whay table variable in this case is slower?


7 Answers

masri999

3/27/2007 5:39:00 PM

0

On Mar 27, 8:40 pm, "Roy Goldhammer" <r...@hotmail.com> wrote:
> Hello there
>
> I have some process that i can't run on single sql query.
>
> In order to run it i must split the huge query to 3 temp tables and run it.
>
> The problem is that i can't use temp tables on function but only on store
> procedure. Also if
>
> So i thought using table variables. I used it this way:
> DECLARE @tbl Table (table decleration ...)
>
> INSERT @tbl
> SELECT .......
>
> this process takes 30 seconds while
>
> select *
> INTO #tmp
>
> took 15 seconds.
>
> Here is my DDL:
>
> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
> OccType tinyint,
> ForDate datetime)
>
> INSERT @ProgDet
> SELECT LPP.SerialNumber, PPD.OccType,
> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
> --INTO #Tmp2
> from V_LastProgramPlan LPP
> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
> from V_LastProgramPlan LPP1
> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
> WHERE LPP.SerialNumber = LPP1.SerialNumber
> AND PPD.OccType = PPD1.OccType)
> GROUP BY LPP.SerialNumber, PPD.OccType
> ORDER BY 1,2
>
> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>
> SELECT LPP.SerialNumber, ForMonth, ForYear,
> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
> case when PPD.OccType = 1 then EmployerName
> else '' end EmployerName,
> start_Date, End_Date,
> PPD.*
> from V_LastProgramPlan LPP
> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
> JOIN @ProgDet PP
> ON LPP.SerialNumber = PP.SerialNumber
> AND PPD.OccType = PP.OccType
> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>
> whay table variable in this case is slower?

You can not create index on table variable using CREATE INDEX .
So your table variable DDL is defined with PK and Clustered Index .
Insert may be slower due to Index on the table. Try to remove the
index and check
Note: table variables are created in tempdb

Uri Dimant

3/28/2007 6:44:00 AM

0

Roy
How big is your load? You use diffrerent techniques to insert the data.
SELECT ..INTO is always faster





"Roy Goldhammer" <roy@hotmail.com> wrote in message
news:eedI4XIcHHA.4004@TK2MSFTNGP06.phx.gbl...
> Hello there
>
> I have some process that i can't run on single sql query.
>
> In order to run it i must split the huge query to 3 temp tables and run
> it.
>
> The problem is that i can't use temp tables on function but only on store
> procedure. Also if
>
> So i thought using table variables. I used it this way:
> DECLARE @tbl Table (table decleration ...)
>
> INSERT @tbl
> SELECT .......
>
> this process takes 30 seconds while
>
> select *
> INTO #tmp
>
> took 15 seconds.
>
> Here is my DDL:
>
> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
> OccType tinyint,
> ForDate datetime)
>
> INSERT @ProgDet
> SELECT LPP.SerialNumber, PPD.OccType,
> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
> --INTO #Tmp2
> from V_LastProgramPlan LPP
> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
> from V_LastProgramPlan LPP1
> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
> WHERE LPP.SerialNumber = LPP1.SerialNumber
> AND PPD.OccType = PPD1.OccType)
> GROUP BY LPP.SerialNumber, PPD.OccType
> ORDER BY 1,2
>
> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>
> SELECT LPP.SerialNumber, ForMonth, ForYear,
> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
> case when PPD.OccType = 1 then EmployerName
> else '' end EmployerName,
> start_Date, End_Date,
> PPD.*
> from V_LastProgramPlan LPP
> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
> JOIN @ProgDet PP
> ON LPP.SerialNumber = PP.SerialNumber
> AND PPD.OccType = PP.OccType
> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>
> whay table variable in this case is slower?
>


Roy Goldhammer

3/28/2007 9:59:00 AM

0

Whell Uri

it is abount 13000 records. and it is not the first time that SELECT...INTO
works faster then INSERT.

My question is: whay table variables works slower then temp tables?

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uiUeWSQcHHA.2316@TK2MSFTNGP04.phx.gbl...
> Roy
> How big is your load? You use diffrerent techniques to insert the data.
> SELECT ..INTO is always faster
>
>
>
>
>
> "Roy Goldhammer" <roy@hotmail.com> wrote in message
> news:eedI4XIcHHA.4004@TK2MSFTNGP06.phx.gbl...
>> Hello there
>>
>> I have some process that i can't run on single sql query.
>>
>> In order to run it i must split the huge query to 3 temp tables and run
>> it.
>>
>> The problem is that i can't use temp tables on function but only on store
>> procedure. Also if
>>
>> So i thought using table variables. I used it this way:
>> DECLARE @tbl Table (table decleration ...)
>>
>> INSERT @tbl
>> SELECT .......
>>
>> this process takes 30 seconds while
>>
>> select *
>> INTO #tmp
>>
>> took 15 seconds.
>>
>> Here is my DDL:
>>
>> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
>> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
>> OccType tinyint,
>> ForDate datetime)
>>
>> INSERT @ProgDet
>> SELECT LPP.SerialNumber, PPD.OccType,
>> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
>> --INTO #Tmp2
>> from V_LastProgramPlan LPP
>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
>> from V_LastProgramPlan LPP1
>> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
>> WHERE LPP.SerialNumber = LPP1.SerialNumber
>> AND PPD.OccType = PPD1.OccType)
>> GROUP BY LPP.SerialNumber, PPD.OccType
>> ORDER BY 1,2
>>
>> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>>
>> SELECT LPP.SerialNumber, ForMonth, ForYear,
>> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
>> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
>> case when PPD.OccType = 1 then EmployerName
>> else '' end EmployerName,
>> start_Date, End_Date,
>> PPD.*
>> from V_LastProgramPlan LPP
>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
>> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
>> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
>> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
>> JOIN @ProgDet PP
>> ON LPP.SerialNumber = PP.SerialNumber
>> AND PPD.OccType = PP.OccType
>> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>>
>> whay table variable in this case is slower?
>>
>
>


Uri Dimant

3/28/2007 10:03:00 AM

0

Roy
> My question is: whay table variables works slower then temp tables?

Not always, it depends. From my experience a table variable "works" better
with small amount of data.





"Roy Goldhammer" <roy@hotmail.com> wrote in message
news:urJ599RcHHA.1000@TK2MSFTNGP05.phx.gbl...
> Whell Uri
>
> it is abount 13000 records. and it is not the first time that
> SELECT...INTO works faster then INSERT.
>
> My question is: whay table variables works slower then temp tables?
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:uiUeWSQcHHA.2316@TK2MSFTNGP04.phx.gbl...
>> Roy
>> How big is your load? You use diffrerent techniques to insert the data.
>> SELECT ..INTO is always faster
>>
>>
>>
>>
>>
>> "Roy Goldhammer" <roy@hotmail.com> wrote in message
>> news:eedI4XIcHHA.4004@TK2MSFTNGP06.phx.gbl...
>>> Hello there
>>>
>>> I have some process that i can't run on single sql query.
>>>
>>> In order to run it i must split the huge query to 3 temp tables and run
>>> it.
>>>
>>> The problem is that i can't use temp tables on function but only on
>>> store procedure. Also if
>>>
>>> So i thought using table variables. I used it this way:
>>> DECLARE @tbl Table (table decleration ...)
>>>
>>> INSERT @tbl
>>> SELECT .......
>>>
>>> this process takes 30 seconds while
>>>
>>> select *
>>> INTO #tmp
>>>
>>> took 15 seconds.
>>>
>>> Here is my DDL:
>>>
>>> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
>>> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
>>> OccType tinyint,
>>> ForDate datetime)
>>>
>>> INSERT @ProgDet
>>> SELECT LPP.SerialNumber, PPD.OccType,
>>> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
>>> --INTO #Tmp2
>>> from V_LastProgramPlan LPP
>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
>>> from V_LastProgramPlan LPP1
>>> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
>>> WHERE LPP.SerialNumber = LPP1.SerialNumber
>>> AND PPD.OccType = PPD1.OccType)
>>> GROUP BY LPP.SerialNumber, PPD.OccType
>>> ORDER BY 1,2
>>>
>>> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>>>
>>> SELECT LPP.SerialNumber, ForMonth, ForYear,
>>> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
>>> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
>>> case when PPD.OccType = 1 then EmployerName
>>> else '' end EmployerName,
>>> start_Date, End_Date,
>>> PPD.*
>>> from V_LastProgramPlan LPP
>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
>>> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
>>> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
>>> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
>>> JOIN @ProgDet PP
>>> ON LPP.SerialNumber = PP.SerialNumber
>>> AND PPD.OccType = PP.OccType
>>> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>>>
>>> whay table variable in this case is slower?
>>>
>>
>>
>
>


Tibor Karaszi

3/28/2007 10:24:00 AM

0

> My question is: whay table variables works slower then temp tables?

This is not a question of table variables vs. temp tables. It is a question of SELECT INTO vs.
INSERT... SELECT. The former can go in minimally logged mode, while the later is always fully
logged.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Roy Goldhammer" <roy@hotmail.com> wrote in message news:urJ599RcHHA.1000@TK2MSFTNGP05.phx.gbl...
> Whell Uri
>
> it is abount 13000 records. and it is not the first time that SELECT...INTO works faster then
> INSERT.
>
> My question is: whay table variables works slower then temp tables?
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message news:uiUeWSQcHHA.2316@TK2MSFTNGP04.phx.gbl...
>> Roy
>> How big is your load? You use diffrerent techniques to insert the data. SELECT ..INTO is always
>> faster
>>
>>
>>
>>
>>
>> "Roy Goldhammer" <roy@hotmail.com> wrote in message news:eedI4XIcHHA.4004@TK2MSFTNGP06.phx.gbl...
>>> Hello there
>>>
>>> I have some process that i can't run on single sql query.
>>>
>>> In order to run it i must split the huge query to 3 temp tables and run it.
>>>
>>> The problem is that i can't use temp tables on function but only on store procedure. Also if
>>>
>>> So i thought using table variables. I used it this way:
>>> DECLARE @tbl Table (table decleration ...)
>>>
>>> INSERT @tbl
>>> SELECT .......
>>>
>>> this process takes 30 seconds while
>>>
>>> select *
>>> INTO #tmp
>>>
>>> took 15 seconds.
>>>
>>> Here is my DDL:
>>>
>>> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
>>> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
>>> OccType tinyint,
>>> ForDate datetime)
>>>
>>> INSERT @ProgDet
>>> SELECT LPP.SerialNumber, PPD.OccType,
>>> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
>>> --INTO #Tmp2
>>> from V_LastProgramPlan LPP
>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
>>> from V_LastProgramPlan LPP1
>>> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
>>> WHERE LPP.SerialNumber = LPP1.SerialNumber
>>> AND PPD.OccType = PPD1.OccType)
>>> GROUP BY LPP.SerialNumber, PPD.OccType
>>> ORDER BY 1,2
>>>
>>> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>>>
>>> SELECT LPP.SerialNumber, ForMonth, ForYear,
>>> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
>>> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
>>> case when PPD.OccType = 1 then EmployerName
>>> else '' end EmployerName,
>>> start_Date, End_Date,
>>> PPD.*
>>> from V_LastProgramPlan LPP
>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
>>> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
>>> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
>>> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
>>> JOIN @ProgDet PP
>>> ON LPP.SerialNumber = PP.SerialNumber
>>> AND PPD.OccType = PP.OccType
>>> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>>>
>>> whay table variable in this case is slower?
>>>
>>
>>
>
>


Uri Dimant

3/28/2007 10:38:00 AM

0

Tibor
SELECT INTO is minimally logged only under Bulk_Logged recovery mode , am I
right?




"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23JqoCNScHHA.1244@TK2MSFTNGP04.phx.gbl...
>> My question is: whay table variables works slower then temp tables?
>
> This is not a question of table variables vs. temp tables. It is a
> question of SELECT INTO vs. INSERT... SELECT. The former can go in
> minimally logged mode, while the later is always fully logged.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Roy Goldhammer" <roy@hotmail.com> wrote in message
> news:urJ599RcHHA.1000@TK2MSFTNGP05.phx.gbl...
>> Whell Uri
>>
>> it is abount 13000 records. and it is not the first time that
>> SELECT...INTO works faster then INSERT.
>>
>> My question is: whay table variables works slower then temp tables?
>>
>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>> news:uiUeWSQcHHA.2316@TK2MSFTNGP04.phx.gbl...
>>> Roy
>>> How big is your load? You use diffrerent techniques to insert the
>>> data. SELECT ..INTO is always faster
>>>
>>>
>>>
>>>
>>>
>>> "Roy Goldhammer" <roy@hotmail.com> wrote in message
>>> news:eedI4XIcHHA.4004@TK2MSFTNGP06.phx.gbl...
>>>> Hello there
>>>>
>>>> I have some process that i can't run on single sql query.
>>>>
>>>> In order to run it i must split the huge query to 3 temp tables and
>>>> run it.
>>>>
>>>> The problem is that i can't use temp tables on function but only on
>>>> store procedure. Also if
>>>>
>>>> So i thought using table variables. I used it this way:
>>>> DECLARE @tbl Table (table decleration ...)
>>>>
>>>> INSERT @tbl
>>>> SELECT .......
>>>>
>>>> this process takes 30 seconds while
>>>>
>>>> select *
>>>> INTO #tmp
>>>>
>>>> took 15 seconds.
>>>>
>>>> Here is my DDL:
>>>>
>>>> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
>>>> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
>>>> OccType tinyint,
>>>> ForDate datetime)
>>>>
>>>> INSERT @ProgDet
>>>> SELECT LPP.SerialNumber, PPD.OccType,
>>>> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
>>>> --INTO #Tmp2
>>>> from V_LastProgramPlan LPP
>>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>>> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
>>>> from V_LastProgramPlan LPP1
>>>> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
>>>> WHERE LPP.SerialNumber = LPP1.SerialNumber
>>>> AND PPD.OccType = PPD1.OccType)
>>>> GROUP BY LPP.SerialNumber, PPD.OccType
>>>> ORDER BY 1,2
>>>>
>>>> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>>>>
>>>> SELECT LPP.SerialNumber, ForMonth, ForYear,
>>>> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
>>>> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
>>>> case when PPD.OccType = 1 then EmployerName
>>>> else '' end EmployerName,
>>>> start_Date, End_Date,
>>>> PPD.*
>>>> from V_LastProgramPlan LPP
>>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>>> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
>>>> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
>>>> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
>>>> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
>>>> JOIN @ProgDet PP
>>>> ON LPP.SerialNumber = PP.SerialNumber
>>>> AND PPD.OccType = PP.OccType
>>>> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>>>>
>>>> whay table variable in this case is slower?
>>>>
>>>
>>>
>>
>>
>
>


Tibor Karaszi

3/28/2007 12:04:00 PM

0

Yes SELECT INTO is minimally logged in bulk_logged and simple recovery model.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Uri Dimant" <urid@iscar.co.il> wrote in message news:OrHSPVScHHA.3632@TK2MSFTNGP02.phx.gbl...
> Tibor
> SELECT INTO is minimally logged only under Bulk_Logged recovery mode , am I right?
>
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:%23JqoCNScHHA.1244@TK2MSFTNGP04.phx.gbl...
>>> My question is: whay table variables works slower then temp tables?
>>
>> This is not a question of table variables vs. temp tables. It is a question of SELECT INTO vs.
>> INSERT... SELECT. The former can go in minimally logged mode, while the later is always fully
>> logged.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://www.solidqualitylea...
>>
>>
>> "Roy Goldhammer" <roy@hotmail.com> wrote in message news:urJ599RcHHA.1000@TK2MSFTNGP05.phx.gbl...
>>> Whell Uri
>>>
>>> it is abount 13000 records. and it is not the first time that SELECT...INTO works faster then
>>> INSERT.
>>>
>>> My question is: whay table variables works slower then temp tables?
>>>
>>> "Uri Dimant" <urid@iscar.co.il> wrote in message news:uiUeWSQcHHA.2316@TK2MSFTNGP04.phx.gbl...
>>>> Roy
>>>> How big is your load? You use diffrerent techniques to insert the data. SELECT ..INTO is
>>>> always faster
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Roy Goldhammer" <roy@hotmail.com> wrote in message
>>>> news:eedI4XIcHHA.4004@TK2MSFTNGP06.phx.gbl...
>>>>> Hello there
>>>>>
>>>>> I have some process that i can't run on single sql query.
>>>>>
>>>>> In order to run it i must split the huge query to 3 temp tables and run it.
>>>>>
>>>>> The problem is that i can't use temp tables on function but only on store procedure. Also if
>>>>>
>>>>> So i thought using table variables. I used it this way:
>>>>> DECLARE @tbl Table (table decleration ...)
>>>>>
>>>>> INSERT @tbl
>>>>> SELECT .......
>>>>>
>>>>> this process takes 30 seconds while
>>>>>
>>>>> select *
>>>>> INTO #tmp
>>>>>
>>>>> took 15 seconds.
>>>>>
>>>>> Here is my DDL:
>>>>>
>>>>> DECLARE @ProgDet TABLE (ID int identity(1, 1) Primary key clustered,
>>>>> SerialNumber nvarchar(14) COLLATE SQL_LATIN1_GENERAL_CP1255_CI_AS,
>>>>> OccType tinyint,
>>>>> ForDate datetime)
>>>>>
>>>>> INSERT @ProgDet
>>>>> SELECT LPP.SerialNumber, PPD.OccType,
>>>>> max(dbo.fnDateSerial(ForYear, ForMonth, 1)) ForDate
>>>>> --INTO #Tmp2
>>>>> from V_LastProgramPlan LPP
>>>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>>>> WHERE PPD.Start_Date = (SELECT max(Start_date) Start_Date
>>>>> from V_LastProgramPlan LPP1
>>>>> JOIN tbl_ProgramPlanDetails PPD1 ON LPP1.id = PPD1.FK_Header
>>>>> WHERE LPP.SerialNumber = LPP1.SerialNumber
>>>>> AND PPD.OccType = PPD1.OccType)
>>>>> GROUP BY LPP.SerialNumber, PPD.OccType
>>>>> ORDER BY 1,2
>>>>>
>>>>> --CREATE INDEX IX_ProgDat ON @ProgDat(SerialNumber, OccType)
>>>>>
>>>>> SELECT LPP.SerialNumber, ForMonth, ForYear,
>>>>> PPD.OccType, PPT.OccType, PPD.nvq_id, nvq_name,
>>>>> case when PPD.OccType = 1 then EmployerID else '' end EmployerID,
>>>>> case when PPD.OccType = 1 then EmployerName
>>>>> else '' end EmployerName,
>>>>> start_Date, End_Date,
>>>>> PPD.*
>>>>> from V_LastProgramPlan LPP
>>>>> JOIN tbl_ProgramPlanDetails PPD ON LPP.id = PPD.FK_Header
>>>>> join tbl_ProgramPlanTypes PPT ON PPD.OccType = PPT.NUM
>>>>> left join nvqs NV ON PPD.nvq_id = NV.nvq_id
>>>>> left join Courses CO ON NV.FK_CourseID = CO.PrimeryKey
>>>>> LEFT JOIN Employer EM ON CO.FK_EmployerID = EM.EmployerID
>>>>> JOIN @ProgDet PP
>>>>> ON LPP.SerialNumber = PP.SerialNumber
>>>>> AND PPD.OccType = PP.OccType
>>>>> AND dbo.fnDateSerial(ForYear, ForMonth, 1) = ForDate
>>>>>
>>>>> whay table variable in this case is slower?
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>