How best to optimize this statement.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How best to optimize this statement.

Author

Topic

never say never
Starting Member

4 Posts

Posted - 2009-10-19 : 15:38:14

I am not an SQL guru by any means, and this is my first post. I created this statement when designing a system about 2 years ago. At that time I knew it was poorly written but it worked fairly well. However, as the table sizes have grown this statement has become pretty sluggish to run and needs some help, I am certain it can be written much better, but I am not sure how.

Basically this statement lists all the accounts that have not made a payment in more than 90 days, if we have not already suspended their drivers license.

Page33 has over 95,000 rows (in about 2 years)
Page32 has around 17,500 rows
Page36 has around 16,500 rows
Page29 has around 18,600 rows
and page0 has around 26,500 rows

If there is any other information that is needed, please let me know.

Thanks in advance for any help.

select page36.Claim as p36Claim, 
            page33.TDate as p33TDate, 
            datediff(d,page33.TDate,getdate()) as PastDue, 
            page29.DLSusp,
            page32.CLocation as p32County,
            *
from page0, page32, page33, page36, page29
where page0.SERIAL = page32.P_SERIAL
  and page0.SERIAL = page33.P_SERIAL
  and page0.serial = page36.P_SERIAL
  and page32.Def = page29.linkserial
  and page32.Type like '%PN%'
  and page33.TType like 'Payment'
  and not exists (select Top 1 page33.P_SERIAL 
                                from page33 
                               where page0.SERIAL = page33.P_SERIAL
                               and page32.subn = page33.subn
                               and page33.TType like 'Payment'
                               and datediff(d,page33.TDate,getdate())<90
                  Order by serial desc
                 )
 and (page33.Serial = (select top 1 page33.SERIAL 
                                             from page33 
                                             where page33.P_SERIAL = page0.SERIAL
                                            and page32.SubN = page33.SubN
                                            and page33.TType like 'PAYMENT'
                                           Order by serial Desc
                     )
       or not exists (select top 1 * 
                                 from page33
                                 where page33.P_SERIAL = page0.SERIAL
                                 and page32.SubN = page33.SubN
                                and page33.TType like 'PAYMENT'
                      )
   )
 and (page29.DLSusp = 'N' or page29.DLSusp is Null)
 and page0.status not like 'closed'
order by p33tdate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-19 : 16:58:33

Change
and datediff(d,page33.TDate,getdate())<90

to
AND page33.TDate >= DATEADD(DAY, -90, GETDATE())

N 56°04'39.26"
E 12°55'05.63"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-20 : 03:34:30

Interesting table names

.

at a coding level this isn't going to help

and page32.Type like '%PN%' -- basically a table scan. can the leading % be dropped/altered in any way?

and page33.TType like 'Payment' -- and with no % this should be a "="

and "Order by serial desc" in your subqueries is an overhead you can do without. you are looking for the presence of 1 or more records. it's not important what order they are in.

I'd advise you maybe rewrite in the style of

select x,y,z from tabel1
inner join table2 on xa=b
left join tabel3 on c=d
where d is null (this bit is the equivalent of a not exists)

at a glance, you seem to refer to similair subqueries, which might be simplfied.

please also post what indices you have on each table. anything in a "join" or a "where" clause is important for efficiency.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-10-20 : 08:05:16

This section


and (page33.Serial = (select top 1 page33.SERIAL 
                                             from page33 
                                             where page33.P_SERIAL = page0.SERIAL
                                            and page32.SubN = page33.SubN
                                            and page33.TType like 'PAYMENT'
                                           Order by serial Desc
                     )
       or not exists (select top 1 * 
                                 from page33
                                 where page33.P_SERIAL = page0.SERIAL
                                 and page32.SubN = page33.SubN
                                and page33.TType like 'PAYMENT'
                      )
   )

Could probably be rewritten as a derived table and joined to with a LEFT JOIN. You are effectively wanting the MAX(SERIAL) grouped by SUBN?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

never say never
Starting Member

4 Posts

Posted - 2009-10-20 : 16:11:32

Peso - Thanks, will give that a try.

AndrewMurphy - Thanks for the pointers. Here is some more information.

The table names are generated by software that allows you to generate "Pages" and in a GUI you create layout, define field types, lengths . . ., it generates the SQL Tables on the fly, so I don't have any control over table names at all.

The only indexes on the tables are the "Serial" field each table has.

What I am trying to do with this query is generate a list of accounts that:

1. Have not made a Payment in the last 90 days.

1.1 If they haven't made a Payment in the last 90 days I want to grab the last payment date. If they haven't made a Payment at all, grab what ever Date there is.

2. We have not already suspended their Drivers Licenses

3. The Case is not Closed

The "PN" in their coding stands for "Promissory Note" and is either in the middle or at the end of their coding string.
Examples include:
IPN "Insured Promissory Note"
StdPN-1/3 Rate "Standard Promissory Note 1/3 rate"
UIMOLPN "Un-Insured Motorist Over Limit Promissory Note"

The list goes on and varies by client, but you get the idea.

In the not exists (first) sub-query I am checking to see if a payment has been made in the last 90 days, so you are correct I don't need to order by (or Top), that should save some overhead.

