Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Constraint Violating Yak Guru

354 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]

	 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,
	Select RecordId,
	                          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)
		   select @chain=Cereschaincode,
				  @selltype = selltype
		   from  #workingtable where RecordId= @Min  --and Cereschaincode is not null
		  insert into [interface_i073].[dbo].[DS_Test]
	 select distinct
			  ,  [sellType]
			  ,@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] ,
			  1 ----itemReady
		  from jdx_iintegrate.dbo.tblFsCatalogueOffer
			[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
  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  
 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.03 seconds. Powered By: Snitz Forums 2000