July 2, 2009 at 6:28 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
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
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...
in the attached excel sheet there are four tables.
The third table is the result of the query..It displays 2958 rows.
The product_diam table contains nearly 60,000 records....
It already takes delay so that i have created the dummy tables and inserted the values in it and finally checked the condition..
Table-5 contains the product_diam sample data's
Below table-5 i have mentioned its datatypes....
Regards,
Prakash
Regards,
Prakash
July 2, 2009 at 7:28 am
Have you checked the query plan from SSMS to see if it is using the index? I am not sure that tables created in the current sproc are capable of using indexes on those tables. In a previous version this was true. Make sure the query plan uses the index..
The option is under Query, Display Estimated Query Plan..
CEWII
July 2, 2009 at 7:40 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply