SQL Server, Select - Insert performance

microsoft.public.sqlserver.server - Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.


SQL Server >> Select - Insert performance

by Bill Oeftering » Mon, 13 Sep 2004 22:37:19 GMT

I have created a scheme to compare tables to some meta data and alter the
table if necessary. I'm using a select insert to populate table, rename it
and recreate the indexes. The problem is this is very slow and the log file
grows huge. What's the fastest way to do this?

Sample Partial script:
/* create the new table
create table tmp_invoice ( invnum char(10) ...)
/* copy the old table data
select (invnum,...) from invoice insert into tmp_invoice
/* drop old table
drop table invoice
/* rename the new table and create indexes
...

Thanks, Bill



Top

SQL Server >> Select - Insert performance

by Andrew J. Kelly » Mon, 13 Sep 2004 22:49:45 GMT


Use BCP and Bulk Insert to move the data between files. If you follow the
guidelines in BOL under "minimally logged bulk copy" you can have minimal
log file growth.

--
Andrew J. Kelly SQL MVP




file




Top
Similar Threads

1. SELECT/INSERT SELECT performance issue - SQL Server

2. Table Spool in simple INSERT...SELECT causing performance problems 

Hello,

I have a table which has a few million records. It has an IDENTITY
column that serves as the primary key. In a part of our application
here, a previous record may need to be copied as a new row. Within a
stored procedure exists SQL like:

INSERT INTO [My_DB].[dbo].[My_Table] (col1, col2, col3, ...)
SELECT @var1, col2, col3, ...
FROM My_Table
WHERE my_id = @my_id  --This column is the IDENTITY column PK

For some reason, this query is using a Table Spool, and I can't figure
out why. I know the definition of a Table Spool (records saved in
tempdb so that rewinds can use the temp table), but I don't understand
why it is being used here or how to prevent it.

I've tried rebuilding all of the indexes on the table and that did not
help.

I tried creating a scaled-down example that reproduces the problem,
but I could not and it's not practical to post the table definition
and data to reproduce the problem here. If there is further
information that would help someone determine the reason for the Table
Spool or something that you would like me to check or test, please let
me know.

     Thanks!
       -Tom.

3. Performance trouble with insert-select, any suggestions? - SQL Server

4. Performance tuning of Large Select / Insert operation 

I'm working on parsing and converting a large data set that's generated
by a 3rd party application for us.  The data I'm going after is stored
in a table with 4 int columns and a TEXT column.  I'm writing some code
to parse the data out of the TEXT columns and write them to 2 new
datatables so that we can query them more efficiently.  The total
source table size is roughly 40,000 rows and 1.5 GB.

Initially I was attempting to handle all 3 steps (get the data from the
table, parse it, write it to the new tables) in one app.  However,
after some serious performance issues with that, I've decided to split
it out into separate modules, at least until I can get it nailed down.

The first bit of ADO code reads the 40,000 rows from the table, parses
it into the correct format and writes it out to a text file.  This
takes approximately 40 minutes, and leaves me with a 1.5GB flat file
with approximately 14,000,000 rows.  I've got no problems with this
part.

The part that I'm having difficulty with is the second part.  I need to
read through the generated rows, and get an identity value based on the
criteria there.  Essentially, we are creating a lookup table out of
some of the values.  If the lookup value already exists, then select
the ID, otherwise, insert into the lookup table and return the new
identity value as the id.

This portion of the code is running unacceptably long (roughly 6 hours
to parse through the first million rows), and am wondering what I can
do to speed things up.  Currently, all I'm doing, is reading the data
file one line at a time, and calling a function to do the
checking/inserting of the identity.

Is there a way that I can speed up this part of the process using some
sort of bulk operation, bcp, dts, etc.  I've tried moving this into SPs
on the server, but the performance was essentially the same.  All of
these scripts are running on the server itself.

I've pasted the vbscript code below that I'm using.  I can move this to
a compiled language if it would help significantly, but cannot use any
.net based language, due to company-wide restrictions.

Thanks,

Mike

	sSQL="set nocount on select ?=FileID from dbo.SoftwareFile with
(NOLOCK) where FileName=? and FilePath=? and FileSize=? and
FileLastModifiedDate=? and FileLastModifiedTime=?"

	set
oParam=oCMD.CreateParameter("@FileID",adInteger,adParamOutput,len(sFileID),sFileID)
	oCmd.Parameters.append(oParam)
	set
oParam=oCMD.CreateParameter("@FileName",adVarChar,adParamInput,len(sFileName),sFileName)
	oCmd.Parameters.append(oParam)
	set
oParam=oCMD.CreateParameter("@FilePath",adVarChar,adParamInput,len(sFilePath),sFilePath)
	oCmd.Parameters.append(oParam)
	set
oParam=oCMD.CreateParameter("@FileSize",adInteger,adParamInput,len(sFileSize),sFileSize)
	oCmd.Parameters.append(oParam)
	set
oParam=oCMD.CreateParameter("@FileLastModifiedDate",adInteger,adParamInput,len(sFileLastModifiedDate),sFileLastModifiedDate)
	oCmd.Parameters.append(oParam)
	set
oParam=oCMD.CreateParameter("@FileLastModifiedTime",adInteger,adParamInput,len(sFileLastModifiedTime),sFileLastModifiedTime)
	oCmd.Parameters.append(oParam)
	oCmd.CommandText=sSQL
	oCmd.Execute
	sFileID=oCMD.Parameters("@FileID")
	if isnull(sFileID) then
		sFileID=-1
		oCmd.Parameters.Delete("@FileID")
		sSQL="set nocount on insert into dbo.SoftwareFile
(FileName,FilePath,FileSize,FileLastModifiedDate,FileLastModifiedTime)
values (?,?,?,?,?)"
		oCmd.CommandText=sSQL
		oCmd.Execute
		oCmd.Parameters.delete("@FileName")
		oCmd.Parameters.delete("@FileSize")
		oCmd.Parameters.delete("@FilePath")
		oCmd.Parameters.delete("@FileLastModifiedDate")
		oCmd.Parameters.delete("@FileLastModifiedTime")
		set
oParam=oCmd.CreateParameter("@FileID",adInteger,adParamOutput,len(sFileID),sFileID)
		oCmd.Parameters.append(oParam)
		' moved this select to a separate statement due to issues with
multiple inserts when it was lumped into the insert
		sSQL="set nocount on select ?=@@identity"
		oCmd.CommandText=sSQL
		oCmd.Execute
		sFileID=oCMD.Parameters("@FileID")
	end if

	do while (oCmd.Parameters.Count > 0)
		oCmd.Parameters.Delete 0
	loop
	GetFileID=SFileID

5. Select Into vs Insert Into - Performance Issue - SQL Server

6. INSERT INTO SELECT performance ??? 

Hello,

I am trying to copy about 300,000 rows of data from one table to another.  I 
am currently using a simple INSERT INTO SELECT FROM statement.  The target 
table already exists (so I cannot use SELECT INTO).  I am seeing pretty poor 
performance during this copy (more than 3 minutes).  The table I am copying 
from is actually the result of a TABLE valued function.

I would like to keep the source as a TABLE valued function so that the logic 
to gather the data is centrally located, but I think this may be one of the 
sources of the poor performance.

A few questions...

1. Is 300,000 rows "too much" for a TABLE valued function?
2. If 300,000 rows is "too much" for a TABLE valued function, how can I keep 
logic for building data centrally located while not using a TABLE valued 
function?  The logic to build the data is based on a few different settings 
of a parent table so a VIEW will not work...
3. Is there an alternative to INSERT INTO SELECT FROM that can be used to 
load the data more quickly?

TIA!!

7. problem w/CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE w/multiple inserts - PostgreSQL

8. Performance Difference between SELECT * and SELECT col1, col2, ...coln