SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Query is Running Very Slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 08/15/2013 :  08:33:32  Show Profile  Reply with Quote
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
  






Vijay is here to learn something from you guys.

Edited by - vijays3 on 08/15/2013 08:37:27
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000