How to speed up this?

Question

How to speed up this?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Jonathan AC Roberts wrote:

Jeff Moden wrote:
If you want a load into a new table, then create the empty table AND create the Clustered Index. Then, forget SSIS... use BULK INSERT with the TABLOCK option and read up on the requirements for MINIMAL LOGGING.
If your "Minimal Logging" is successful, the load will be faster than with loading into a bare table with logging AND you won't have to build the Clustered Index afterwards and you won't have the "doubling of space required".
There is a way to do such "Minimal Logging" in SSIS but I don't know how to do it because I avoid SSIS. Phil Parkin would likely be the one to help there.
You can just add a script control and put a any T-SQL statement in it. So you can do minimal logging from SSIS.

Rather than this, I suspect that Jeff was referring to FastLoad options within an SSIS Dataflow component.

FastLoad

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

water490 SSC Eights! Points: 959 More actions · Answer 2

thank you everyone for your replies. very helpful

i am going to disable that component and see how it affects SSIS run time. i will do this for the next few weeks to get some data and then decide on next steps.

one thing i do want to know...is there a way to find out how far the table is from being properly indexed for maximum performance? are there any metrics we can use that help us know if the table is properly indexed or not? if there is such a metric then i want to track that too in addition to the SSIS run time.

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 3

Phil Parkin wrote:

Jonathan AC Roberts wrote:
Jeff Moden wrote:
If you want a load into a new table, then create the empty table AND create the Clustered Index. Then, forget SSIS... use BULK INSERT with the TABLOCK option and read up on the requirements for MINIMAL LOGGING.
If your "Minimal Logging" is successful, the load will be faster than with loading into a bare table with logging AND you won't have to build the Clustered Index afterwards and you won't have the "doubling of space required".
There is a way to do such "Minimal Logging" in SSIS but I don't know how to do it because I avoid SSIS. Phil Parkin would likely be the one to help there.
You can just add a script control and put a any T-SQL statement in it. So you can do minimal logging from SSIS.
Rather than this, I suspect that Jeff was referring to FastLoad options within an SSIS Dataflow component.

Yes, a Data Flow control is the normal way to do it and it has a data pump so is very fast just like bulk insert.

But I thought Jeff was looking for a way to do it in SSIS using standard T-SQL.

water490 SSC Eights! Points: 959 More actions · Answer 4

water490 wrote:

thank you everyone for your replies. very helpful
i am going to disable that component and see how it affects SSIS run time. i will do this for the next few weeks to get some data and then decide on next steps.
one thing i do want to know...is there a way to find out how far the table is from being properly indexed for maximum performance? are there any metrics we can use that help us know if the table is properly indexed or not? if there is such a metric then i want to track that too in addition to the SSIS run time.

is anyone able to help me with this?