Re: What GROUP BY is not...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I really don't know about what Jet does in the step "group" of its query
plan. MS SQL Server is more detailed and uses the index (if it exists, and
if the number of records is high enough) of the field to be DISTINCT/GROUP
BY.

If you implant that strategy yourself, in this case, and if the index
exists, since the reading of the records (note the query is covered by the
index) is done already sorted, so you can dump the result list onto a stack
rather than on a sorted list. Indeed, to find if a record is already in the
"selected/grouped" list, you only have to compare it with the top value on
the stack. If the index does not exist, you have to relay on stats (if they
are available): does the result will have much less rows than the initial
data, or not. If the number of row is much smaller in the result, may be
preferable to use a sorted list (faster to sort, a little bit longer to
search) than to sort the whole initial set first, then use a stack (longer
to sort the whole initial set, faster to 'search' for existence). Again, if
the index exists, the query being covered by the index, you don't need to
touch the data at all, just the index, so, you have less IO (since index
would be, generally, more compact, than the 'table', it will resides on less
'pages', so less requirements to hit the hard disk). You see, that is what
SQL is: it knows different way of doing the described set, and decide which
strategy it will take. If you want to do the same, clearly, you should
weight the similar alternatives, and 'hard code' multiple solutions, not
just one (in general).



Vanderghast, Access MVP


"Warrio" <warrio@xxxxxxxxxxx> wrote in message
news:4666fcfb$0$23409$5402220f@xxxxxxxxxxxxxxxxxx
one question then :

Does the jet engine sort the selected data before grouping them? If I had
to write the function my self, I would create two queries one for a small
amount of data and the other for considerable amount of data.

Because sorting the data would be useful to avoid make a useless loop on
the list of data to display to check if it's displayed already or not,
because it's the same as the previous record of the sorted data...




.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... As far back as Access 97, Jet has made every attempt ... to "pass through" every Access query against a linked ODBC datasource. ... ends - DB2, Oracle, and SQL Server among others. ... > solution might be to use ADP. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... That query looks very much like the SELECT in the procedure you ... The ORDER BY CASE @sort is supposed to only tell SQL to return data ... far as I can call you have <bigquery> thrice in your procedure: ... I was SQL Server 2000 when I wrote this SPROC but we have since ...
    (comp.databases.ms-sqlserver)
  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... this query is likely to return multiple rows. ... Since @sort is NULL at this point, ... since your original query had some 7-8 tables whereof several repeated ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Need help with recursive SQL
    ... MS SQL Server 2005 has proprietary syntax to deal with that kind of problem. ... But you said you use Jet. ... a query implying the table a finite number of time. ... michael, william and alfred should be returned. ...
    (microsoft.public.access.queries)
  • Re: Calculated field in pass through query
    ... Server with logging to Jet without logging, you are not making a fair ... You could turn off the SQL Server logging and have a product ... perform query work at speeds more than 100 times what I can do with Jet. ...
    (microsoft.public.access.queries)