PERFORMANCE ISSUE

Question

PERFORMANCE ISSUE

niranjankumar_k

Ten Centuries

Points: 1075
More actions
May 20, 2008 at 2:18 am

#121432

BCP done initially in staging tables ( 2 ( 1000 columns)) which does have any index . by using this table records will be populated to main table . in this scenario this staging table uses join with main table for updation. so main table column has index ( non cluster or cluster ) but this staging table does not have. cud i add non cluster index on this staging ??? if i add will it affect BCP ???
this process runs 6.30 minutes ( bcp takes only 10 minutes for 3.5 lak records) . and staging table used in 75% of joins
by doing bulk insert and deletion will it make any impact ???
noncluster will not affect PERFORMANCE like cluster while insertion and deletion am i correct ??

Viewing 4 posts - 1 through 4 (of 4 total)

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

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

The following is from Books Online and should help you...

Logged and Minimally Logged Bulk Copy Operations
When using the full recovery model, all row-insert operations performed by bcp are logged in the transaction log. For large data loads, this can cause the transaction log to fill rapidly. To help prevent the transaction log from running out of space, a minimally logged bulk copy can be performed if all of these conditions are met:
The recovery model is simple or bulk-logged.
The target table is not being replicated.
The target table does not have any triggers.
The target table has either 0 rows or no indexes.
The TABLOCK hint is specified. For more information, see Controlling the Locking Behavior.
Any bulk copy into an instance of Microsoft® SQL Server™ that does not meet these conditions is logged.

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Create indexes after the bcp/Bulk insert. That way they won't interfere with the load, but will be present when you're querying the table later.

As always, test carefully to ensure that performance has improved.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Indexes don't interfere with the load if the table is empty at the beginning of the load.

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