|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Create temp tablecreate table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 decimal(15,5),col6 decimal(15,5)) INSERT INTO #temp(col1,col2,col3,col4,col5,col6) SELECT col1,col2,col3,col4,col5,col6 from table.... drop table #temp it takes 260 mili seconds. If I use: SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... drop table #temp it takes about 30ms. Why is that difference? Any expanation? I don't understand. In real life I can't use second example, because I have one of the columns in temp table defined as identity(). So I should reduced time of execution of first example. Any idea? regards,S On e possible reason is that SELECT INTO can be minimally logged (if database has appropriate
recovery model, which tempdb has), where INSERT SELECT if always fully logged, regardless of recovery model. You could always try table variable instead of temp table and see if that improves things. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "simon" <simon.zu***@iware.si> wrote in message news:uJSof.20735$h6.755806@news.siol.net... > IF I create temp table and insert something into it: > > create table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 decimal(15,5),col6 > decimal(15,5)) > > INSERT INTO #temp(col1,col2,col3,col4,col5,col6) > SELECT col1,col2,col3,col4,col5,col6 from table.... > > drop table #temp > > it takes 260 mili seconds. > > If I use: > > SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... > > drop table #temp > > it takes about 30ms. > > Why is that difference? > Any expanation? I don't understand. > > In real life I can't use second example, because I have one of the columns in temp table defined > as identity(). > So I should reduced time of execution of first example. Any idea? > > regards,S > > In real life I can't use second example, because I have one of the columns You can use the IDENTITY function in a SELECT....INTO statement to create a > in temp table defined as identity(). new identity column and assign values: SELECT IDENTITY(int, 1, 1) AS Id, ID as col0, col1,col2,col3,col4,col5,col6 FROM table.... See the Books Online for more information. Also, to add on to Tibor's remarks, SELECT...INTO is minimally logged in the SIMPLE or BULK_LOGGED recovery model but fully logged in the FULL model. Tempdb uses SIMPLE so that is why the operation is minimally logged there. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "simon" <simon.zu***@iware.si> wrote in message news:uJSof.20735$h6.755806@news.siol.net... > IF I create temp table and insert something into it: > > create table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 > decimal(15,5),col6 decimal(15,5)) > > INSERT INTO #temp(col1,col2,col3,col4,col5,col6) > SELECT col1,col2,col3,col4,col5,col6 from table.... > > drop table #temp > > it takes 260 mili seconds. > > If I use: > > SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... > > drop table #temp > > it takes about 30ms. > > Why is that difference? > Any expanation? I don't understand. > > In real life I can't use second example, because I have one of the columns > in temp table defined as identity(). > So I should reduced time of execution of first example. Any idea? > > regards,S > thank you , I'll try
I have another question: if @variable=1 then select col1 into #temp from table1... else select col2 into #temp from table1... I get an error: There is already an object named '#temp' in the database. So, I can't use the select... into... statement if I would like to have the exact table name? this works bit is slower than insert into: if @variable=1 then insert into #temp select col1from table1... else insert into #temp select col2 into #temp from table1 Any suggestion? thanks,S Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:O9OMltwAGHA.4080@TK2MSFTNGP14.phx.gbl... >> In real life I can't use second example, because I have one of the >> columns in temp table defined as identity(). > > You can use the IDENTITY function in a SELECT....INTO statement to create > a new identity column and assign values: > > SELECT > IDENTITY(int, 1, 1) AS Id, ID as col0, > col1,col2,col3,col4,col5,col6 > FROM table.... > > See the Books Online for more information. > > Also, to add on to Tibor's remarks, SELECT...INTO is minimally logged in > the SIMPLE or BULK_LOGGED recovery model but fully logged in the FULL > model. Tempdb uses SIMPLE so that is why the operation is minimally logged > there. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "simon" <simon.zu***@iware.si> wrote in message > news:uJSof.20735$h6.755806@news.siol.net... >> IF I create temp table and insert something into it: >> >> create table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 >> decimal(15,5),col6 decimal(15,5)) >> >> INSERT INTO #temp(col1,col2,col3,col4,col5,col6) >> SELECT col1,col2,col3,col4,col5,col6 from table.... >> >> drop table #temp >> >> it takes 260 mili seconds. >> >> If I use: >> >> SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... >> >> drop table #temp >> >> it takes about 30ms. >> >> Why is that difference? >> Any expanation? I don't understand. >> >> In real life I can't use second example, because I have one of the >> columns in temp table defined as identity(). >> So I should reduced time of execution of first example. Any idea? >> >> regards,S >> > > BEWARE!!! Be careful with SELECT INTO!!! The IDENTITY function does not
produce consistent results. Look at the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 Show quote "simon" <simon.zu***@iware.si> wrote in message news:SCUof.20736$h6.756191@news.siol.net... > thank you , I'll try > > I have another question: > > if @variable=1 then > select col1 into #temp from table1... > else > select col2 into #temp from table1... > > I get an error: > > There is already an object named '#temp' in the database. > > So, I can't use the select... into... statement if I would like to have > the exact table name? > > this works bit is slower than insert into: > > if @variable=1 then > insert into #temp > select col1from table1... > else > insert into #temp > select col2 into #temp from table1 > > Any suggestion? > > thanks,S > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:O9OMltwAGHA.4080@TK2MSFTNGP14.phx.gbl... >>> In real life I can't use second example, because I have one of the >>> columns in temp table defined as identity(). >> >> You can use the IDENTITY function in a SELECT....INTO statement to create >> a new identity column and assign values: >> >> SELECT >> IDENTITY(int, 1, 1) AS Id, ID as col0, >> col1,col2,col3,col4,col5,col6 >> FROM table.... >> >> See the Books Online for more information. >> >> Also, to add on to Tibor's remarks, SELECT...INTO is minimally logged in >> the SIMPLE or BULK_LOGGED recovery model but fully logged in the FULL >> model. Tempdb uses SIMPLE so that is why the operation is minimally >> logged there. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "simon" <simon.zu***@iware.si> wrote in message >> news:uJSof.20735$h6.755806@news.siol.net... >>> IF I create temp table and insert something into it: >>> >>> create table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 >>> decimal(15,5),col6 decimal(15,5)) >>> >>> INSERT INTO #temp(col1,col2,col3,col4,col5,col6) >>> SELECT col1,col2,col3,col4,col5,col6 from table.... >>> >>> drop table #temp >>> >>> it takes 260 mili seconds. >>> >>> If I use: >>> >>> SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... >>> >>> drop table #temp >>> >>> it takes about 30ms. >>> >>> Why is that difference? >>> Any expanation? I don't understand. >>> >>> In real life I can't use second example, because I have one of the >>> columns in temp table defined as identity(). >>> So I should reduced time of execution of first example. Any idea? >>> >>> regards,S >>> >> >> > > > if @variable=1 then One method:> select col1 into #temp from table1... > else > select col2 into #temp from table1... > > I get an error: > > There is already an object named '#temp' in the database. > > So, I can't use the select... into... statement if I would like to have > the exact table name? SELECT col1 INTO #temp FROM table1... WHERE @variable=1 UNION ALL SELECT col2 FROM table1... WHERE @variable <> 1 OR @variable IS NULL -- Show quoteHope this helps. Dan Guzman SQL Server MVP "simon" <simon.zu***@iware.si> wrote in message news:SCUof.20736$h6.756191@news.siol.net... > thank you , I'll try > > I have another question: > > if @variable=1 then > select col1 into #temp from table1... > else > select col2 into #temp from table1... > > I get an error: > > There is already an object named '#temp' in the database. > > So, I can't use the select... into... statement if I would like to have > the exact table name? > > this works bit is slower than insert into: > > if @variable=1 then > insert into #temp > select col1from table1... > else > insert into #temp > select col2 into #temp from table1 > > Any suggestion? > > thanks,S > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:O9OMltwAGHA.4080@TK2MSFTNGP14.phx.gbl... >>> In real life I can't use second example, because I have one of the >>> columns in temp table defined as identity(). >> >> You can use the IDENTITY function in a SELECT....INTO statement to create >> a new identity column and assign values: >> >> SELECT >> IDENTITY(int, 1, 1) AS Id, ID as col0, >> col1,col2,col3,col4,col5,col6 >> FROM table.... >> >> See the Books Online for more information. >> >> Also, to add on to Tibor's remarks, SELECT...INTO is minimally logged in >> the SIMPLE or BULK_LOGGED recovery model but fully logged in the FULL >> model. Tempdb uses SIMPLE so that is why the operation is minimally >> logged there. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "simon" <simon.zu***@iware.si> wrote in message >> news:uJSof.20735$h6.755806@news.siol.net... >>> IF I create temp table and insert something into it: >>> >>> create table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 >>> decimal(15,5),col6 decimal(15,5)) >>> >>> INSERT INTO #temp(col1,col2,col3,col4,col5,col6) >>> SELECT col1,col2,col3,col4,col5,col6 from table.... >>> >>> drop table #temp >>> >>> it takes 260 mili seconds. >>> >>> If I use: >>> >>> SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... >>> >>> drop table #temp >>> >>> it takes about 30ms. >>> >>> Why is that difference? >>> Any expanation? I don't understand. >>> >>> In real life I can't use second example, because I have one of the >>> columns in temp table defined as identity(). >>> So I should reduced time of execution of first example. Any idea? >>> >>> regards,S >>> >> >> > > thank you Dan
regards,S Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:OgN5%23GzAGHA.3520@TK2MSFTNGP10.phx.gbl... >> if @variable=1 then >> select col1 into #temp from table1... >> else >> select col2 into #temp from table1... >> >> I get an error: >> >> There is already an object named '#temp' in the database. >> >> So, I can't use the select... into... statement if I would like to have >> the exact table name? > > One method: > > SELECT col1 INTO #temp FROM table1... > WHERE @variable=1 > UNION ALL > SELECT col2 FROM table1... > WHERE @variable <> 1 OR @variable IS NULL > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "simon" <simon.zu***@iware.si> wrote in message > news:SCUof.20736$h6.756191@news.siol.net... >> thank you , I'll try >> >> I have another question: >> >> if @variable=1 then >> select col1 into #temp from table1... >> else >> select col2 into #temp from table1... >> >> I get an error: >> >> There is already an object named '#temp' in the database. >> >> So, I can't use the select... into... statement if I would like to have >> the exact table name? >> >> this works bit is slower than insert into: >> >> if @variable=1 then >> insert into #temp >> select col1from table1... >> else >> insert into #temp >> select col2 into #temp from table1 >> >> Any suggestion? >> >> thanks,S >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:O9OMltwAGHA.4080@TK2MSFTNGP14.phx.gbl... >>>> In real life I can't use second example, because I have one of the >>>> columns in temp table defined as identity(). >>> >>> You can use the IDENTITY function in a SELECT....INTO statement to >>> create a new identity column and assign values: >>> >>> SELECT >>> IDENTITY(int, 1, 1) AS Id, ID as col0, >>> col1,col2,col3,col4,col5,col6 >>> FROM table.... >>> >>> See the Books Online for more information. >>> >>> Also, to add on to Tibor's remarks, SELECT...INTO is minimally logged in >>> the SIMPLE or BULK_LOGGED recovery model but fully logged in the FULL >>> model. Tempdb uses SIMPLE so that is why the operation is minimally >>> logged there. >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> >>> "simon" <simon.zu***@iware.si> wrote in message >>> news:uJSof.20735$h6.755806@news.siol.net... >>>> IF I create temp table and insert something into it: >>>> >>>> create table #temp(col1 int,col2 int,col3 int,col4 decimal(15,5),col5 >>>> decimal(15,5),col6 decimal(15,5)) >>>> >>>> INSERT INTO #temp(col1,col2,col3,col4,col5,col6) >>>> SELECT col1,col2,col3,col4,col5,col6 from table.... >>>> >>>> drop table #temp >>>> >>>> it takes 260 mili seconds. >>>> >>>> If I use: >>>> >>>> SELECT col1,col2,col3,col4,col5,col6 into #temp from table.... >>>> >>>> drop table #temp >>>> >>>> it takes about 30ms. >>>> >>>> Why is that difference? >>>> Any expanation? I don't understand. >>>> >>>> In real life I can't use second example, because I have one of the >>>> columns in temp table defined as identity(). >>>> So I should reduced time of execution of first example. Any idea? >>>> >>>> regards,S >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics