Inserting 25 millions records SQL server takes 3hr

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 2005 Forums

Transact-SQL (2005)

Inserting 25 millions records SQL server takes 3hr

Author

Topic

Rajesh_r_varma
Starting Member

3 Posts

Posted - 2008-05-19 : 03:21:39

Hi Teachies,

I am using SQL Server Standard Edition with good HardWare configuration.

In one of table i am inserting around 25 millions records and that takes time around more than 3 hrs.

same thing is happening while fetching records from that table.

this database contains only single file group i.e primary

and that table contains .. Clustered as well as non clustered index.

it doesnot have any Triggers.

How do i increase this performance.

Paritioning of table cannot be use in SQL Server Standard Edition.

Or Dropping all non clustered index before insert operation will improve my performance.

Please find the details ..

SERVER CONFIGURATION:

Intel pentium (R) 4 CPU
2.88 GHZ,
2.79 GHZ ,2 GB RAM

Operating System: WINDOWS 2003 R2 STANDARD SERVICE PACK 2

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

DAtABASE DETAILS:

MDF and LDF located on C:\ Drive

Available Space on C:\ DRIVE 2.94 GB

TABLES DETAILS

CREATE TABLE [dbo].[TIX_PAYMENT_SCHEDULE](

[PaymentScheduleId] [bigint] IDENTITY(1,1) NOT NULL,

[OwedAmountId] [int] NULL, --NonClusteredIndex

[ProposalId] [int] NOT NULL, --NonClusteredIndex

[BrandId] [int] NULL, -- NonClusteredIndex

[DueDate] [datetime] NULL, --NonClusteredIndex

[OverdueDate] [datetime] NULL,--NonClusteredIndex

[ExpectedAmount] [decimal](18, 2) NULL,

[TransactionStatusId] [tinyint] NULL,--NonClusteredIndex

[IsLate] [char](1) NULL,

[IsPaymentReceived] [char](1) NULL,

[ScheduleBatchJournalId] [bigint] NULL,--NonClusteredIndex

[IsValidSchedule] [char](1) NULL,

[RuleId] [int] NULL,

[ActionId] [int] NOT NULL,

[ReasonId] [tinyint] NULL,

[Comments] [nvarchar](2000) NULL,

[NoofDays] [int] NULL,

[ActualAmountReceived] [decimal](18, 2) NULL,

[CreatedBy] [uniqueidentifier] NULL,

[CreatedDateTime] [datetime] NOT NULL,

[LastUpdatedBy] [uniqueidentifier] NULL,

[LastUpdatedDateTime] [datetime] NOT NULL,

[CaseScheduleId] [bigint] NULL,--NonClusteredIndex

[ActionDate] [datetime] NULL,

[HasExactMatch] [char](1) NULL,

[IsCatchupBalanced] [char](1) NULL,

[HasModified] [char](1) NULL,--NonClusteredIndex

[PendDate] [datetime] NULL,

[IsAutoAccept] [char](1) NULL,

[CatchupBalanceIdentifier] [uniqueidentifier] NULL,--NonClusteredIndex

CONSTRAINT [PK_TIX_PAYMENT_SCHEDULE] PRIMARY KEY CLUSTERED

(

[PaymentScheduleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

TABLE 2

CREATE TABLE [dbo].[TIX_PAYMENT_CASE_SCHEDULE](

[CaseScheduleId] [bigint] IDENTITY(1,1) NOT NULL,

[ProposalId] [int] NOT NULL,--NonClusteredIndex

[DueDate] [datetime] NOT NULL,

[OverDueDate] [datetime] NOT NULL,

[TotalExpectedAmount] [decimal](18, 2) NOT NULL,

[TotalActualPaymentReceived] [decimal](18, 2) NOT NULL,

[TransactionStatusId] [int] NOT NULL,--NonClusteredIndex

[ActionId] [int] NULL,

[CreatedBy] [uniqueidentifier] NULL,

[CreatedDateTime] [datetime] NULL,

[LastUpdatedBy] [uniqueidentifier] NULL,

[LastUpdatedDateTime] [datetime] NULL,

[IsValidSchedule] [char](1) NULL,

[ScheduleBatchJournalId] [bigint] NULL,

[IsCatchupBalanced] [char](1) NULL,

[HasModified] [char](1) NULL,--NonClusteredIndex

[CatchupBalanceIdentifier] [uniqueidentifier] NULL,

CONSTRAINT [PK_TIX_PAYMENT_CASE_SCHEDULE] PRIMARY KEY CLUSTERED

(

[CaseScheduleId] ASC

)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

STORED PROCEDURE:

CREATE PROC [dbo].[TIX_PRC_GENERATE_PAYMENTSCHEDULE_DATA]
(
@XMLParams XML,
@ToDate datetime,
@HasModified char(1)

)
AS
BEGIN
SET NOCOUNT ON
--Exception Handling Variable Declaration
DECLARE @ErrorMessage NVARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(50),
@ErrorLine INT,
@ErrorDesc NVARCHAR(100)

DECLARE @XMLPayment INT
BEGIN TRY
IF @XMLParams IS NOT NULL
BEGIN --BEGIN IF

SET @ErrorDesc='Error Occured While Inserting into TIX_PAYMENT_SCHEDULE FROM XML'

INSERT INTO TIX_PAYMENT_SCHEDULE
(
OwedAmountId,
ProposalId,
BrandId,
DueDate,
OverdueDate ,
CreatedDateTime,
LastUpdatedDateTime,
ExpectedAmount,
ActualAmountReceived,
ScheduleBatchJournalId,
RuleId,
TransactionStatusId,
ActionId,
IsLate,
IsPaymentReceived ,
IsValidSchedule,
--Added by DC : 119
IsCatchupBalanced,
CatchupBalanceIdentifier,
HasModified
---------------------------------------------------
)
SELECT
Main.ELEMENT.value('(OwedAmountId)[1]','int') AS OwedAmountId,
Main.ELEMENT.value('(ProposalId)[1]','int') AS ProposalId,
Main.ELEMENT.value('(BrandId)[1]','int') AS BrandId,
convert(datetime,Main.ELEMENT.value('(DueDate)[1]','varchar(100)')) AS DueDate,
convert(datetime,Main.ELEMENT.value('(OverdueDate)[1]','varchar(100)')) AS OverdueDate,
@ToDate AS CreatedDateTime,
@ToDate AS LastUpdatedDateTime,
convert(decimal(18,2),Main.ELEMENT.value('(ExpectedAmount)[1]','varchar(100)')) AS ExpectedAmount,
convert(decimal(18,2),Main.ELEMENT.value('(ActualAmountReceived)[1]','varchar(100)')) AS ActualAmountReceived,
Main.ELEMENT.value('(ScheduleBatchJournalId)[1]','bigint') AS ScheduleBatchJournalId,
Main.ELEMENT.value('(RuleId)[1]','int') AS RuleId,
Main.ELEMENT.value('(TransactionStatusId)[1]','int') AS TransactionStatusId,
Main.ELEMENT.value('(ActionId)[1]','int') AS ActionId,
Main.ELEMENT.value('(IsLate)[1]','char(1)') AS IsLate,
Main.ELEMENT.value('(IsPaymentReceived)[1]','char(1)') AS IsPaymentReceived,
Main.ELEMENT.value('(IsValidSchedule)[1]','char(1)') AS IsValidSchedule

--Added by DC for 119

,Main.ELEMENT.value('(IsCatchupBalanced)[1]','char(1)') AS IsCatchupBalanced
,Main.ELEMENT.value('(CatchupBalanceIdentifier)[1]','nvarchar(1000)') AS CatchupBalanceIdentifier
,@HasModified
---------------------------------------------------------------------

FROM @XMLParams.nodes ('(/ROOT/DATA)') AS Main(ELEMENT)

END--END IF

END TRY--Main END TRY
BEGIN CATCH --Main BEGIN CATCH

SELECT @ErrorMessage = @ErrorDesc+Char(13)+Error_Message(),
@ErrorSeverity = Error_Severity(),
@ErrorState = Error_State(),
@ErrorNumber = Error_Number(),
@ErrorProcedure = Error_Procedure(),
@ErrorLine = Error_Line()
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber,
@ErrorProcedure,
@ErrorLine
)
END CATCH --Main END CATCH
END --Main END

STOREDPROCEDURE 2

CREATE PROCEDURE [dbo].[TIX_PRC_GET_PAYMENTSCHEDULE_SCHEDULE_FOR_DATE_RANGE]
(

@ToDate datetime,
@IsValidSchedule char(1)

)
AS
BEGIN
SET NOCOUNT ON

--Execption Handling Variable Declaration
DECLARE @ErrorMessage NVARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(50),
@ErrorLine INT,
@ErrorDesc NVARCHAR(100)

BEGIN TRY --Exception Handling
SET @ErrorDesc='Error Occured while fetching records from TIX_PAYMENT_SCHEDULE'

SELECT
PaymentScheduleId,
OwedAmountId,
ProposalId,
DueDate,
OverdueDate,
ExpectedAmount,
TransactionStatusId,
IsPaymentReceived,
IsLate,
ActionId,
ActualAmountReceived,
IsValidSchedule,
BrandId,
CaseScheduleId,
ReasonId,
Comments,
NoOfDays,
ActionDate,
IsCatchupBalanced,
CatchupBalanceIdentifier,
HasModified
from TIX_PAYMENT_SCHEDULE with (nolock)
WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule

SELECT DISTINCT OwedAmountId,proposalId,brandId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By OwedAmountId,ProposalId,BrandId asc
SELECT DISTINCT ProposalId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By ProposalId asc

END TRY
BEGIN CATCH
SELECT @ErrorMessage=@ErrorDesc+CHAR(13)+ Error_Message(),
@ErrorNumber=Error_Number(),
@ErrorState=Error_State(),
@ErrorProcedure=Error_Procedure(),
@ErrorLine=Error_Line(),
@ErrorSeverity=Error_Severity()

RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber,
@ErrorProcedure,
@ErrorLine
)

END CATCH

END

Thanks & Regards

Rajesh Varma

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 03:29:58

Remove all (10) indexes, insert the data and recreate all indexes.

E 12°55'05.25"
N 56°04'39.16"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-19 : 04:23:48

...and make sure the datafile has enough free space in advance of the insert operation. I'd also seriously reconsider using another format than xml if the file contains 25 mill rows of data but if that's not an option I would also assume that passing the entire xml as a parameter to the procedure would cause some overhead... I have never worked with xml and sql server before but isn't it possible to use OPENROWSET or somthing like that instead? I really don't know about this but I would think that such a method would be faster.

--
Lumbago

Rajesh_r_varma
Starting Member

3 Posts

Posted - 2008-05-23 : 11:06:40

I have changed my sql server setting of memory

on server properties tab --> maximum servery memory to 1536

and minimum memory per query -->5120

and also implemented suggestion of Database Tunning Advisior

I got good amount of performance enhancements. for 15Lakh's Records.

But my problem here is , i am Excepted to get around 3 crore records.

and i am using Standard edition . i cant use table partioning

So in that case . if i increase my RAM memory to 4GB ,

and allocate 3GB of RAM MEMORY to SQL SERVER .....is it going to slove my problem..

wating for valuable reply

Thanks

Rajesh Varma.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-23 : 11:15:50

"DAtABASE DETAILS:

MDF and LDF located on C:\ Drive

Available Space on C:\ DRIVE 2.94 GB"

This space looks really less for the operation you are doing.Log files will get filled up as you are doing massive inserts.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-23 : 11:54:06

I agree with sodeep, doesn't APPEAR there is enough disk space to handle this. Set recovery mode to simple, drop you indexes, run your insert (keeping your fingers crossed). Attempt to recreate your indexes (it's going to depend on disk space). Once complete, set recovery mode back to full (or whatever you had it before setting it to simple). Good luck. And think about moving the database off of the c:\ drive (not best practices) to another partition that has more free space. It looks like you are going to need it.

Terry

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-23 : 12:13:16

hope it is not a production server! either way you really need to think about recreating a new machine or redo the current one with way much bigger C drive my goodness only 2.94 gig? as the other folks said that is way too small
also why xml as the parm, is that by design or just for the sake of doing it by xml. how big is the xml file, where is it located @, on the c drive? is the server raided with multi disks or just one physical hard disk? how big is the hard disk? is it partitioned is so how? C: and D: or what?
for short term solution what the others have mentioned is perfect but for long term solution you need to rethink your setup

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources