The query execution taking too long

[Ganesh Subbiah]

Hi,

- cross posting from Sqlserver programming newsgroup -

----- Original Message -----
From: "Ganesh Subbiah" <gan...@chellasoft.com>
Newsgroups: microsoft.public.sqlserver.programming
Sent: Tuesday, September 08, 2009 2:41 PM
Subject: The query execution taking too long

> Hello,
>
> My server machine details are (2 x 2.26 GHZ Quad Core Processor, 12 GB
> RAM,
> SQL Server 2005 Enterprise Edition, 500 GB SATA HDD)
>
> I am using the following query to execute in SQL 2005 Server,
>
> SET @sqlString = 'INSERT INTO [VoucherDetail' + @YearName + '](
> [VoucherHeaderID], [AccountID], [AccountCode], [SubAccountType],
> [SubAccountCode], [CurrencyCode],
> [FCDrAmount], [FCCrAmount], [BCDrAmount], [BCCrAmount],
> [ExchangeRate], [Narration], [Active], [ModifierID], [ModifiedDateTime]
> )
> SELECT
> [VoucherHeaderID], [AccountID], [AccountCode], [SubAccountType],
> [SubAccountCode], [CurrencyCode],
> [FCDrAmount], [FCCrAmount], [BCDrAmount], [BCCrAmount],
> 1, [Narration], 1, ModifierID, ModifiedDateTime
> FROM
> ' + @T_VoucherDetail
> EXECUTE(@sqlString)
>
> In this query @YearName = 2009, VoucherDetail2009 is the permanent table
> name
> @T_VoucherDetail is the temporay table
>
> In @T_VoucherDetail table I am having 8,00,000 records.
> In VoucherDetail2009 table I am having 31,14,516 records.
>
> In VoucherDetail2009 table has the following structure
>
> CREATE TABLE [dbo].[VoucherDetail2009](
> [IDCol] [int] IDENTITY(1,1) NOT NULL,
> [VoucherHeaderID] [int] NOT NULL,
> [AccountID] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [AccountCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [SubAccountType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [SubAccountCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [CurrencyCode] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [FCDrAmount] [numeric](15, 4) NOT NULL,
> [FCCrAmount] [numeric](15, 4) NOT NULL,
> [BCDrAmount] [numeric](15, 4) NOT NULL,
> [BCCrAmount] [numeric](15, 4) NOT NULL,
> [ExchangeRate] [numeric](12, 6) NOT NULL,
> [Narration] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Active] [bit] NOT NULL,
> [ModifierID] [int] NOT NULL,
> [ModifiedDateTime] [datetime] NOT NULL,
> [LastChanged] [timestamp] NOT NULL,
> CONSTRAINT [PK_VoucherDetail2009] PRIMARY KEY CLUSTERED
> (
> [IDCol] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> @T_VoucherDetail has the same structure with out clustered index.
>
> The query taking 5 minutes 54 seconds
>
> Is there anyway to reduce the insertion time in my query?
>
> Can anybody help?
>
>
> --
> Regards,
> Ganesh
>
>

[Jeremy A. Holovacs]

please correct your punctuation,

Is it 8,000,000 or 800,000 records?
31,140,516 records? 3,114,516?

It makes a significant difference. Please take care when providing
statistics.

--
-------------------------------
Jeremy Holovacs
MCITP, MCDBA, MCSE
"Ganesh Subbiah" <gan...@chellasoft.com> wrote in message
news:eP71wNHM...@TK2MSFTNGP04.phx.gbl...