1
\$\begingroup\$

This is a stored procedure that takes 5-30+ minutes to run depending on the parameter they select.

It also has a nasty side effect of clogging down our SQL Server.

SET NOCOUNT ON;

    DECLARE @clients TABLE (customer varchar(200))

    IF (NULLIF(@startdate, '') IS NULL)
        set @startdate = getdate()-7
    IF (NULLIF(@enddate, '') IS NULL)
        set @enddate = getdate()

    IF (ISNULL(@clientName,'') = 'ALL')
        INSERT INTO @clients SELECT customer FROM customer (NOLOCK)
    ELSE IF(ISNULL(@clientName,'') = 'Capital One')
        INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000380','0000611','0000541','0000715')
    ELSE IF(ISNULL(@clientName,'') = 'PRA')
        INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000411')
    ELSE IF(ISNULL(@clientName,'') = 'Midland')
        INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000584')
    ELSE IF(ISNULL(@clientName,'') = 'Trak')
        INSERT INTO @clients SELECT customerid FROM fact (NOLOCK) WHERE customgroupid=25
    ELSE IF(ISNULL(@clientName,'') = 'Hanna')
        INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000644','0000647','0000648','0000665','0000697','0000726','0000773','0000803','0000804','0000814')
    ELSE
        INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer = @clientName

    -- Insert statements for procedure here
    select distinct m.number as FileNumber
        ,isnull(dbi.bankname, '') as Bank
        ,c.name as ClientName
        ,CONVERT(VARCHAR(10),m.received,101) as ReceivedByFirm
        ,'' as ChargeOffDate
        ,datediff(d, m.received, getdate()) as AgeOfDebt
        ,0 as DebtAmount
        ,case when d.lastname = '' then d.name else d.lastname end as DebtorLastName -- show d.name if debtor last name is blank
        ,CONVERT(VARCHAR(10),m.dob,101) as DateOfBirth
        ,dbo.stripnondigits(d.zipcode) as DebtorZipcode
        ,d.state as DebtorState
        ,d.homephone
        ,d.workphone
        ,'' as MobilePhone -- no mobile phone 
        ,case when d.ssn != '' then 1 else 0 end as HasSSN
        ,case when pe.verified is not null then 1 else 0 end as Employed
        ,(select dbo.mmSddSyyyy(max(lr.DateProcessed)) from LetterRequest lr
            WHERE lr.DateProcessed>'' AND lr.DateRequested<GETDATE() AND Deleted=0 
            AND LetterCode in ('40029','50046','09997','09998','09999','10001','10002','10003','10004','10005','10006','10010','10011','10014','10015','50050','50041','50042','50001','50030','50040','50045','50060','50090','11001') AND lr.AccountID=m.number) as FirstLetterDate --Date letter sent to debtor by law firm
        ,(select min(created) from notes where number=m.number AND action like 'T%') as FirstCall
        ,(select count(created) from notes where number=m.number and created between @startdate and @enddate and action like 'T%') as TotalCalls
        ,(select case when count(created) > 0 then 1 else 0 end from notes where number=m.number and created between @startdate and @enddate and action = 'DT') as DebtorCalledFirm
        ,(select isnull(max(dbo.mmsddsyyyy(created)), '') from notes where number=m.number and created between @startdate and @enddate and action = 'DT') as DebtorCalledFirmDate
        ,case when m.status in ('PIF', 'SIF') then 1 when m.qlevel in (998,999) then 3 else 2 end as CollectionStatus
        ,m.desk as CollectorName
        ,isnull((select isnull(dbo.mmSddSyyyy(cc.DateFiled), '') from courtcases cc
            where cc.accountid = m.number and cc.DateFiled>'1900-01-01 00:00:00' and isnull(cc.casenumber,'')!=''
            and (m.current1+m.current2+m.current3+m.current4+m.current5+m.current6+m.current7+m.current8+m.current9+m.current10)>0), '') as SuitDate
        ,(select isnull(dbo.mmSddSyyyy(cc.ServiceDate), '') from courtcases cc where cc.accountid = m.number) as ServiceDate
        ,(select isnull(dbo.mmSddSyyyy(cc.JudgementDate), '') from courtcases cc where cc.accountid = m.number) as JudgmentDate
        ,isnull((select max(dbo.mmSddSyyyy(s.DateChanged))
            from statushistory s inner join courtcases cc on s.accountid=cc.accountid inner join debtors d on d.number = cc.accountid
            where s.accountid=m.number and isnull(cc.JudgementDate,'')!='' and s.DateChanged >'1900-01-01 00:00:00' and 
                s.newstatus in ('LNG','LXG','WGS','WGW','GIL','GNG') and isnull(cc.ServiceDate,'')!='' and isnull(d.jobname,'')!=''
                and s.id not in (select historyid from statuserror where accountnumber = m.number)), '') as GarnishmentDate
        ,getdate() as Today
        ,(select case when count(number) > 0 then 1 else 0 end from payhistory ph
            where ph.number = m.number and batchtype in ('PU', 'PC')) as PaymentMade
        ,isnull(datediff(d, m.received, getdate()) - (select datediff(d, min(ph.datepaid), getdate()) from payhistory ph where ph.number = m.number and batchtype in ('PU', 'PC')), '') as DaysElapsed
        ,case when m.status = 'PIF' then 'PIF' when m.status = 'SIF' then 'SIF' when m.status = 'PPA' then 'PPA' else '' end as PaymentMethod
        ,m.paid+m.paid1+m.paid2+m.paid3+m.paid4+m.paid5+m.paid6+m.paid7+m.paid8+m.paid9+m.paid10 as TotalPaid
        ,m.score as CollectionScore
        ,m.original as OriginalClaim
        ,isnull(datediff(d, m.received, m.chargeoffdate), '') as ChargeOff
        ,case when (isnull((select isnull(dbo.mmSddSyyyy(cc.DateFiled), '') from courtcases cc
            where cc.accountid = m.number and cc.DateFiled>'1900-01-01 00:00:00' and isnull(cc.casenumber,'')!=''
            and (m.current1+m.current2+m.current3+m.current4+m.current5+m.current6+m.current7+m.current8+m.current9+m.current10)>0), '')) = '' then 1 else 0 end as SuitFiled
        ,case when (select isnull(dbo.mmSddSyyyy(cc.JudgementDate), '') from courtcases cc where cc.accountid = m.number) = '' then 1 else 0 end as JudgmentObtained
        ,'' as JudgmentInOurFavor
        ,case when (isnull((select max(dbo.mmSddSyyyy(s.DateChanged))
            from statushistory s inner join courtcases cc on s.accountid=cc.accountid inner join debtors d on d.number = cc.accountid
            where s.accountid=m.number and isnull(cc.JudgementDate,'')!='' and s.DateChanged >'1900-01-01 00:00:00' and 
                s.newstatus in ('LNG','LXG','WGS','WGW','GIL','GNG') and isnull(cc.ServiceDate,'')!='' and isnull(d.jobname,'')!=''
                and s.id not in (select historyid from statuserror where accountnumber = m.number)), '')) = '' then 1 else 0 end as Garnishment
    from master m
    inner join customer c on c.customer = m.customer
    inner join debtors d on d.number = m.number
    inner join jm_people p on p.accountid = m.number
    left outer join jm_peopleemployment pe on pe.pid = p.pid
    left outer join debtorbankinfo dbi on dbi.acctid = m.number
    where m.customer IN (SELECT customer from @clients)
    order by m.number
