sqlbulkupload slow... possibly need some type of index?

Good Morning,

I do not know much about sql index but from what I have read up on there needs to be index on tables to make them run efficiently.
I found the easiest way for me to do this is to run SSMS Execution Plan, where it will tell you the recommended index to put on a table.

So far based on a stored procedure I wrote, I put one index on the main table.  Below is the table design and the index I put on.
The issue is when I do a sqlbulkupload for about 20,000 rows it takes a full 5 minutes.  Which seems excessive.

Would putting the right index on the table solve this issue?
And if so how should I create it since I can't see EXECUTION PLAN on SSMS in a .NET sqlbulkupload.

Thanks in advance!!

USE [PrimeDB]
GO

/****** Object:  Table [dbo].[GMI_adatpos]    Script Date: 8/19/2015 8:14:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GMI_adatpos](
	[FILEDATE] [date] NULL,
	[PRECID] [varchar](50) NULL,
	[PFIRM] [varchar](50) NULL,
	[POFFIC] [varchar](50) NULL,
	[PACCT] [varchar](50) NULL,
	[PATYPE] [varchar](50) NULL,
	[PCUSIP] [varchar](50) NULL,
	[PCTYM] [varchar](50) NULL,
	[PSBCUS] [varchar](50) NULL,
	[PSTYPE] [varchar](50) NULL,
	[PSUBTY] [varchar](50) NULL,
	[PSTYP2] [varchar](50) NULL,
	[PSTRIK] [float] NULL,
	[PEXPDT] [varchar](50) NULL,
	[PTDATE] [varchar](50) NULL,
	[PTPRIC] [float] NULL,
	[PBS] [varchar](50) NULL,
	[PSPRED] [varchar](50) NULL,
	[PACNTY] [varchar](50) NULL,
	[PRR] [varchar](50) NULL,
	[PQTY] [int] NULL,
	[PSDSC1] [varchar](50) NULL,
	[PSDSC2] [varchar](50) NULL,
	[PMKVAL] [float] NULL,
	[PYSTMV] [varchar](50) NULL,
	[PMULT] [varchar](50) NULL,
	[PSDATE] [varchar](50) NULL,
	[PDELTA] [varchar](50) NULL,
	[PLTDAT] [varchar](50) NULL,
	[PPCNTY] [varchar](50) NULL,
	[PEXCH] [varchar](2) NULL,
	[PFC] [varchar](50) NULL,
	[PTYPE] [varchar](50) NULL,
	[PSYMBL] [varchar](50) NULL,
	[PPTYPE] [varchar](50) NULL,
	[PSUBEX] [varchar](50) NULL,
	[PMATDT] [varchar](50) NULL,
	[PPRTPR] [varchar](50) NULL,
	[PBASIS] [varchar](50) NULL,
	[PCLOSE] [float] NULL,
	[PPRCDT] [varchar](50) NULL,
	[PPRTCP] [varchar](50) NULL,
	[PUNDCP] [varchar](50) NULL,
	[PPRTUC] [varchar](50) NULL,
	[PPRVCP] [float] NULL,
	[PCURSY] [varchar](50) NULL,
	[PCURCD] [varchar](50) NULL,
	[PCMNT1] [varchar](50) NULL,
	[PCMNT2] [varchar](50) NULL,
	[PCMNT3] [varchar](50) NULL,
	[PRTHT] [varchar](50) NULL,
	[POC] [varchar](50) NULL,
	[PGIVIO] [varchar](50) NULL,
	[PGIVF#] [varchar](50) NULL,
	[PCARD] [varchar](50) NULL,
	[PCOMM] [float] NULL,
	[PFEE1] [float] NULL,
	[PFEE2] [float] NULL,
	[PFEE3] [float] NULL,
	[PFEE4] [float] NULL,
	[PFEE5] [float] NULL,
	[PFEE6] [float] NULL,
	[PFEE7] [float] NULL,
	[PFEE8] [float] NULL,
	[PFEE9] [float] NULL,
	[PGICHG] [float] NULL,
	[PBKCHG] [float] NULL,
	[POTHER] [float] NULL,
	[PGROSS] [float] NULL,
	[PNET] [float] NULL,
	[PATCOM] [varchar](50) NULL,
	[PATFE1] [varchar](50) NULL,
	[PBQTY] [int] NULL,
	[PSQTY] [int] NULL,
	[PTRACE] [varchar](50) NULL,
	[PMVARN] [varchar](50) NULL,
	[PUNDCN] [varchar](50) NULL,
	[PCURAT] [varchar](50) NULL,
	[PLOC] [varchar](50) NULL,
	[PCLASS] [varchar](50) NULL,
	[PTIME] [varchar](50) NULL,
	[PATFE2] [varchar](50) NULL,
	[PATOTH] [varchar](50) NULL,
	[PCALC] [varchar](50) NULL,
	[PLEVEL] [varchar](50) NULL,
	[PREFNO] [varchar](50) NULL,
	[PCHIT] [varchar](50) NULL,
	[PTTYPE] [varchar](50) NULL,
	[PBEXCH] [varchar](50) NULL,
	[PBCODE] [varchar](50) NULL,
	[PBYELLOW] [varchar](50) NULL,
	[PCREXCH] [varchar](50) NULL,
	[PCRUNDL] [varchar](50) NULL,
	[PCRROOT] [varchar](50) NULL,
	[PEREXCH] [varchar](50) NULL,
	[PERUNDL] [varchar](50) NULL,
	[PERROOT] [varchar](50) NULL,
	[PBTICK] [varchar](50) NULL,
	[PBTICK2] [varchar](50) NULL,
	[POSITIONID] [uniqueidentifier] NOT NULL,
	[ACCOUNTID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_GMI_adatpos] PRIMARY KEY CLUSTERED 
(
	[POSITIONID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO

Open in new window


my 1 index so far
USE [PrimeDB]
GO

/****** Object:  Index [adatPosIndexOnFiledate]    Script Date: 8/19/2015 8:24:23 AM ******/
CREATE NONCLUSTERED INDEX [adatPosIndexOnFiledate] ON [dbo].[GMI_adatpos]
(
	[FILEDATE] ASC
)
INCLUDE ( 	[POFFIC],
	[PACCT],
	[PCTYM],
	[PSBCUS],
	[PSUBTY],
	[PSTRIK],
	[PBS],
	[PQTY],
	[PSDSC1],
	[PMKVAL],
	[PEXCH],
	[PFC],
	[PTYPE],
	[PCLOSE],
	[PCURSY],
	[PBEXCH],
	[ACCOUNTID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Open in new window

Stephen ForeroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Putting index on any table would fast the retrieval not inserting.

If you add index to your table data inserting will slower and data retrieval will be faster.

However you can disable them before insert and enable after insert

--Disable Index
ALTER INDEX [IXYourIndex] ON YourTable DISABLE
GO

--Enable Index
ALTER INDEX [IXYourIndex] ON YourTable REBUILD
GO
0
Stephen ForeroAuthor Commented:
thanks, I didn't know that.  Is disabling and enabling index an efficient method?
also is there a faster way to update a table than sqlbulkupload?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Disabling and Enabling index is one of the mostly used and simplest way to improve the Bulk uploads.

Below are some good ways to improve BULK INSERT operations :

>> Using TABLOCK as query hint.
>>Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
>>Changing the Recovery model of database to be BULK_LOGGED during the load operation.
>>If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
>>Using Trace Flag 610 at the beginning of BULK INSERT operation.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
You should make the FILEDATE your clustered index and the primary key nonclustered.  A guid for a clustered index will cause huge overhead.  Here are the commands to do that:

DROP INDEX [adatPosIndexOnFiledate] ON [dbo].[GMI_adatpos];
ALTER TABLE [dbo].[GMI_adatpos] DROP CONSTRAINT [PK_GMI_adatpos] ;

CREATE CLUSTERED INDEX CL_GMI_adatpos ON [dbo].[GMI_adatpos] ( FILEDATE ) WITH ( FILLFACTOR = 99, SORT_IN_TEMPDB = ON );

ALTER TABLE [dbo].[GMI_adatpos] ADD
CONSTRAINT [PK_GMI_adatpos]  PRIMARY KEY NONCLUSTERED
( [POSITIONID] ) WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON );
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen ForeroAuthor Commented:
thanks Scott, to be honest I don't even need the column POSITIONID.  Would it benefit me to make Filedate clusteredIndex like you said and have no nonclustered?
0
Scott PletcherSenior DBACommented:
Would it benefit me to make Filedate clusteredIndex
Absolutely, 100%, whatever you do or don't do with PositionId.


and have no nonclustered?
Eh, whatever.  One nonclustered index on that won't hurt that much, so personally I'd say go ahead and create it.  Later check its usage: if it's not being used, then you can drop it.
0
Stephen ForeroAuthor Commented:
thanks Vikas/Scott.
I started by completely deleting the POSITIONID column, and made the clustered index on Filedate.
That increased the speed enough for not get these slow timeouts.  Next I'm going to disable and enable the index as well.
Thanks for everyones help!
0
Scott PletcherSenior DBACommented:
Putting index on any table would fast the retrieval not inserting.

That's just not true.  An extremely random clustered index, esp. one with a longer key, such as a guid, will dramatically slow down INSERTs to the table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.