Sorting / Grouping and sorting in the stored procedure

[RJ]

Acc 2003 / Sql Server 2005

I have a fairly dynamic selection screen for user input for report criteria
including sort fields and Top n (records or percent or Ntile).

Up to this point I have allowed Access to sort the returned record set based
upon Sorting and Grouping criteria. To my knowledge you can’t turn this off
short of deleting the grouping. With the addition of Top / Rank criteria the
sp MUST sort / rank to return the proper records.

My questions is if the retuned record set is presorted, does this help, hurt
or have no effect upon the access report?

I am rambling a bit in my question but I hope someone has a comment.

 

[Gabriele Bertolucci]

I have a fairly dynamic selection screen for user input for report criteria

including sort fields and Top n (records or percent or Ntile).

Up to this point I have allowed Access to sort the returned record set based
upon Sorting and Grouping criteria. To my knowledge you can¢t turn this off
short of deleting the grouping. With the addition of Top / Rank criteria the
sp MUST sort / rank to return the proper records.

My questions is if the retuned record set is presorted, does this help, hurt
or have no effect upon the access report?

When you use "Sorting and Grouping" feature, Access ALWAYS group/sort AGAIN
the recordset that is returned, independently from what is the data source.
Seo, if you want to sort/group data in a report, I suggest you to avoid
sorting or grouping in a stored procedure and let Access do this job.

 

[Douglas J. Steele]

When you use "Sorting and Grouping" feature, Access ALWAYS group/sort
AGAIN
the recordset that is returned, independently from what is the data
source.
Seo, if you want to sort/group data in a report, I suggest you to avoid
sorting or grouping in a stored procedure and let Access do this job.

Not only that, but you cannot be sure that Access will respect the order of
the incoming data.

I agree with Garbriele that you should let Access do the sorting for you.

 

[RJ]

I agree with both of you. But in the case of selecting the Top 10 Percent
(as an example) I have no choice to do the sort in the sp and, due to
grouping requirements, Access as well. As you say Doug, I wasn't sure if
Access would respect the incoming presorted data or not. I would hope it
would at least make the Access sort easier. The sp’s are smart enough to
NOT perform the sort if Top/Rank is not part of the select / order by
statements.

Another possible tactic is to let all the records though and do the TOP
filtering in Access. Based on record volumes this could have a significant
impact. I guess that was the question I was really trying to ask. The
double sort vs network traffic reduction.

Thank you both for the input.