By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,526 Members | 1,895 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,526 IT Pros & Developers. It's quick & easy.

Trying to speed-up report based on saved query (the SQL is posted here too)

P: n/a
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me and the end user. The
report is a student grades sheet showing the results of all his/her
activities and final grade for all students in any course--one page per
student. Should I break this into several smaller queries? use DAO inside
the report instead of a saved query? Should I make a smaller query then use
DLookup() to get the rest of the data I need? Seems slower to me. I wish I
didn't need to make so many calculations. It seems that if I made
DAO.Databases inside the report, I'd still have to query them, so it would
just involve even more extra steps. Is my query too convoluted to expect
better results? Any suggestions how I can improve the performance? Many
thanks to all who reply.

Here's my query:

SELECT students.studentID, students.lname, students.fname,
courses.courseCode, groups.groupID, activities.activityID,
groups.groupWeight, groups.groupOrder, activities.activityWeight,
activities.activityOrder, studentScores.score,
[activities].[activityWeight]/DSum("activityweight","activities","groupID="
& [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS activityPoints
FROM students INNER JOIN (((courses INNER JOIN groups ON courses.courseCode
= groups.courseCode) INNER JOIN studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON (students.studentID =
studentScores.studentID) AND (students.studentID =
studentsInCourses.studentID)
GROUP BY students.studentID, courses.courseCode, groups.groupID,
activities.activityID, groups.groupWeight, groups.groupOrder,
activities.activityWeight, activities.activityOrder, studentScores.score,
students.lName, students.fName;
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Right off the top... DSUM inside a query, executed twice for each record,
would raise the proverbial red flag. But, that would be happening, too, when
you ran the query without the report.

And, as we don't really know anything about the report itself, we really
can't offer any useful suggestions. But, of course, formatting and
displaying or printing the Report _will_ take some time -- putting
calculations in the report may or may not affect the response time.

I'm reluctant to even suggest it, as usually it takes _more_ time, but have
you tried displaying the course and activity data in subforms?

Larry Linson
Microsoft Access MVP

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:Lb1sd.140$vY3.129@trnddc01...
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me and the end user. The
report is a student grades sheet showing the results of all his/her
activities and final grade for all students in any course--one page per
student. Should I break this into several smaller queries? use DAO inside
the report instead of a saved query? Should I make a smaller query then use DLookup() to get the rest of the data I need? Seems slower to me. I wish I didn't need to make so many calculations. It seems that if I made
DAO.Databases inside the report, I'd still have to query them, so it would
just involve even more extra steps. Is my query too convoluted to expect
better results? Any suggestions how I can improve the performance? Many
thanks to all who reply.

Here's my query:

SELECT students.studentID, students.lname, students.fname,
courses.courseCode, groups.groupID, activities.activityID,
groups.groupWeight, groups.groupOrder, activities.activityWeight,
activities.activityOrder, studentScores.score,
[activities].[activityWeight]/DSum("activityweight","activities","groupID=" & [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS activityPoints FROM students INNER JOIN (((courses INNER JOIN groups ON courses.courseCode = groups.courseCode) INNER JOIN studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON (students.studentID =
studentScores.studentID) AND (students.studentID =
studentsInCourses.studentID)
GROUP BY students.studentID, courses.courseCode, groups.groupID,
activities.activityID, groups.groupWeight, groups.groupOrder,
activities.activityWeight, activities.activityOrder, studentScores.score,
students.lName, students.fName;

Nov 13 '05 #2

P: n/a
Yep. You should break into small queries.
I would recommend a couple of queries upon which the main query should be
based

1) GroupActivitiesWeights
SELECT activity.GroupID, Sum(activity.activityWeight) as
ActivityTotalForGroup
FROM activity
GROUP BY activity.GroupID;

2) CourseGroupWeights
SELECT groups.courseCode, Sum(groups.groupWeight) AS GroupTotalForCourse
FROM groups
GROUP BY groups.courseCode;

These 2 queries will do what the DSum's are currently being used to for.
It's much more efficient to use purpose-built queries to obtain these values
since they are only run once, whereas the way you've done the DSums
means they have to run on every row, which really slows it down.

In your main query
1) Include GroupActivitiesWeights and join it
on GroupActiviesWeights.GroupID = activities.groupID
2) Include CourseGroupWeights and join it
on CourseGroupWeights.courseCode = studentsincourses.courseCode

Then just substitute for you calculated fields:
ActivityPercentageWeight =
(activity.activityWeight /
[GroupActivitiesWeight].[ActivityTotalForGroup])
and GroupPercentageWeight =
(groups.groupWeight / [CourseGroupWeights].[GroupTotalForCourse])

HTH,
Ian.

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:Lb1sd.140$vY3.129@trnddc01...
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me and the end user. The
report is a student grades sheet showing the results of all his/her
activities and final grade for all students in any course--one page per
student. Should I break this into several smaller queries? use DAO inside
the report instead of a saved query? Should I make a smaller query then
use
DLookup() to get the rest of the data I need? Seems slower to me. I wish
I
didn't need to make so many calculations. It seems that if I made
DAO.Databases inside the report, I'd still have to query them, so it would
just involve even more extra steps. Is my query too convoluted to expect
better results? Any suggestions how I can improve the performance? Many
thanks to all who reply.

Here's my query:

SELECT students.studentID, students.lname, students.fname,
courses.courseCode, groups.groupID, activities.activityID,
groups.groupWeight, groups.groupOrder, activities.activityWeight,
activities.activityOrder, studentScores.score,
[activities].[activityWeight]/DSum("activityweight","activities","groupID="
& [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS
activityPoints
FROM students INNER JOIN (((courses INNER JOIN groups ON
courses.courseCode
= groups.courseCode) INNER JOIN studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON (students.studentID =
studentScores.studentID) AND (students.studentID =
studentsInCourses.studentID)
GROUP BY students.studentID, courses.courseCode, groups.groupID,
activities.activityID, groups.groupWeight, groups.groupOrder,
activities.activityWeight, activities.activityOrder, studentScores.score,
students.lName, students.fName;

Nov 13 '05 #3

P: n/a
You can use a make-table query to separate out the effect of
the query speed and the report formatting. Run the make-table
query to make a table, then link the report up to the table.

Any time taken to run the report against the new table is
due to the Report, and you should look there. Any time to
run the make-table query is in the query, and you should
look there.

(david)

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:Lb1sd.140$vY3.129@trnddc01...
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me and the end user. The
report is a student grades sheet showing the results of all his/her
activities and final grade for all students in any course--one page per
student. Should I break this into several smaller queries? use DAO inside
the report instead of a saved query? Should I make a smaller query then use DLookup() to get the rest of the data I need? Seems slower to me. I wish I didn't need to make so many calculations. It seems that if I made
DAO.Databases inside the report, I'd still have to query them, so it would
just involve even more extra steps. Is my query too convoluted to expect
better results? Any suggestions how I can improve the performance? Many
thanks to all who reply.

Here's my query:

SELECT students.studentID, students.lname, students.fname,
courses.courseCode, groups.groupID, activities.activityID,
groups.groupWeight, groups.groupOrder, activities.activityWeight,
activities.activityOrder, studentScores.score,
[activities].[activityWeight]/DSum("activityweight","activities","groupID=" & [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS activityPoints FROM students INNER JOIN (((courses INNER JOIN groups ON courses.courseCode = groups.courseCode) INNER JOIN studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON (students.studentID =
studentScores.studentID) AND (students.studentID =
studentsInCourses.studentID)
GROUP BY students.studentID, courses.courseCode, groups.groupID,
activities.activityID, groups.groupWeight, groups.groupOrder,
activities.activityWeight, activities.activityOrder, studentScores.score,
students.lName, students.fName;

Nov 13 '05 #4

P: n/a
Thank you all!!! These replies are full of very good help.

Rich
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:Lb1sd.140$vY3.129@trnddc01...
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me and the end user. The
report is a student grades sheet showing the results of all his/her
activities and final grade for all students in any course--one page per
student. Should I break this into several smaller queries? use DAO inside
the report instead of a saved query? Should I make a smaller query then use DLookup() to get the rest of the data I need? Seems slower to me. I wish I didn't need to make so many calculations. It seems that if I made
DAO.Databases inside the report, I'd still have to query them, so it would
just involve even more extra steps. Is my query too convoluted to expect
better results? Any suggestions how I can improve the performance? Many
thanks to all who reply.

Here's my query:

SELECT students.studentID, students.lname, students.fname,
courses.courseCode, groups.groupID, activities.activityID,
groups.groupWeight, groups.groupOrder, activities.activityWeight,
activities.activityOrder, studentScores.score,
[activities].[activityWeight]/DSum("activityweight","activities","groupID=" & [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS activityPoints FROM students INNER JOIN (((courses INNER JOIN groups ON courses.courseCode = groups.courseCode) INNER JOIN studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON (students.studentID =
studentScores.studentID) AND (students.studentID =
studentsInCourses.studentID)
GROUP BY students.studentID, courses.courseCode, groups.groupID,
activities.activityID, groups.groupWeight, groups.groupOrder,
activities.activityWeight, activities.activityOrder, studentScores.score,
students.lName, students.fName;

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.