ADP report slow with large record count

[sherkozmo]

SQL2000 - AccessXP
I built an adp file with a stored procedure from SQL as follows:
SELECT * FROM
Z_mis_sjk_job_code_access
WHERE job_code=@JobCode
UNION ALL
SELECT * FROM
Z_mis_sjk_job_code_access_mkey
WHERE job_code=@JobCode
ORDER BY app_only, submenu_number, menu_routine_number,
menu_routine_text, initials

This sp runs fast with no problems. If I choose a job code that has a
small recordset return the report in access will run, a little slow,
but will run. If I choose a job code that returns a large recordset
(18,000), Access will show "not responding" in task manager and
ultimately the machine runs low on virtual memory and doesn't preview
the report. When I run the sp in the query window for the large job
code it runs in 4 seconds.

I tried to add the ORDER BY clause recently to see if that would help
the reports performance and it didn't help. I know I am suppose to
try and do all the processing on the SQL server but I must be missing
something.

The report in AccessXP Project (adp) has some grouping levels and
counting of records within those groups. I took out the page counter
at the bottom. It seems as though my access report is inefficient. I
even rebuilt the reports and it hasn't helped. Any other advice on
what I can do to get the report to run faster or at all in my project?

All help is greatly appreciated.
Sherry
sk*******@sfmc-gi.org

[Steve Jorgensen]

My guess is that, with a stored procedure, Access is having to sort and group
from the returned recordset. Try building the SQL Statement on the fly (in
the Form_Open handler), and assign the RecordSource to the SQL statement, and
see if that makes a difference.

It could also be, though, that sorting and grouping the union (which cannot
take advantage of any indexes) is what's so slow.

On 1 Dec 2003 08:24:22 -0800, sk*******@sfmc-gi.org (sherkozmo) wrote:

SQL2000 - AccessXP
I built an adp file with a stored procedure from SQL as follows:
SELECT * FROM
Z_mis_sjk_job_code_access
WHERE job_code=@JobCode
UNION ALL
SELECT * FROM
Z_mis_sjk_job_code_access_mkey
WHERE job_code=@JobCode
ORDER BY app_only, submenu_number, menu_routine_number,
menu_routine_text, initials

This sp runs fast with no problems. If I choose a job code that has a
small recordset return the report in access will run, a little slow,
but will run. If I choose a job code that returns a large recordset
(18,000), Access will show "not responding" in task manager and
ultimately the machine runs low on virtual memory and doesn't preview
the report. When I run the sp in the query window for the large job
code it runs in 4 seconds.

I tried to add the ORDER BY clause recently to see if that would help
the reports performance and it didn't help. I know I am suppose to
try and do all the processing on the SQL server but I must be missing
something.

The report in AccessXP Project (adp) has some grouping levels and
counting of records within those groups. I took out the page counter
at the bottom. It seems as though my access report is inefficient. I
even rebuilt the reports and it hasn't helped. Any other advice on
what I can do to get the report to run faster or at all in my project?

All help is greatly appreciated.
Sherry
sk*******@sfmc-gi.org

[Tom van Stiphout]

On 1 Dec 2003 08:24:22 -0800, sk*******@sfmc-gi.org (sherkozmo) wrote:

The 4-second situation is not comparable: it just means that the query
starts outputting rows after 4 seconds, not that it is completed in 4
seconds.
To debug this, I would start with Query Analyzer (in the SQL Server
tools). Check the time it takes to complete the entire query.
Then create a very simple report, just dumping the data to the report
without any formatting or grouping or page numbers. That time should
be in the same ballpark as the QA time.
Then add formatting, grouping etc. The addl time is the overhead of
the report engine. Not much you can do about that, unless a more
powerful query would return the data pre-grouped.

18,000 rows, at 40 rows per page, is 450 pages. Who is going to read
all of that?

-Tom.

SQL2000 - AccessXP
I built an adp file with a stored procedure from SQL as follows:
SELECT * FROM
Z_mis_sjk_job_code_access
WHERE job_code=@JobCode
UNION ALL
SELECT * FROM
Z_mis_sjk_job_code_access_mkey
WHERE job_code=@JobCode
ORDER BY app_only, submenu_number, menu_routine_number,
menu_routine_text, initials

This sp runs fast with no problems. If I choose a job code that has a
small recordset return the report in access will run, a little slow,
but will run. If I choose a job code that returns a large recordset
(18,000), Access will show "not responding" in task manager and
ultimately the machine runs low on virtual memory and doesn't preview
the report. When I run the sp in the query window for the large job
code it runs in 4 seconds.

I tried to add the ORDER BY clause recently to see if that would help
the reports performance and it didn't help. I know I am suppose to
try and do all the processing on the SQL server but I must be missing
something.

The report in AccessXP Project (adp) has some grouping levels and
counting of records within those groups. I took out the page counter
at the bottom. It seems as though my access report is inefficient. I
even rebuilt the reports and it hasn't helped. Any other advice on
what I can do to get the report to run faster or at all in my project?

All help is greatly appreciated.
Sherry
sk*******@sfmc-gi.org

