Link to home
Start Free TrialLog in
Avatar of Stephen Forero
Stephen ForeroFlag for United States of America

asked on

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

Avatar of Vikas Garg
Vikas Garg
Flag of India image

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
Avatar of Stephen Forero

ASKER

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?
SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.
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!
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.