vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2013-08-15 : 08:33:32
|
[code]Hi All,I have a stored procedure where I am trying to insert data in my DS_Test table but the select statement is running very slow.I am trying to load only 3 records ( my stage table has more than 3000 records)to check why this very slow . My select statement is calling nested functions.All my functions are using the same column names [ChainID],Salesprice and selltype to filter the records.I have created the Nonclustered index using column(ChainID, CountryCode, Name, sellType) and clustered Index on Id column of jdx_iintegrate.dbo.tblFsCatalogueOffer table (jdx_iintegrate is a different database).It is extremely slow to load 3000 records it has taken 4 hours.Also If I am checking with 3 records its taking time and going in infinite loop.I am not clear whats is the problem..Can you guys advise me on this.USE [interface_i073]GO Create table #workingtable ( RecordId int, artno VARCHAR(18) NOT NULL, salesorg varchar(50) NOT NULL, price money NOT NULL, selltype varchar(20) NULL, Cereschaincode varchar(5)) Create table #StageTable ( RecordId int identity(1,1), artno VARCHAR(18) NOT NULL, salesorg varchar(50) NOT NULL, price money NOT NULL, selltype varchar(20) NULL, Cereschaincode varchar(5)) insert into #StageTable select artno ,salesorg,price,selltype ,map.Cereschaincode from dbo.stg_jdonline_article stg left join dbo.v_minimap_sap_ceres_sitecode map on stg.salesorg =map.SapSalesOrganisation --- where Cereschaincode is not null insert into #workingtable(RecordId, artno, salesorg, price, selltype, Cereschaincode) Select RecordId, artno, salesorg, price, selltype, Cereschaincode from #StageTable where Cereschaincode is not null declare @Min int, @Max int, @chain varchar(5), @sellPrice money, @artno varchar(18) , @salesorg varchar(255), @selltype varchar(255), @ErrorMessage nvarchar(4000), @ErrorSeverity int, @ErrorState int, @Timestamp Datetime = Getdate() set @Min=1 set @Max= 3 select @Max= MAX(RecordId) from #workingtable ---- Select @Min, @Max while (@Min<=@Max) begin select @chain=Cereschaincode, @sellPrice=price, @artno=artno, @salesorg=salesorg, @selltype = selltype from #workingtable where RecordId= @Min --and Cereschaincode is not null insert into [interface_i073].[dbo].[DS_Test] ([ChainId] ,[ArticleNo] ,[SalesOrg] ,[CountryCode] ,[Range_Start] ,[Range_End] ,[Term_Period] ,[Interest_rate] ,[selltype] ,[Status] ,[Deposit] ,[EndDate] ,[Name] ,[SellingPrice] ,[DepositAmount] ,[MonthlyInstallment] ,[TotalCredit] ,Sql_inserted ,StatusId) select distinct [ChainID] ,@artno ,@salesorg ,[CountryCode] ,[range_start] ,[range_end] ,[term_period] ,[interest_rate] , [sellType] ,[Status] ,[Deposit] ,[EndDate] ,[Name] ,@sellPrice AS [Selling Price] ,(@sellPrice/[Deposit]) AS [Deposit Amount] ,[dbo].[funct_MonthlyPayment](@sellPrice,@chain,@selltype) AS [Monthly Instalment] ,[dbo].[funct_TotalCredit](@sellPrice,@chain,@selltype) AS [Total Credit] , @Timestamp, 1 ----itemReady from jdx_iintegrate.dbo.tblFsCatalogueOffer where [ChainID] = @chain and @sellPrice > [range_start] AND @sellPrice < [range_end] AND [sellType] = @selltype AND [CountryCode] = 'R' AND [Name] = 'Standard' delete from #workingtable where recordid=@Min set @Min=@Min+1 end drop table #workingtable drop table #StageTable [/code]Vijay is here to learn something from you guys.
|
|