Performance issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance issue

Author

Topic

acid_bratt
Starting Member

13 Posts

Posted - 2003-09-12 : 23:02:34

Hi .. Is there a way I can optimize the code below so that it won't take me hours to have it done?

-- insert data from text file to temporary table
BULK INSERT GLServerDB.dbo.GLwposteddetails FROM 'D:\GLS\SrcFiles\P0803EH.txt'
WITH (FORMATFILE = 'C:\GLS\POSTED.fmt')

-- delete trailer & header records
Delete from GLwPostedDetails
Where wp_AcctYear = ' '

-- insert records from working table to true table
insert INTO GLtPostedDetails (pd_CtrlEntity, pd_BKUnit,
pd_BKDept, pd_GLCode, pd_ProdCode,
pd_BaseCurr, pd_SourceCurr,
pd_RecordType, pd_AcctYear,
pd_AcctPeriod, pd_VersionNo,
pd_EffectiveDate, pd_JEGrpNumber,
pd_JEGrpDate, pd_JENumber,
pd_JEAmount, pd_DCInd, pd_JECurrAmt,
pd_TransDesc, pd_ExchRate,
pd_ExchRTTypeCode,
pd_ExchRTEffDate,
pd_TransCode, pd_SLCode,
pd_PresentingUnit,
pd_PresentingDept,
pd_AcctRuleID, pd_RefNo,
pd_UserID, pd_SourceJESeq,
pd_UpdateDate, pd_UpdateTime)
SELECT wp_CtrlEntity, wp_BKUnit, wp_BKDept, wp_GLCode, wp_ProdCode,
wp_BaseCurr, wp_SourceCurr, wp_RecordType, wp_AcctYear,
wp_AcctPeriod, wp_VersionID, convert(smalldatetime, wp_EffectiveDate, 101) as wp_EffectiveDate, wp_JEGrpNumber, convert(smalldatetime, wp_JEGrpDate, 101) as wp_JEGrpDate, wp_JENumber,
wp_JEAmount, wp_DCInd, cast(wp_JECurrAmt as decimal) as
wp_JECurrAmt, wp_TransDesc, cast(wp_ExchRate as decimal) as wp_ExchRate, wp_ExchRTTypeCode,
case
when wp_ExchRTEffDate = ' ' then null
else convert(smalldatetime, wp_ExchRTEffDate, 101)
end as wp_ExchRTEffDate,
wp_TransCode, wp_SLCode, wp_PresentingUnit,
wp_PresentingDept, wp_AcctRuleID, wp_RefNo, wp_UserID, wp_SourceJESeq, convert(smalldatetime,wp_UpdateDate, 101) as wp_UpdateDate, convert(smalldatetime, wp_UpdateTime) as wp_UpdateTime
FROM GLwPostedDetails

delete from glwposteddetails

next file

-- addl info: text files contains million records

Thanks..

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-13 : 18:00:50

1. Is option 'select into/bulkcopy' turn on? Otherwise BULK INSERT
will be logged in, affecting performance.
sp_dboption 'myDB', 'select into/bulkcopy', true

2. Use TRUNCATE TABLE glwposteddetails instead of
delete from glwposteddetails.

acid_bratt
Starting Member

13 Posts

Posted - 2003-09-15 : 05:47:23

thanks

dsdeming

479 Posts

Posted - 2003-09-15 : 08:23:07

It may also help if you drop the indexes on GLtPostedDetails and recreate them when the insert is finished. It really depends on the number and type of indexes and on whether or not apps/users are accessing the table during all this processing.

Dennis

Bien
Starting Member

12 Posts

Posted - 2003-09-17 : 05:39:05

what is the fastest way to recreate the indexes?

****

****

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources