Import is making my tempdb huge

I am having this big issue with a table that I have. When I try to import daily data (about 1500 records) it takes forever and it balloons my tempdb from less than 1 gig to over 10 gigabytes. There are about 900K records in the table, not a big table by no means.

I have 3 constraints (nothing big 2 automatically set the default to 0 and one to a value called 'EN'). I have 2 triggers, one to trigger modified date and one to change created date. The code for the table is  below


USE [PBJ_2BE]
GO

/****** Object:  Table [dbo].[ACH_1114]    Script Date: 11/09/2015 12:22:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ACH_1114](
	[ID1] [int] IDENTITY(1,1) NOT NULL,
	[As Of Date] [nvarchar](255) NULL,
	[As of Time] [nvarchar](255) NULL,
	[Bank ID] [nvarchar](255) NULL,
	[Bank Name] [nvarchar](255) NULL,
	[State] [nvarchar](255) NULL,
	[Account Number] [nvarchar](255) NULL,
	[Account Type] [nvarchar](255) NULL,
	[Account Name] [nvarchar](255) NULL,
	[Currency] [nvarchar](255) NULL,
	[IBAN] [nvarchar](255) NULL,
	[Debit Amount] [float] NULL,
	[Credit Amount] [float] NULL,
	[Entry Class Code] [nvarchar](255) NULL,
	[Entry Class Description] [nvarchar](255) NULL,
	[Discretionary Data] [nvarchar](255) NULL,
	[Sending Company ID] [nvarchar](255) NULL,
	[ID] [nvarchar](255) NULL,
	[Sending Company Name] [nvarchar](255) NULL,
	[Individual Name] [nvarchar](255) NULL,
	[First Addenda] [nvarchar](255) NULL,
	[Value Date] [nvarchar](255) NULL,
	[Remittance Information] [nvarchar](255) NULL,
	[End to End Reference] [nvarchar](255) NULL,
	[Bank Reference] [nvarchar](255) NULL,
	[Tfr Amt and FX Rate] [nvarchar](255) NULL,
	[Payment Purpose] [nvarchar](255) NULL,
	[Remittance Type] [nvarchar](255) NULL,
	[Status] [nvarchar](255) NOT NULL,
	[PB_pmt] [bit] NOT NULL,
	[Process] [bit] NOT NULL,
	[CreatedDate] [datetime] NULL,
	[ModifiedDate] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ACH_1114] ADD  CONSTRAINT [DF_ACH_1114_Status]  DEFAULT ('EN') FOR [Status]
GO

ALTER TABLE [dbo].[ACH_1114] ADD  CONSTRAINT [DF_ACH_1114_PB_pmt]  DEFAULT ((0)) FOR [PB_pmt]
GO

ALTER TABLE [dbo].[ACH_1114] ADD  CONSTRAINT [DF_ACH_1114_Process]  DEFAULT ((0)) FOR [Process]
GO

Open in new window


it takes about 35 minutes to import this via ssis and it balloons my tempdb.

It also makes my CPU Usage at 100%, which is crazy.

any feedback would be appreciated
damixaAsked:
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.

lcohanDatabase AnalystCommented:
Please see optimizations for tempdb at links below and I would add multiple files ASAP if you don't have them already. Not much can be done related to size other than setting the recovery to simple as mentioned below.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
http://www.brentozar.com/sql/tempdb-performance-and-configuration/


As far as CPU intensive please look for following factors affecting this:

1. CLUSTERED index/PK in the INSERT table.
2. Foreign Keys/Constraints  in the INSERT table - make sure appropriate indexes exists to help quick check of both FKey/constraint.
3. Triggers on  the INSERT table.
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
Jose TorresSenior SQL Server DBACommented:
How are you importing the data?
0
damixaAuthor Commented:
I am importing it via the import process in access. I noticed that if I do an SSIS job the tempdb is not getting big, but it is still very slow.
0
Jose TorresSenior SQL Server DBACommented:
SSIS is generally better at this type of work.
To speed up SSIS try the following.
Change the packet size of the connection manager this is usually too low at 4kb max allowed is 32kb.
Make sure you use the fastload option.
If it still takes too long run a profiler while you run the package.
0
Anthony PerkinsCommented:
Totally unrelated, but this is a very bad idea:
      [Debit Amount] [float] NULL,
      [Credit Amount] [float] NULL,

float (like double and single in MS Access) is an approximate data type and you are liable to end up with 10.1999999999999 for 10.20.  So unless you are dealing with astronomical numbers that require exponential values (or the US budget) never ever use float (or real).  Instead use numeric (or decimal), money or smallmoney.
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 2008

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.