Stored Procedure Query Optimization

Question

I have the following query and it's not working exactly as i want it to and it is really slow so i figured i'd ask for some help.

CREATE PROCEDURE [dbo].[SummaryReport]
@event varchar(7) = null,
@pet_num varchar(12) = null
AS
BEGIN
WITH pet_counts
     AS (SELECT   event,
                  pet_num,
                  pageid,
                  linenum,
                  tot_sig_page,
                  IDNUM,
                  val_date,
                  obj_type
-- Objections
                  ,case when sum(case when INV_SIG = '1' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_SIG = '1' then 1 else 0 end)) + ' Invalid Sig' else '' end as InvalidSignature
      ,case when sum(case when INV_ADR = '1' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_ADR = '1' then 1 else 0 end)) + ' Invalid Addr' else '' end as InvalidAddress
      ,case when sum(case when INV_DIST = '1' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_DIST = '1' then 1 else 0 end)) + ' Invalid Dist' else '' end as InvalidDistrict
      ,case when sum(case when inc_adr = '1' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when inc_adr = '1' then 1 else 0 end)) + ' Inc Add' else '' end as IncAdd
      ,case when sum(case when dup_sig = '1' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when dup_sig = '1' then 1 else 0 end)) + ' Dup Sig' else '' end as DupSig
      ,case when sum(case when Inv_Circulator = '1' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when Inv_Circulator = '1' then 1 else 0 end)) + ' No CRC Date' else '' end as NoCRCDate
      ,case when sum(case when isnull(REASON,'') <> '' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when isnull(REASON,'') <> '' then 1 else 0 end)) + ' Other' else '' end as OtherReason
      ,sum(case when INV_SIG = '1' then 1 else 0 end)
     + sum(case when INV_ADR = '1' then 1 else 0 end)
     + sum(case when INV_DIST = '1' then 1 else 0 end)
     + sum(case when inc_adr = '1' then 1 else 0 end)
     + sum(case when dup_sig = '1' then 1 else 0 end)
     + sum(case when Inv_Circulator = '1' then 1 else 0 end)
     + sum(case when isnull(REASON,'') <> '' then 1 else 0 end) as TotalObjections
-- Sustained
      ,case when sum(case when INV_SIG_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_SIG_ST = 'S' then 1 else 0 end)) + ' Sustained (Invalid Sig)' else '' end as SustainedInvalidSignature
      ,case when sum(case when INV_ADR_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_ADR_st = 'S' then 1 else 0 end)) + ' Sustained (Invalid Addr)' else '' end as SustainedInvalidAddress
      ,case when sum(case when INV_DIST_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_DIST_st = 'S' then 1 else 0 end)) + ' Sustained (Invalid Dist)' else '' end as SustainedInvalidDistrict
      ,case when sum(case when inc_adr_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when inc_adr_st = 'S' then 1 else 0 end)) + ' Sustained (Inc Add)' else '' end as SustainedIncAdd
      ,case when sum(case when dup_sig_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when dup_sig_st = 'S' then 1 else 0 end)) + ' Sustained (Dup Sig)' else '' end as SustainedDupSig
      ,case when sum(case when Inv_Circulator_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when Inv_Circulator_ST = 'S' then 1 else 0 end)) + ' Sustained (No CRC Date)' else '' end as SustainedNoCRCDate
      ,case when sum(case when oth_reas_ST = 'S' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when oth_reas_st = 'S' then 1 else 0 end)) + ' Sustained (Other)' else '' end as SustainedOtherReason
      ,sum(case when INV_SIG_ST = 'S' then 1 else 0 end)
     + sum(case when INV_ADR_ST = 'S' then 1 else 0 end)
     + sum(case when INV_DIST_ST = 'S' then 1 else 0 end)
     + sum(case when inc_adr_ST = 'S' then 1 else 0 end)
     + sum(case when dup_sig_ST = 'S' then 1 else 0 end)
     + sum(case when Inv_Circulator_ST = 'S' then 1 else 0 end)
     + sum(case when oth_reas_ST = 'S' then 1 else 0 end) as TotalSustained
