Loading CSV Files Using Dynamic SQL

Question

Loading CSV Files Using Dynamic SQL

Viewing 3 posts - 16 through 17 (of 17 total)

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

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

Sean Redmond - Monday, December 10, 2018 6:07 AM
I'm preparing a pilot-study on Full-Text Search and this script will go part of the way towards it. The extended procedure master.dbo.xp_DirTree will be useful anyway.
The aim is to see how useful it might be in storing some/many/all of the company's office-documents, saved scripts and PDFs.
FTS can read & index LOBs with the help of iFilters although Jeff's comment about LOBs being the wrong way to do things is a tad worrying. I am expecting the DB to grow big quickly and one of the aims of the pilot study is to see what exactly the resource requirements are.

If you must load LOBs, make sure that you've used set the table option to store the LOBs out of row. I've recently done some testing that shows (at least for the test data I built) that INSERTs of LOBs with that option turned on make the INSERTs about 19.6% faster and it doesn't hurt reads either. It fact, it makes the reads of the LOB data (I used a LIKE search) an average of 3.9% faster for the test data I used.

Of course, forcing LOBs to be out of row so that the stuff that could fit won't end up in-row will also help your non-LOB selects sing because they won't have to read larger unused LOB data. As a bit of a sidebar and major benefit, the option also took the Clustered Index rebuilds for the test data I used down from 7500 milliseconds down to 185 milliseconds not to mention the huge savings in Logical Reads for queries that don't address the LOB data.

Here's the code to set the option for a given table.
EXEC sp_tableoption 'dbo.SomeTable','large value types out of row',1;

Please DO read up on the use of sp_tableoption for tables that already have data in them. There are extra steps required to force existing in-row lobs out-of-row. (An "in-place" update must be done).

--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)

sean redmond SSCertifiable Points: 5816 More actions · Answer 2

Hi Jeff,
I will indeed read up on it.
Thanks for the very prompt reply,
Sean.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

You're welcome. I also neglected to state that the test data consisted of 1 million rows with 2 non-LOB columns and 1 VARCHAR(MAX) column and total table size was 572MB. It was a very simple proof-of-principle test.

--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)