\$\endgroup\$
4
\$\begingroup\$

performance review of SQL code without knowing the cardinality of the data, and the indexes used, is a real challenge, but, I would recommend that you try two things:

first, try make @customer table a top-level item in the Join:

from @customer csub
inner join master m on csub.customer = m.customer
inner join customer c on csub.customer = c.customer
.....

The other item that concerns me is that you use the DISTINCT keyword. This automatically implies a temp-table and a sort. This could be a 'killer' for this query because it has to compute and save all the select-value calculations. From what I can tell, there is no reason why you should get duplicate data anyway.

The 'order-by' may be possible to solve without a temp table (perhaps the query-plan can be tweaked to query off the master table in number order using an index (is the data clustered by number?), but I would consider removing it (the order-by) as well, unless you really need the data sorted by that.

\$\endgroup\$
  • \$\begingroup\$ There are no indexes to my knowledge. I will try the @customer and removing distinct and see how much of an improvement that gives me. \$\endgroup\$ – James Wilson Nov 21 '13 at 17:51
  • \$\begingroup\$ No indexes? Check the execution plan! \$\endgroup\$ – Peter Kiss Nov 21 '13 at 18:11
  • \$\begingroup\$ @PeterKiss I will try that, I should stipulate I am not the best at sql. I can do just about anything with a general knowledge, not having the advanced stuff is probably why this is running slow. :) \$\endgroup\$ – James Wilson Nov 21 '13 at 18:15
  • 1
    \$\begingroup\$ Adding that top level join really sped it up. A query that took 5 minutes before is now taking 40 seconds. Thank you! \$\endgroup\$ – James Wilson Nov 21 '13 at 18:33
  • 1
    \$\begingroup\$ @JamesWilson - good indexes will make it even better. \$\endgroup\$ – rolfl Nov 21 '13 at 18:39
2
\$\begingroup\$

Instead of if statements you could try using a Case statement instead

so this:

   IF (ISNULL(@clientName,'') = 'ALL')
       INSERT INTO @clients SELECT customer FROM customer (NOLOCK)
   ELSE IF(ISNULL(@clientName,'') = 'Capital One')
       INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000380','0000611','0000541','0000715')
   ELSE IF(ISNULL(@clientName,'') = 'PRA')
       INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000411')
   ELSE IF(ISNULL(@clientName,'') = 'Midland')
       INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000584')
   ELSE IF(ISNULL(@clientName,'') = 'Trak')
       INSERT INTO @clients SELECT customerid FROM fact (NOLOCK) WHERE customgroupid=25
   ELSE IF(ISNULL(@clientName,'') = 'Hanna')
       INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer in ('0000644','0000647','0000648','0000665','0000697','0000726','0000773','0000803','0000804','0000814')
   ELSE
       INSERT INTO @clients SELECT customer FROM customer (NOLOCK) WHERE customer = @clientName

should look like this:

INSERT INTO @clients 
CASE (ISNULL(@clientName, ''))
    WHEN 'All'
        THEN SELECT customer FROM customer (NOLOCK)
    WHEN 'Capital One' 
        THEN SELECT customer FROM customer (NOLOCK) WHERE customer IN ('0000380','0000611','0000541','0000715')
    WHEN 'PRA'
        THEN SELECT customer FROM customer (NOLOCK) WHERE customer = '0000411'
    WHEN 'Midland'
        THEN SELECT customer FROM customer (NOLOCK) WHERE customer = '0000584'
    WHEN 'Trak'
        THEN SELECT customerid FROM fact (NOLOCK) WHERE customgroupid = 25
    WHEN 'Hanna'
        THEN SELECT customer FROM customer (NOLOCK) WHERE customer IN ('0000644','0000647','0000648','0000665','0000697','0000726','0000773','0000803','0000804','0000814')
    ELSE SELECT customer FROM customer (NOLOCK) WHERE customer = @clientName

This makes it a little cleaner, I don't know how it will affect the performance of the Query, you would have to run an EXPLAIN on it.

You should always be thinking DRY or Don't Repeat Yourself

This pretty much takes that ISNULL(@clientName,'') and turns it into a variable almost, so now it is either '' or it is a value that was given. since it will not match anything if it is '', we should put in a WHEN statement at the beginning to match '' and dump out so it doesn't waste resources

Something like:

WHEN ''
    THEN SELECT customer FROM customer (NOLOCK) WHERE customer = @clientName

I am not sure exactly what you want to do if no clientName was given but it seems to me that it would fall into the ELSE statement so I figured that is what you wanted to do with it. Again, this should be the first CASE in our Case Statement.


If you don't have to use a special function, don't. When there is only one thing in the list you want to match, just use an = sign instead of using the IN operator.

\$\endgroup\$
  • 2
    \$\begingroup\$ Well, I wouldn't expect a CASE statement to really be any faster/slower, not for something like this. And it's only marginally cleaner. An IN() clause with only one element is functionally identical to = (and maybe optimized that way). Really, the best solution to deal with this part of the query is likely a translation table (although dealing with 'Trak' would be tricky) - this won't necessarily help with speed, but should help readability immensely. However, the author's real problem is no indices. And possibly some iffy design choices... \$\endgroup\$ – Clockwork-Muse Jan 4 '14 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.