In the next Subquery I am looking for the last payment made so I need to sort to find the last payment OR if there aren't any payments, just pick a transaction for the account.

I am wondering if creating a View would help. If I were to create a view with the needed info from Page33, Page32, Page36, Page29 and PAge0, say LAST PAYMENT Only, would the time it takes to create the view and to test the payments be greater or less than the time it takes to run a query with so many sub queries?

Transact Charlie - I keep thinking there has to be a way to re-write this as well, but I have not been able to figure it out as of yet.

What I am doing is grabbing the last [Max(Serial)] "PAYMENT" transaction for P_Serial, SubN. If there is no "PAYMENT" then I will take the last transaction [Max(Serial)] for P_Serial, SubN, no matter what type.

---

I would like to thank everyone for taking the time to assist me with this.

The real problem is that this query generates a list that the end users can click on to goto that claim. But it refreshes every time they return to the list, which is every few seconds when they are going over Late Payments. So this query runs over and over and over. This is the way the software is designed, not my choice.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-21 : 06:47:29

can you change "page32.Type like '%PN%'" to "page32.Type in ('IPN','StdPN','UIMOLPN')" ... etc
bit less futureproof, but more efficient.

Also indices on any/all fields involved in WHERE/JOIN conditions is a near must. The lack of them is the real drawback to your performance problem.

Also you are mixing up "restricting your data pool" with "retrieving specific data items" for desired records. I'd back up a bit and get the restricted list of accounts working properly and then add on the extra bit to retrieve the "payment information" for those accounts. As worded the latter may restrict your records being returned.

Changing the layout/structure of the SQL to the bit advised, will make it clearer what each statement is contributing to the overall query. At the moment you are inner joining on eveerything - with optional payment information, you may require a left join in there.

I'd reverse it back a bit an dstart with a simple 2-table join, review the performance plan and then add in more code and review the effect on the performance plan again and tweak the code (or indices) to ensure you stay close to optimal performance always, and repeat until you have the full query working.

You need to be looking to get away from table & index scans - they are real killers.

select x,y,z from tabel1
inner join table2 on xa=b
left join tabel3 on c=d
where d is null (this bit is the equivalent of a not exists)

And please advise the name of this **** software so that we can all avoid using it in future

never say never
Starting Member

4 Posts

Posted - 2009-10-21 : 08:33:46

AndrewMurphy - Thanks again for the pointers.

Yes I can change my type search. I didn't want to chance forgetting to change it later, guess that is a bit of laziness on my part. I did have a thought last night that I might create a Stored Procedure scheduled to run every night. I could have it run the above query (optimized) and dump it into a table, then just return the table, instead of running the query every time the software refreshes. This would mean a payment that came in that day would not be reflected, but that probably is not a bad thing, since they are reviewing the records anyway.

I can add additional indexes (will be interesting to see if the software wipes them out with a screen change).

Actually the software is pretty decent for what it was designed for. It is designed as a Case Management System for Law Firms. They had the software when I started working for them, and it is very easy to create 'screens' of data for different types of cases, some case types have 18 screens to them. It also has a very versatile contact database. The software in conjunction with another software package allows the creation of legal documents very quickly without the need to edit or modify them.

That said there are problems with the software too. Since it is so easy anyone can do it, the screens (and subsequently the tables) are often designed by end users who don't understand even the basic way a DB works and they make bad decisions about the data layout, field types, . . . which of course results in difficulty creating efficient queries . . .

Next I came along and pushed the software well beyond it's design limits. In addition to Case Management, it is also doing basic accounting for our Subrogation department. I had never designed an accounting system before and had to design, test and move to production in just under 6 weeks, even though my initial estimate for design and testing was 4 - 6 months. That was extremely challenging to say the least. For the most part though the system works well and even survived its first audit with only a couple of minor recommendations, and no critical findings from the auditor. Even the recommendations were based on the limits of the software we are using and not my design, so I am quite proud of this project overall. The software is called "Lawbase", and truthfully, it is not bad software for what it is designed to do, I have taken it well past what it was designed to do, even their programmers told me many things could not be done within their software, but I found a way, this query and a couple of the auditors recommendations are the only issues that remain.

If I had it to do all over again, I would use a slightly different approach on the accounting stuff, but unless the current setup inhibits something I will likely leave it as it is and just keep my design changes for the next project.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-21 : 10:04:30

putting the above code into a stored procedure will also help benefit from improved security and also improved performance....use of cache. etc...qualify tables with "dbo." first.

A basic presumption on our part was that you were using SP's in the first place.

SP's is near mandatory for any good coding practice. read (other discussions) here for many reasons why.

never say never
Starting Member

4 Posts

Posted - 2009-10-21 : 14:12:50

Again, we are back to the software that the firm uses. I can't run a stored procedure from inside there software because it is wrapped inside their cruft.

But if I create a stored procedure and have it run once a day and dump the information into another table I think I can just do a select from SPTable inside that software and things will work much better. It just won't quite be realtime, it will be a once a day update.

I use Stored Procedures exclusively for the accounting functions that do not happen from within this software, and for reports. From within the software, I am limited by their constraints.

Thanks again for all your assistance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources