MS Access Multiuser - Running a slow report/query on a network

Author

Running a slow report/query on a network

Don

2005-04-06, 8:06 pm

I have a report that is generated via two queries. One query extracts
information from two tables. Table Patients has 21,571 records and has each
of the fields indexed that are extracted into the query. The other table RX1
has 633,205 records and all five fields in the table that are extracted for
the query are indexed. The second query seems to take the longest amount of
time.
The tables are extracted into the following query:

SELECT RX1.CDC_NBR, PATIENTS.LNAME, RX1.STOP, PATIENTS.UNIT, RX1.SIG1, IIf("
" & [SIG1] & " " Like "* QHS *","HS",IIf(" " & [SIG1] & " " Like "* QAM
*","AM",IIf(" " & [SIG1] & " " Like "* QPM *","PM",IIf(" " & [SIG1] & " "
Like "* BID *","AM, PM",IIf(" " & [SIG1] & " " Like "* TID *","AM, PM,
HS"))))) AS SIGCODE, IIf(" " & [SIG1] & " " Like "* QAM *","AM") AS SIGAM,
IIf(" " & [SIG1] & " " Like "* QPM *","PM") AS SIGPM, IIf(" " & [SIG1] & " "
Like "* QHS *","HS") AS SIGHS, IIf(" " & [SIG1] & " " Like "* BID *","AM,
PM") AS SIGBID, IIf(" " & [SIG1] & " " Like "* TID *","AM, PM, HS") AS SIGTID
FROM PATIENTS LEFT JOIN RX1 ON PATIENTS.CDC_NBR = RX1.CDC_NBR
WHERE (((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1)
Not Like "* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* QAM *")) OR
(((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not Like
"* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* QPM *")) OR
(((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not Like
"* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* QHS *")) OR
(((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not Like
"* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* BID *")) OR
(((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not Like
"* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* TID *"));

This query is then the basis of another query that extracts and assigns a
value for different codes as follows: (this one very slow)

SELECT DISTINCT [PillLine1qry].CDC_NBR, [PillLine1qry].LNAME,
[PillLine1qry].UNIT, GetSCode([CDC_NBR],"AM") AS AM, GetSCode([CDC_NBR],"PM")
AS PM, GetSCode([CDC_NBR],"HS") AS HS, GetSCode([CDC_NBR],"AM, PM") AS BID,
GetSCode([CDC_NBR],"AM, PM, HS") AS TID, (([AM]+" ") & ([PM]+" ") & ([HS]+"
") & ([BID]+" ") & ([TID])) AS [CheckMEDNot used],
DailyMedicationSched
ule([AM],[PM],[HS],[
BID],[TID]) AS REPORT_MEDnotused
FROM PillLine1qry
WHERE ((([PillLine1qry].UNIT) Between [Start Building ie B01] And [Ending
Building Plus One]) And ((Right([SIGCODE],10
)) In ("AM","PM","HS","AM,
PM","AM, PM, HS")))
ORDER BY [PillLine1qry].LNAME;

I know that the two queries and the large record source over the network
make this a slow report to generate but anything that you could help me with
to speed it would be appreciated. Some of the things I noted.

1. I tried to move the queries on the server for all housing units and I ran
the queries to make a table so I could print this report out extracting
different housing as needed from the new table. This report runs immediately
and is the way I wish it would run. Running the queries to make the table on
the server uses almost 100% of the processing power but takes only 15
minutes. When I try to run the same query (on the server) activated over the
network, it ran over an hour and showed no sign of completing.

2. I have ran this as it should be (queries and reports on the fe and tables
only on the be) and it takes 15 to 60 minutes or more depending on how many
housing units I include of course the more units the longer it takes.

I have been working an a solution to run this report automatically in the
morning due to the length of time it takes to run. How best to solve this
situation/speed it up?

Thanks,

Dennis

david epsom dot com dot au

2005-04-06, 8:06 pm

Get rid of all those LIKE expressions!
And while you are at it, get rid of all those IIF expressions
also.

Perhaps you could create a mapping table to label the
records as HS, AM, PM etc, and to flag the records that
meet particular conditions?

Also, just rewriting the criteria so that

((RX1.STOP>Date$()) AND (PATIENTS.UNIT<>"Gone") AND
(RX1.SIG1 Not Like "* PRN*"))

Only appears once in the criteria, would probably
make it run faster than it does now.

(david)

"Don" <Don@discussions.microsoft.com> wrote in message
news:F65E25C4-2B8B-4BC9-BE83- 3B89C86A97B7@microso
ft.com...
>I have a report that is generated via two queries. One query extracts
> information from two tables. Table Patients has 21,571 records and has
> each
> of the fields indexed that are extracted into the query. The other table
> RX1
> has 633,205 records and all five fields in the table that are extracted
> for
> the query are indexed. The second query seems to take the longest amount
> of
> time.
> The tables are extracted into the following query:
>
> SELECT RX1.CDC_NBR, PATIENTS.LNAME, RX1.STOP, PATIENTS.UNIT, RX1.SIG1,
> IIf("
> " & [SIG1] & " " Like "* QHS *","HS",IIf(" " & [SIG1] & " " Like "* QAM
> *","AM",IIf(" " & [SIG1] & " " Like "* QPM *","PM",IIf(" " & [SIG1] & " "
> Like "* BID *","AM, PM",IIf(" " & [SIG1] & " " Like "* TID *","AM, PM,
> HS"))))) AS SIGCODE, IIf(" " & [SIG1] & " " Like "* QAM *","AM") AS SIGAM,
> IIf(" " & [SIG1] & " " Like "* QPM *","PM") AS SIGPM, IIf(" " & [SIG1] & "
> "
> Like "* QHS *","HS") AS SIGHS, IIf(" " & [SIG1] & " " Like "* BID *","AM,
> PM") AS SIGBID, IIf(" " & [SIG1] & " " Like "* TID *","AM, PM, HS") AS
> SIGTID
> FROM PATIENTS LEFT JOIN RX1 ON PATIENTS.CDC_NBR = RX1.CDC_NBR
> WHERE (((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1)
> Not Like "* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* QAM *")) OR
> (((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not
> Like
> "* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* QPM *")) OR
> (((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not
> Like
> "* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* QHS *")) OR
> (((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not
> Like
> "* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* BID *")) OR
> (((RX1.STOP)>Date$()) AND ((PATIENTS.UNIT)<>"Gone") AND ((RX1.SIG1) Not
> Like
> "* PRN*") AND ((" " & [RX1].[SIG1] & " ") Like "* TID *"));
>
>
> This query is then the basis of another query that extracts and assigns a
> value for different codes as follows: (this one very slow)
>
> SELECT DISTINCT [PillLine1qry].CDC_NBR, [PillLine1qry].LNAME,
> [PillLine1qry].UNIT, GetSCode([CDC_NBR],"AM") AS AM,
> GetSCode([CDC_NBR],"PM")
> AS PM, GetSCode([CDC_NBR],"HS") AS HS, GetSCode([CDC_NBR],"AM, PM") AS
> BID,
> GetSCode([CDC_NBR],"AM, PM, HS") AS TID, (([AM]+" ") & ([PM]+" ") &
> ([HS]+"
> ") & ([BID]+" ") & ([TID])) AS [CheckMEDNot used],
> DailyMedicationSched
ule([AM],[PM],[HS],[
BID],[TID]) AS REPORT_MEDnotused
> FROM PillLine1qry
> WHERE ((([PillLine1qry].UNIT) Between [Start Building ie B01] And [Ending
> Building Plus One]) And ((Right([SIGCODE],10
)) In ("AM","PM","HS","AM,
> PM","AM, PM, HS")))
> ORDER BY [PillLine1qry].LNAME;
>
>
> I know that the two queries and the large record source over the network
> make this a slow report to generate but anything that you could help me
> with
> to speed it would be appreciated. Some of the things I noted.
>
> 1. I tried to move the queries on the server for all housing units and I
> ran
> the queries to make a table so I could print this report out extracting
> different housing as needed from the new table. This report runs
> immediately
> and is the way I wish it would run. Running the queries to make the table
> on
> the server uses almost 100% of the processing power but takes only 15
> minutes. When I try to run the same query (on the server) activated over
> the
> network, it ran over an hour and showed no sign of completing.
>
> 2. I have ran this as it should be (queries and reports on the fe and
> tables
> only on the be) and it takes 15 to 60 minutes or more depending on how
> many
> housing units I include of course the more units the longer it takes.
>
> I have been working an a solution to run this report automatically in the
> morning due to the length of time it takes to run. How best to solve this
> situation/speed it up?
>
> Thanks,
>
> Dennis
>

Sponsored Links