SQL Server Performance Forum – Threads Archive

slow proc

We are on SQL2000 sp3A on win2003 sp1 . We are running the 3rd party application on sql database. sometimes it runs really slow and kills the flow of rest of the application. here is the example . what is really bothering here is it happens quite few times unpredictable but kills the apps , FYI tables has been indexed properly , maintenance work (reindex ,update statistics done every day). is there anything can be tuned on the procs .. suggestion welcome execute pricingsearch_all 102, ””, ””, ””, 1, ”12/01/2005”, ”12/01/2005 11:59:59PM”, ””, ””, ””, ””, ””, ””, ””, ””, ””, ””, ””, ””, 0, ”|N|O|”, ””, ””’ here is the code for the proc CREATE PROCEDURE PricingSearch_all
@bundleid_val int, @desc_val char(22), @tkr_val char(10), @cusip_val char(12), @datetype tinyint,
@timerecvd_val1 char(20), @timerecvd_val2 char(21), @status_val1 char(1), @status_val2 char(1),
@status_val3 char(1), @status_val4 char(1), @status_val5 char(1), @status_val6 char(1),
@status_val7 char(1), @status_val8 char(1), @status_val9 char(1), @uploaded_val char(1),
@Sedol_val char(12), @ISIN_val char(12), @status_vals varchar(100)
AS
/******Create temp table with statuses to search for********/
set nocount on
declare
@pos int
create table #status_vals (
status_val char(1)
)
create index main_index on #status_vals (status_val)
select @pos = charindex(‘|’, @status_vals, 1)
while ((@pos > 0) and (datalength(substring(@status_vals, @pos + 1, 1)) > 0) and (substring(@status_vals, @pos + 1, 1) <> ‘|’))
begin
insert into #status_vals
select substring(@status_vals, @pos + 1, 1)
select @pos = charindex(‘|’, @status_vals, @pos + 1)
end
set nocount off
/***********************************************************/
if @datetype = 0
begin
select distinct p.status, rtrim(s.description) + ‘ ‘ + s.desc2 ‘description’, s.tkr, p.price, p.lastquote, p.tradedate, p.vol, p.bid, p.ask, p.timerecvd,
p.opening_price, p.historic_close, p.kassakurs, p.last2, p.priceid, s.cusip, s.sedol, s.bloomberg,
s.ric, s.isin, p.vendor, p.userid, p.timelock, s.timelock, s.cap_pointer, s.cap, p.modified,
p.bundleid, o.uploaded, b.name, p.fvm_factor
from prices p, securitymaster s, openpositions o, bundles b, #status_vals sv
where s.tkr = p.tkr and
s.tkr *= o.tkr and
p.bundleid = b.id and
p.bundleid <=
case
when (@bundleid_val = -1) then 1000
else @bundleid_val
end and
p.bundleid >=
case
when (@bundleid_val = -1) then 0
else @bundleid_val
end and
s.description like
case
when (@desc_val = ”) then ‘%’
else rtrim(@desc_val)
end and
s.tkr like
case
when (@tkr_val = ”) then ‘%’
else rtrim(@tkr_val)
end and
s.cusip like
case
when (@cusip_val = ”) then ‘%’
else rtrim(@cusip_val)
end and
p.old = ‘F’ and
o.uploaded like
case
when(@uploaded_val = ”) then ‘%’
else rtrim(@uploaded_val)
end and
s.sedol like
case
when(@Sedol_val = ”) then ‘%’
else rtrim(@Sedol_val)
end and
s.ISIN like
case
when(@ISIN_val = ”) then ‘%’
else rtrim(@ISIN_val)
end and
–p.status in (@status_val1, @status_val2, @status_val3, @status_val4, @status_val5, @status_val6, @status_val7,
— @status_val8, @status_val9) and
p.status = sv.status_val and
o.id not in (select * from closedfunds)
order by s.tkr
end
else if @datetype = 1
begin … the same as above with little modification… thanks in advance

What about execution plan?
May be tables like: prices,securitymasters,etc., need indexes.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

A few suggestions 1. Don’t use outer join between securitymaster and openpositions (s.tkr *= o.tkr) because condition o.id not in (select * from closedfunds) will filter out rows where there is no match s.tkr = o.tkr. 2. For each possible @dateType value create separate procedure and call it either directly from client code (The solution I prefer) or execute it from the main procedure. That way you will make query optimizer easier to manage execution plans for different branches. 3. Instead of replacing parameter values with special meaning (@bundleid_val = -1, @desc_val = ”) with their ‘special representation’ (1000, ‘%’) decode them on client and pass decoded values as sp parameter values. I mean pass 1000 as @bundleid_val, not -1 and ‘%’ as @desc_val not ”. That will allow query optimizer to have better information for building execution plan. 4. The solution you use for handling list of values (parsing input parameter into temporary table) is CPU intensive. Also there is no need for index on that temp table unless there are 1000s status values passed. 5. Not performance related, but using ANSI join syntax is more readable and less error prone.

]]>