-- Overruled
      ,case when sum(case when INV_SIG_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_SIG_ST = 'O' then 1 else 0 end)) + ' Overruled (Invalid Sig)' else '' end as OverruledInvalidSignature
      ,case when sum(case when INV_ADR_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_ADR_st = 'O' then 1 else 0 end)) + ' Overruled (Invalid Addr)' else '' end as OverruledInvalidAddress
      ,case when sum(case when INV_DIST_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when INV_DIST_st = 'O' then 1 else 0 end)) + ' Overruled (Invalid Dist)' else '' end as OverruledInvalidDistrict
      ,case when sum(case when inc_adr_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when inc_adr_st = 'O' then 1 else 0 end)) + ' Overruled (Inc Add)' else '' end as OverruledIncAdd
      ,case when sum(case when dup_sig_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when dup_sig_st = 'O' then 1 else 0 end)) + ' Overruled (Dup Sig)' else '' end as OverruledDupSig
      ,case when sum(case when Inv_Circulator_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when Inv_Circulator_ST = 'O' then 1 else 0 end)) + ' Overruled (No CRC Date)' else '' end as OverruledNoCRCDate
      ,case when sum(case when oth_reas_ST = 'O' then 1 else 0 end) > 0 then convert(varchar(5), sum(case when oth_reas_st = 'O' then 1 else 0 end)) + ' Overruled (Other)' else '' end as OverruledOtherReason
      ,sum(case when INV_SIG_ST = 'O' then 1 else 0 end)
     + sum(case when INV_ADR_ST = 'O' then 1 else 0 end)
     + sum(case when INV_DIST_ST = 'O' then 1 else 0 end)
     + sum(case when inc_adr_ST = 'O' then 1 else 0 end)
     + sum(case when dup_sig_ST = 'O' then 1 else 0 end)
     + sum(case when Inv_Circulator_ST = 'O' then 1 else 0 end)
     + sum(case when oth_reas_ST = 'O' then 1 else 0 end) as TotalOverruled
-- Cand Exceptions
      ,sum(case when INV_SIG_EX = 'C' then 1 else 0 end)
     + sum(case when INV_ADR_EX = 'C' then 1 else 0 end)
     + sum(case when INV_DIST_EX = 'C' then 1 else 0 end)
     + sum(case when inc_adr_EX = 'C' then 1 else 0 end)
     + sum(case when dup_sig_EX = 'C' then 1 else 0 end)
     + sum(case when Inv_Circulator_EX= 'C' then 1 else 0 end)
     + sum(case when oth_reas_EX = 'C' then 1 else 0 end) as TotalCandidateExceptions
-- Objector Exceptions
      ,sum(case when INV_SIG_EX = 'O' then 1 else 0 end)
     + sum(case when INV_ADR_EX = 'O' then 1 else 0 end)
     + sum(case when INV_DIST_EX = 'O' then 1 else 0 end)
     + sum(case when inc_adr_EX = 'O' then 1 else 0 end)
     + sum(case when dup_sig_EX = 'O' then 1 else 0 end)
     + sum(case when Inv_Circulator_EX = 'O' then 1 else 0 end)
     + sum(case when oth_reas_EX = 'O' then 1 else 0 end) as TotalObjectorExceptions
        FROM petchl
        WHERE event=@event
              AND pet_num=@pet_num
        GROUP BY event,
                  pet_num,
                  pageid,
                  linenum,
                  tot_sig_page,
                  IDNUM,
                  val_date,
                 obj_type),
