performance question

[james_keegan via AccessMonster.com]

Hey folks:

Gotta query that's taking FOREVER, and was hoping someone could give me some
tips on improving performance.

What I have is a system where I need to identify people who have DISenrolled
from a particular service plan. Unfortunatly, the way the 'legacy' system
was designed, the disenrollment record doesn't include the data on what plan
the client is disenrolling FROM. So at first I solved this problem by
looking at the previous record (occurence number) and getting the plan data
from that record. Well that's all good MOST of the time, but of course
*most* doesn't cut it! The problems occured when there was a 'delete' or a
'clear' which get's stored as a record with a delete flag equal to true. The
delete record applies to the line below it, and also doesn't have any plan
data. So what I needed was to select from the history table (previousline)
the most recent VALID record for each client (CIN), where valid is defined by
having either a 50 or a 70 in the plan data field (benefits package).

So this is what I came up with, after much cursing and hair-pulling:

sqlstring = "SELECT * into tblPrevlines FROM [MMC-FHP-previousline] AS A
WHERE a.[Occurrence # PC]=(SELECT min([Occurrence # PC]) FROM [mmc-fhp-
previousline] as B WHERE b.[CIN PC]=a.[CIN PC] and (b.[PCP Benefits Package
PC]='50' or b.[PCP Benefits Package PC]='70'));"

This works to populate tblPrevlines with ONE record for each CIN in [mmc-fhp-
previousline] which represents the most recent record (minimum occurence
number) for that CIN where there's a valid plan code. That's what I needed.
But the problem is that [mmc-fhp-previousline] has around 9000 records, and
the query takes a good 15 minutes, even when it's running off my 'c' drive.
The history table is only going to get bigger, and in production, they'll be
running this over the network, so it's only going to get worse.

Is there a better way to do what I want to do here? (and no, I can't roll
out SuperComputers to the users to run the app on!)

thanks much.
jk

 

[Allen Browne]

You may be able to speed this up by breaking it into 2 queries.

Assuming that:
- [Occurrence # PC] is unique to the [CIN PC],
- [Occurrence # PC] is indexed,
- [CIN PC] is indexed,
- [PCP Benefits Package PC] is indexed,
you should find that this executes quickly:

SELECT [CIN PC],
Min([Occurrence # PC]) AS MinOfOccurrenceNumPC
FROM [mmc-fhp-previousline]
WHERE [PCP Benefits Package PC] In ('50', '70')
GROUP BY [CIN PC];

Save that, and create another query based on this table and your original.
I'm guessing you need to join them on [CIN PC], and also on
MinOfOccurrenceNumPC to [Occurrence # PC].

After verifying this second query yields correct results and gives good
performance, you can turn it into an append query.

The suggestion may need some adapting, but hopefully suggests a useful
approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hey folks:

Gotta query that's taking FOREVER, and was hoping someone could give me
some
tips on improving performance.

What I have is a system where I need to identify people who have
DISenrolled
from a particular service plan. Unfortunatly, the way the 'legacy' system
was designed, the disenrollment record doesn't include the data on what
plan
the client is disenrolling FROM. So at first I solved this problem by
looking at the previous record (occurence number) and getting the plan
data
from that record. Well that's all good MOST of the time, but of course
*most* doesn't cut it! The problems occured when there was a 'delete' or
a
'clear' which get's stored as a record with a delete flag equal to true.
The
delete record applies to the line below it, and also doesn't have any plan
data. So what I needed was to select from the history table
(previousline)
the most recent VALID record for each client (CIN), where valid is defined
by
having either a 50 or a 70 in the plan data field (benefits package).

So this is what I came up with, after much cursing and hair-pulling:

sqlstring = "SELECT * into tblPrevlines FROM [MMC-FHP-previousline] AS A
WHERE a.[Occurrence # PC]=(SELECT min([Occurrence # PC]) FROM [mmc-fhp-
previousline] as B WHERE b.[CIN PC]=a.[CIN PC] and (b.[PCP Benefits
Package
PC]='50' or b.[PCP Benefits Package PC]='70'));"

This works to populate tblPrevlines with ONE record for each CIN in
[mmc-fhp-
previousline] which represents the most recent record (minimum occurence
number) for that CIN where there's a valid plan code. That's what I
needed.
But the problem is that [mmc-fhp-previousline] has around 9000 records,
and
the query takes a good 15 minutes, even when it's running off my 'c'
drive.
The history table is only going to get bigger, and in production, they'll
be
running this over the network, so it's only going to get worse.

Is there a better way to do what I want to do here? (and no, I can't roll
out SuperComputers to the users to run the app on!)

thanks much.
jk