creating table with Create Table  
Author Message
PelcoFR85011A





PostPosted: Wed Jan 17 07:24:34 CST 2007 Top

SQL Server Developer >> creating table with Create Table

Hello there

I have huge table with 1,500,000 records

When i use Create Table to create temp table based on the huge table with
only 6 records of 12 and insert all the data it takes at least 12 seconds

And when i use Select () into statement it takes 3 seconds

on the execution plan the subtree cost is the same.

whay it can be happening

SQL Server12  
 
 
Uri





PostPosted: Wed Jan 17 07:24:34 CST 2007 Top

SQL Server Developer >> creating table with Create Table Roy

Nowadays 1.500.000 is not considered huge :-))

> And when i use Select () into statement it takes 3 seconds
> on the execution plan the subtree cost is the same.
> whay it can be happening

What is recovery model of the database?
Under Bulk-Recovery model this SELECT * INTO operation is minimally logged
, thus it could much faster





> Hello there
>
> I have huge table with 1,500,000 records
>
> When i use Create Table to create temp table based on the huge table with
> only 6 records of 12 and insert all the data it takes at least 12 seconds
>
> And when i use Select () into statement it takes 3 seconds
>
> on the execution plan the subtree cost is the same.
>
> whay it can be happening
>
>
>


 
 
Dan





PostPosted: Wed Jan 17 07:28:00 CST 2007 Top

SQL Server Developer >> creating table with Create Table A SELECT...INTO into a tempdb table is always minimally logged due because
tempdb is in the SIMPLE recovery model. A normal INSERT is fully logged so
there is more overhead.

Don't put too much consideration on execution plan costs; the most important
performance indicator is actual elapsed time.

--
Hope this helps.

Dan Guzman
SQL Server MVP



> Hello there
>
> I have huge table with 1,500,000 records
>
> When i use Create Table to create temp table based on the huge table with
> only 6 records of 12 and insert all the data it takes at least 12 seconds
>
> And when i use Select () into statement it takes 3 seconds
>
> on the execution plan the subtree cost is the same.
>
> whay it can be happening
>
>
>

 
 
dmarkle





PostPosted: Wed Jan 17 08:00:31 CST 2007 Top

SQL Server Developer >> creating table with Create Table Let's see your code.

-Dave



> Hello there
>
> I have huge table with 1,500,000 records
>
> When i use Create Table to create temp table based on the huge table with
> only 6 records of 12 and insert all the data it takes at least 12 seconds
>
> And when i use Select () into statement it takes 3 seconds
>
> on the execution plan the subtree cost is the same.
>
> whay it can be happening