July 2, 2009 at 3:14 am
hi,
i have pasted the stored procedure below and the execution time of stored procedure takes more time.
CREATE PROCEDURE p_diamondsearch_stud_Earring_test
(
@LowPrice varchar(50)= '100',
@HeighPrice varchar(50)= '1000000',
@Cut1 varchar(20)='' ,
@Cut2 varchar(20)='' ,
@Shape varchar(50) = '0',
@Weight1 varchar(6) = '0',
@Weight2 varchar(6) = '0',
@ColorLow varchar(50) ,
@ColorHeigh varchar(50),
@ClarityLow varchar(50),
@ClarityHeigh varchar(50),
@OrderBy varchar(50)='catalognr',
@SortOrder varchar(10)='ASC'
)
AS
BEGIN
create table #Diamonds1
(
partno1 varchar(30),color1 varchar(10),weight1 decimal(18,6),cut1 varchar(10),clarity1 varchar(10),length1 decimal(18,6),depth1 decimal(18,6),width1 decimal(18,6),sell1 decimal(10,2),LWRatio1 decimal(20,2),polish1 varchar(10),symmtry1 varchar(10),lab varchar(10),table_perc decimal(10,2),fluorescence varchar(10),shape varchar(10)
)
create table #Diamonds2
(
partno1 varchar(30),color1 varchar(10),weight1 decimal(18,6),cut1 varchar(10),clarity1 varchar(10),length1 decimal(20,2),depth1 decimal(20,2),width1 decimal(20,2),sell1 decimal(10,2),LWRatio1 decimal(20,2),polish1 varchar(10),symmtry1 varchar(10),lab varchar(10),table_perc decimal(10,2),fluorescence varchar(10),shape varchar(10)
)
declare @StrSQL varchar(5000),
@tablevar int
SET @StrSQL = ''
SET @StrSQL = @StrSQL + ' SELECT D.partno as partno1,D.color as color1,D.weight as weight1 ,D.cut as cut1 ,D.clarity as clarity1 ,D.length as lenght1,D.depth as depth1,D.width as width1 ,D.sell as sell1 ,LWRatio1 = (D.length/D.width), D.polish as polish1 ,D.symmetry as symmtry1,D.lab as lab, D.table_perc as tableperc1,D.fluorescence as fluorescence1,D.shape
FROM product_diam D where (D.catalognr = ''1025'' or D.catalognr = ''1027'') AND D.New_Release=''N'' AND (D.qtyoh - D.qtycom) > 0 and D.cut > 0 and D.avail <> ''T'' and (D.below_standard = 0) '
--PRICE
if len(@LowPrice) > 0 and len(@HeighPrice) > 0
Begin
Set @StrSQL = @StrSQL + ' AND (D.sell ) BETWEEN ' + @LowPrice + ' AND ' + @HeighPrice
End
--Cut
if len(@Cut1) > 0 and len(@Cut2) > 0
Begin
Set @StrSQL = @StrSQL + ' AND D.cut BETWEEN '+ @Cut1 + 'AND ' + @Cut2
End
--Shape
if len(@Shape)>0
Begin
Set @StrSQL = @StrSQL + ' AND D.shape = ' + @Shape
End
--Weight
if len(@Weight1) > 0 and len(@Weight2) > 0
Begin
Set @StrSQL = @StrSQL + ' AND (D.weight) BETWEEN ' + @Weight1 + ' AND ' + @Weight2
End
--Color
if len(@ColorLow) > 0 and len(@ColorHeigh) > 0
Begin
Set @StrSQL = @StrSQL + ' AND D.color BETWEEN ''' + @ColorLow + ''' AND ''' + @ColorHeigh + ''''
End
--Clarity
if len(@ClarityLow) > 0 and len(@ClarityHeigh) > 0
Begin
Set @StrSQL = @StrSQL + ' AND D.clarity BETWEEN ' + @ClarityLow + ' AND ' + @ClarityHeigh
End
--print(@StrSQL)
declare @STR varchar(5000)
set @STR=''
insert into #Diamonds1 Exec(@StrSQL)
insert into #Diamonds2 Exec(@StrSQL)
create index diamond_ind1 on #Diamonds1(weight1,cut1,color1,clarity1,length1,depth1,width1,table_perc,shape,symmtry1,polish1,fluorescence,partno1)
create index diamond_ind2 on #Diamonds2(weight1,cut1,color1,clarity1,length1,depth1,width1,table_perc,shape,symmtry1,polish1,fluorescence,partno1)
set @STR = @STR + ' SELECT D.partno1,D.color1,D.weight1,D.cut1,D.clarity1,D.length1,D.depth1,D.width1,D.sell1,D.polish1,D.symmtry1,D.lab,D.table_perc as tableperc1,D.fluorescence as fluorescence1,D.shape as shape1,LWRatio1=(D.length1/D.width1)
,P.partno1 as partno2,P.color1 as color2 ,P.weight1 as weight2 ,P.cut1 as cut2 ,P.clarity1 as clarity2 ,P.length1 as length2,P.depth1 as depth2 ,P.width1 as width2 ,P.sell1 as sell2 , P.polish1 as polish2 ,P.symmtry1 as symmetry2,P.lab as lab2,P.shape as shape2,LWRatio2=(D.length1/D.width1)
,P.table_perc as tableperc2,P.fluorescence as fluorescence2,Total =(D.sell1 + P.sell1)
FROM #Diamonds1 D join #Diamonds2 P on D.partno1 <> P.partno1 and
(P.weight1 between (D.weight1 - .01) and (D.weight1 + .01))
and (((D.cut1= 1) and (P.cut1 = 1))
or ((D.cut1 between 1 and 2 ) and (P.cut1 between 1 and 2 ))
or ((D.cut1 between 2 and 3 ) and (P.cut1 between 2 and 3 ))
or ((D.cut1 between 3 and 4 ) and (P.cut1 between 3 and 4 ))
or ((D.cut1 = 4) and (P.cut1 = 4)))
and (((D.color1 between ''D'' and ''E'') and (P.color1 between ''D'' and ''E''))
or ((D.color1 between ''E'' and ''F'') and (P.color1 between ''E'' and ''F''))
or ((D.color1 between ''F'' and ''G'') and (P.color1 between ''F'' and ''G''))
or ((D.color1 between ''G'' and ''H'') and (P.color1 between ''G'' and ''H''))
or ((D.color1 between ''H'' and ''I'') and (P.color1 between ''H'' and ''I''))
or ((D.color1 between ''I'' and ''J'') and (P.color1 between ''I'' and ''J''))
or ((D.color1 between ''J'' and ''K'') and (P.color1 between ''J'' and ''K''))
or ((D.color1 between ''K'' and ''L'') and (P.color1 between ''K'' and ''L''))
or ((D.color1 between ''L'' and ''M'') and (P.color1 between ''L'' and ''M'')))
and (((D.clarity1 = 5) and (P.clarity1 = 5))
or ((D.clarity1 between 10 and 15) and (P.clarity1 between 10 and 15))
or ((D.clarity1 between 20 and 25) and (P.clarity1 between 20 and 25))
or ((D.clarity1 between 30 and 35) and (P.clarity1 between 30 and 35))
or ((D.clarity1 = 40) and (P.clarity1 = 40))) '
if @Shape=4
Begin
set @STR = @STR + 'and ((P.length1 between D.length1 - .10 and D.length1 + .10) or (P.length1 between D.length1 and D.length1 + .10))
and ((P.depth1 between D.depth1 - .10 and D.depth1 + .10) or (P.depth1 between D.depth1 and D.depth1 + .10))
and ((P.width1 between D.width1 - .10 and D.width1 + .10) or (P.width1 between D.width1 and D.width1 + .10))
and (D.length1/D.width1) between (P.length1/P.width1)-.01 and (P.length1/P.width1)+.01
and ((D.table_perc between P.table_perc - 2 and P.table_perc + 2 ) and (P.table_perc between D.table_perc - 2 and D.table_perc + 2 ))'
End
if @Shape<>4
Begin
set @STR = @STR + 'and ((P.length1 between D.length1 - .10 and D.length1 + .10) or (P.length1 between D.length1 and D.length1 + .10))
and ((P.depth1 between D.depth1 - .10 and D.depth1 + .10) or (P.depth1 between D.depth1 and D.depth1 + .10))
and ((P.width1 between D.width1 - .10 and D.width1 + .10) or (P.width1 between D.width1 and D.width1 + .10))
and (D.length1/D.width1) between (P.length1/P.width1)-.05 and (P.length1/P.width1)+.05
and ((D.table_perc between P.table_perc - 5 and P.table_perc + 5 ) and (P.table_perc between D.table_perc - 5 and D.table_perc + 5 ))'
End
set @STR = @STR + 'and D.cut1=P.cut1 and D.shape = P.shape and D.symmtry1= P.symmtry1 and D.polish1=P.polish1 and D.fluorescence=P.fluorescence AND ((D.partno1 + P.partno1) <> (P.partno1+D.partno1)) '
if len(@OrderBy)>0
Begin
if (@OrderBy = 'sell')
Begin
Set @STR = @STR + ' Order By (D.sell1) ' + @SortOrder
End
else
Begin
set @OrderBy = @OrderBy + '1'
Set @STR = @STR + ' Order By D.' + @OrderBy+ ',P.'+ @OrderBy + ' ' + @SortOrder
end
End
END
Exec(@str)
drop index #Diamonds1.diamond_ind1
drop index #Diamonds2.diamond_ind2
drop table #Diamonds1
drop table #Diamonds2
--Exec(@StrSQL)
--print @StrSQL
GO
in the above procedure i have created two dummy tables and inserted some values in the dummy tables based on some condition.This process takes only less time.
But after that i am selecting some records from the dummy tables based on some condition but here the delay time seem to be very high.How to solve this problem.
one of them told to do the following
After inserting to the dummy table create index for the fields which is used in the last search conditions
Check which condition is taking more time.
i have also created the index for dummy tables but the time seems to be very high.....
Please any one hepl me..........
It's urgent...
hi i have attached the excel files
Regards,
Prakash
July 2, 2009 at 4:01 am
What version of SQL Server are you using?
There are far better ways to write this query. Your JOIN condition is on a non-equal value, which is the first problem.
Why are you using dynamic SQL?
There are so many things wrong here. I don't know where to start.
July 2, 2009 at 4:25 am
Ummm... you need to provide a bit more detail. What, exactly, does the spreadsheet represent? You also haven't provided any sample data for the table product_diam, so we don't know it's structure or content, making it rather difficult to determine where any likely problem might be. Also, be sure to indicate how many actual records there are in that table, even if any sample data you provide is only a few records. In other words, help us help you...
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply