Inserting 25 Millions Records SQL Server Takes 3hr

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