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
 General SQL Server Forums
 New to SQL Server Programming
 Select Query is Running Very Slow

Author  Topic 

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.
   

- Advertisement -