user_info as
(
 SELECT vp.IDNUM,
 v.full_name,
 ltrim((isnull(rtrim(ltrim(v.addr_num)),''))
 + ' ' + isnull(rtrim(ltrim(v.addr_frac)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_dir)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_str)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_type)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_other)),'')) as address1,
 (isnull(v.cityname,'')+ ' ' + isnull(v.addr_zip,'')) as address2,
 v.regdate,
 v.birthdate, 
 v.sex,
 v.prec,
 s.signature
FROM         petchl AS vp INNER JOIN
                      v_JPPUsers AS v ON vp.IDNUM = v.IDNUM LEFT OUTER JOIN
                      Signatures AS s ON v.IDNUM = s.IDNUM
WHERE    vp.event=@event
         AND vp.pet_num=@pet_num

UNION ALL
 SELECT vp.IDNUM,
 v.full_name,
 ltrim((isnull(rtrim(ltrim(v.addr_num)),''))
 + ' ' + isnull(rtrim(ltrim(v.addr_frac)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_dir)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_str)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_type)),'')
 + ' ' + isnull(rtrim(ltrim(v.addr_other)),'')) as address1,
 (isnull(v.cityname,'')+ ' ' + isnull(v.addr_zip,'')) as address2,
 null as regdate,
 v.birthdate, 
 v.sex,
 v.prec,
 s.signature
FROM         petchl AS vp INNER JOIN
                      v_Cityusers AS v ON vp.IDNUM = v.IDNUM LEFT OUTER JOIN
                      v_CitySignatures AS s ON v.IDNUM = s.IDNUM 
WHERE    vp.event=@event
         AND vp.pet_num=@pet_num
)

SELECT    p.event,
   p.PET_NUM,      
   p.PAGEID,       
   p.LINENUM,
         convert(varchar(10), vp.pet_date, 101) as pet_date,
   p.InvalidSignature,    
   p.InvalidAddress,
   p.InvalidDistrict,
   p.IncAdd,
   p.DupSig,
   p.NoCRCDate,
   p.OtherReason, 
   p.TotalObjections,   
   p.SustainedInvalidSignature,    
   p.SustainedInvalidAddress,
   p.SustainedInvalidDistrict,
   p.SustainedIncAdd,
   p.SustainedDupSig,
   p.SustainedNoCRCDate,
   p.SustainedOtherReason, 
   p.TotalSustained, 
   p.OverruledInvalidSignature,    
   p.OverruledInvalidAddress,
   p.OverruledInvalidDistrict,
   p.OverruledIncAdd,
   p.OverruledDupSig,
   p.OverruledNoCRCDate,
   p.OverruledOtherReason, 
   p.TotalOverruled,
   p.TotalCandidateExceptions,
   p.TotalObjectorExceptions,
   p.TOT_SIG_PAGE,  
   v.full_name,
   v.address1,
   v.address2,
   p.IDNUM,    
   v.regdate,
   v.birthdate, 
   convert(varbinary(max), v.signature) as signature
FROM     pet_counts p
         LEFT OUTER JOIN user_info v
           ON p.IDNUM = v.IDNUM
         LEFT OUTER JOIN vrpet vp
  ON p.event = vp.event 
           AND p.PET_NUM = vp.PET_NUM  
WHERE  p.event = @event
     and p.pet_num = @pet_num

ORDER BY pageid,
         linenum
END

The query runs if i don't do a distinct on the final select, but i somehow need to a distinct because i'm having duplicate rows being returned. I'm guessing it's because of the image field. Is there any better / more efficient way to do a query of this nature and have the correct number of records returned?

Thanks

That is a LOT of code to wade through--someone is going to have to really want rep to go through the process of understanding all of that. What have you tried? Have you examined the execution plan of the query to start gaining clues on why it's slow? — Phil Sandler
– Phil Sandler, Commented Dec 21, 2009 at 17:47
Yeah i figured it would take alot of time for anyone to answer. Maybe i shouldn't have posted it all at once. I'm not really an sql guy so.How would i go about examining the execution plan? And what should i be looking for. — Newbie
– Newbie, Commented Dec 21, 2009 at 17:57
@Newbie take it one SELECT at a time. Get some data, check the data, if it looks good and next inner select. Check data, make sure GROUP BYs look good, add next SELECT. Keep going till it looks good. — JonH
– JonH, Commented Dec 21, 2009 at 18:02
@Newbie: I recommend to do SET STATISTICS IO ON then run your procedure. Look in the messages panel and see which tables have high logical read count and high scan count. The you can focus on the queries that involve those tables. — Remus Rusanu
– Remus Rusanu, Commented Dec 21, 2009 at 19:51

JonH · Accepted Answer · 2009-12-21 18:03:48Z

3

I've seen lots of questions that become problems that state 'I have to keep the distinct there otherwise I get duplicate rows.' This is just another problem within the existing problem. It's not only a very bad cut up bandage it's actually part of the problem.

You are probably not using GROUP BY correctly, if you are having to force a DISTINCT. We need more info with sample data as well. My advice is to stop looking at the entire code piece and figuring it all out. My advice is to look at the first select and figure out do you really need to group by all of those fields.

Take it one piece at a time

@Newbie I also added a comment:

@Newbie take it one SELECT at a time. Get some data, check the data, if it looks good and next inner select. Check data, make sure GROUP BYs look good, add next SELECT. Keep going till it looks good.

edited Dec 21, 2009 at 18:03

answered Dec 21, 2009 at 17:55

JonH

33.2k13 gold badges94 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Remus Rusanu · Accepted Answer · 2009-12-21 17:54:21Z

2

Most often SQL queries are not slow because how they are wtitten, but because of the table schema (size, cardinality, selectivity, available indexes). The optimizer can understand a lot from a query and optimize it no matter how brutally is written. To answer any performance question about a SQL query one must include the data schema is involved and the size of tables.

The engine already provides you with a great deal of help to answer your own question. It gives information about missing indexes, it keeps track of individual query performance and the documentation covers the basic recommendations.

I suggest you start with Tutorial: Database Engine Tuning Advisor.

answered Dec 21, 2009 at 17:54

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

1 Comment

John Farrell Over a year ago

In this case I'd say the query optimizer is not going to help at all for that first bit of case/sum madness.

Andomar · Accepted Answer · 2009-12-21 17:55:26Z

1

The first step in optimizing is to separate the query in small parts. Run each part as a step and determine which part is hurting most. The "show actual execution plan" option from the Query menu can be a big help in that.

Once you've found the part of the query that hurts, it's often easy to see how it might be improved. If you can't figure it out, even after trying, you can post on Stack Overflow asking for specific advice.

answered Dec 21, 2009 at 17:55

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Damir Sudarevic · Accepted Answer · 2009-12-21 18:42:09Z

0

Let us look at this:

case 
when sum(case when INV_ADR = '1' then 1 else 0 end) > 0
then convert(varchar(5), sum(case when INV_ADR = '1' then 1 else 0 end)) + ' Invalid Addr'
else '' end as InvalidAddress

Note that case(....) runs for each row. There are many like this.

It seems that INV_ADR is a char() trying to fake boolean. Now, suppose that INV_ADR is a tinyint with default to 0 and constraint (0,1)

You could first (in a sub-query or a cte) simply say:

sum(INV_ADR) AS [NumberOfInvalidAddr]

Then in another query -- once all aggregations are done -- referencing the first one:

case 
when [NumberOfInvalidAddr] > 0
then convert(varchar(5), [NumberOfInvalidAddr]) + ' Invalid Addr'
else '' end as InvalidAddress

Should be way faster.

edited Dec 21, 2009 at 18:42

answered Dec 21, 2009 at 18:35

Damir Sudarevic

22.2k3 gold badges50 silver badges72 bronze badges

Collectives™ on Stack Overflow

Stored Procedure Query Optimization

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related