May 19, 2008
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
View 6 Replies
View Related
Mar 27, 2006
I have a table with first name, last name, SSN(social security number)and other columns.I want to assign group number according to this business logic.1. Records with equal SSN and (similar first name or last name) belongto the same group.John Smith 1234Smith John 1234S John 1234J Smith 1234John Smith and Smith John falls in the same group Number as long asthey have similar SSN.This is because I have a record of equal SSN but the first name andlast name is switched because of people who make error inserting lastname as first name and vice versa. John Smith and Smith John will haveequal group Name if they have equal SSN.2. There are records with equal SSN but different first name and lastname. These belong to different group numbers.Equal SSN doesn't guarantee equal group number, at least one of thefirst name or last name should be the same. John Smith and Dan Brownwith equal SSN=1234 shouldn't fall in the same group number.Sample data:Id Fname lname SSN grpNum1 John Smith 1234 12 Smith John 1234 13 S John 1234 14 J Smith 1234 15 J S 1234 16 Dan Brown 1234 27 John Smith 1111 3I have tried this code for 65,000 rows. It took 20 minute. I have torun it for 21 million row data. I now that this is not an efficientcode.INSERT into temp_FnLnSSN_grpSELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,(SELECT 1 + count(*)FROM distFLS AS c2WHERE c2.ssn < c1.ssnor (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =substring(c1.fname,1,1) or substring(c2.lname,1,1) =substring(c1.lname,1,1)or substring(c2.fname,1,1) =substring(c1.lname,1,1) or substring(c2.lname,1,1) =substring(c1.fname,1,1)))) AS group_numberFROM distFLS AS c1JOIN tu_people_data AS c3ON (c1.ssn = c3.ssn andc1.fname = c3.fname andc1.lname= c3.lname)dist FLS is distinct First Name, last Name and SSN table from thepeople table.I have posted part of this question, schema one week ago. Please referthis thread.http://groups.google.com/group/comp...6eb380b5f2e6de6
View 5 Replies
View Related
