Improving data import performance - More RAM ?

Question

Improving data import performance - More RAM ?

Viewing 4 posts - 46 through 48 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Haven't tested it, but here's how I'd convert the code... gets rid of one of the Temp Tables, a couple of variables, and the loop. It's not so important to get rid of the loop because, comparatively speaking, it won't use much time, but it does allow for some real simplification of the code. Barry has a wonderful series of articles developing on such loop elimination in SQL Server 2k5 and above. Here's the link to all of his articles...

http://qa.sqlservercentral.com/Authors/Articles/RBarry_Young/659055/

[font="Courier New"] ALTER PROCEDURE dbo.usp_ImportMultipleFiles

@FilePath VARCHAR(512),

@Pattern VARCHAR(512),

@TableName VARCHAR(512)

AS

--===== Environmental presets

SET QUOTED_IDENTIFIER OFF

SET NOCOUNT ON

--===== Create working table(s)

CREATE TABLE #DirListing

(ResumeFileName VARCHAR(512))

--===== Declare local variables and presets

DECLARE @FileName VARCHAR(512),

@Query VARCHAR(MAX)

--===== Import the pattern filtered file names

-- Note that the filepath and pattern have been quoted in case someone slips in some spaces.

SELECT @Query ='master.dbo.xp_cmdshell ''dir ' + QUOTENAME(@FilePath + @Pattern,'"') + ' /b'''

INSERT INTO #DirListing

(ResumeFileName)

EXEC (@Query)

--===== Create the query to import all qualified files

SELECT @Query = (SELECT 'BULK INSERT ' + @Tablename + ' '

+ 'FROM ' + QUOTENAME(@Filepath + ResumeFileName,'''') + ' '

+ 'WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''')' + CHAR(10)

FROM #DirListing

WHERE ResumeFileName > ' '

FOR XML PATH(''))

--===== Import all the files using the assembled query

EXEC (@Query)

[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

RBarryYoung (5/11/2009)
Heh, you're killing me here, Jeff. I never actually removed the loop. Yeah, I know, that's a first for me, but I was just trying to get all of the syntax and semantics fixed. And the thing that makes it fast (the BULK INSERT) was added by the OP, not me. I was just a debugger on this one.. 🙂

Yep... I knew that... I thought it would make a good addition to one of your articles. Haven't tested it but I "Modenized" the code using some "Barry" fine techniques. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Hey, thanks for the plug, Jeff!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

RBarryYoung (5/11/2009)
Hey, thanks for the plug, Jeff!

Absolutely no problem, ol' friend. I love that series (can't wait for the next part) and the tremendous responses you got from the "collective".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions