Stored Procedure Query Optimization

Question

Hi all,

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

Answer 1

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.

Answer 2

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.

Answer 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.

Answer 4

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.