[sherkozmo]

Steve - I removed the union and tried the same report on the first
select and the same scenerio - the report is still slow. Can you give
me a little more direction on the idea behind building the SQL
statement on the fly.
Thanks again.
Sherry

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<k8********************************@4ax.com>. ..

My guess is that, with a stored procedure, Access is having to sort and group
from the returned recordset. Try building the SQL Statement on the fly (in
the Form_Open handler), and assign the RecordSource to the SQL statement, and
see if that makes a difference.

It could also be, though, that sorting and grouping the union (which cannot
take advantage of any indexes) is what's so slow.

On 1 Dec 2003 08:24:22 -0800, sk*******@sfmc-gi.org (sherkozmo) wrote:

SQL2000 - AccessXP
I built an adp file with a stored procedure from SQL as follows:
SELECT * FROM
Z_mis_sjk_job_code_access
WHERE job_code=@JobCode
UNION ALL
SELECT * FROM
Z_mis_sjk_job_code_access_mkey
WHERE job_code=@JobCode
ORDER BY app_only, submenu_number, menu_routine_number,
menu_routine_text, initials

This sp runs fast with no problems. If I choose a job code that has a
small recordset return the report in access will run, a little slow,
but will run. If I choose a job code that returns a large recordset
(18,000), Access will show "not responding" in task manager and
ultimately the machine runs low on virtual memory and doesn't preview
the report. When I run the sp in the query window for the large job
code it runs in 4 seconds.

I tried to add the ORDER BY clause recently to see if that would help
the reports performance and it didn't help. I know I am suppose to
try and do all the processing on the SQL server but I must be missing
something.

The report in AccessXP Project (adp) has some grouping levels and
counting of records within those groups. I took out the page counter
at the bottom. It seems as though my access report is inefficient. I
even rebuilt the reports and it hasn't helped. Any other advice on
what I can do to get the report to run faster or at all in my project?

All help is greatly appreciated.
Sherry
sk*******@sfmc-gi.org

[Steve Jorgensen]

Well, sure, but read Tom's answer too because that also makes sense. It could
simply be that in one case, SQL Server is able to return the first rows
quickly, while in the other case, it must process all reows before returning
anything. Are you really intending to report 18,000 rows?

Anyway, for building the SQL on the fly, all you really have to do is assemble
the same SQL you posted in your message, but append the actual job code
expressions into the strings where the parameters are now. Presuming job code
is a number, and assuming you are using Access 2002, you can pass the job code
to the report through the OpenArgs argument (in 2000 and ealier, there is no
OpenArgs when opening reports), and insert it into the string like this...

Me.Recordsource = _
"SELECT * FROM Z_mis_sjk_job_code_access " & _
"WHERE job_code=" & Me.OpenArgs " & _
"UNION ALL SELECT * FROM Z_mis_sjk_job_code_access_mkey" & _
....

On 2 Dec 2003 06:20:19 -0800, sk*******@sfmc-gi.org (sherkozmo) wrote:

Steve - I removed the union and tried the same report on the first
select and the same scenerio - the report is still slow. Can you give
me a little more direction on the idea behind building the SQL
statement on the fly.
Thanks again.
Sherry

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<k8********************************@4ax.com>. ..

My guess is that, with a stored procedure, Access is having to sort and group
from the returned recordset. Try building the SQL Statement on the fly (in
the Form_Open handler), and assign the RecordSource to the SQL statement, and
see if that makes a difference.

It could also be, though, that sorting and grouping the union (which cannot
take advantage of any indexes) is what's so slow.

On 1 Dec 2003 08:24:22 -0800, sk*******@sfmc-gi.org (sherkozmo) wrote:

>SQL2000 - AccessXP
>I built an adp file with a stored procedure from SQL as follows:
>SELECT * FROM
>Z_mis_sjk_job_code_access
>WHERE job_code=@JobCode
>UNION ALL
>SELECT * FROM
>Z_mis_sjk_job_code_access_mkey
>WHERE job_code=@JobCode
>ORDER BY app_only, submenu_number, menu_routine_number,
>menu_routine_text, initials
>
>This sp runs fast with no problems. If I choose a job code that has a
>small recordset return the report in access will run, a little slow,
>but will run. If I choose a job code that returns a large recordset
>(18,000), Access will show "not responding" in task manager and
>ultimately the machine runs low on virtual memory and doesn't preview
>the report. When I run the sp in the query window for the large job
>code it runs in 4 seconds.
>
>I tried to add the ORDER BY clause recently to see if that would help
>the reports performance and it didn't help. I know I am suppose to
>try and do all the processing on the SQL server but I must be missing
>something.
>
>The report in AccessXP Project (adp) has some grouping levels and
>counting of records within those groups. I took out the page counter
>at the bottom. It seems as though my access report is inefficient. I
>even rebuilt the reports and it hasn't helped. Any other advice on
>what I can do to get the report to run faster or at all in my project?
>
>All help is greatly appreciated.
>Sherry
>sk*******@sfmc-gi.org