Whats fastest Unique Values or Group by?

[Hallgeir]

I have a query with two output fields and a lot of records. But I want the
query to show only unique combinations of this two fields. I could use the
query property "Unique values" (hope thats the english property name) or I
could use "Group by". My question is : which one of this solutions is the
fastest and most efficient to use ?

regards

 

[Jeff Boyce]

Hallgeir

"... a lot of records" may mean something different to you than to me. A
well-normalized database, with (if appropriate) indexed fields will produce
a decent response time (?fast) for hundreds of thousands of rows. The
version of Access, the version of your OS, the "horsepower" of your PC, the
amount of RAM in your PC, whether or not this is a "split" database, with
data located on a network server, these are also all factors in "fast".

What happens when you try it the two different ways you mentioned?

 

[Guest]

Try this ---
SELECT Table3.Field1, Table3.Field2
FROM Table3
GROUP BY Table3.Field1, Table3.Field2;

 

[Hallgeir]

Jeff
Both of my solutions works fast. I can't measure any signifcant difference
in time. I was just wondering if there was any known "good practice" in such
case. Which one of my solutions would the best expert use.

Hallgeir

"... a lot of records" may mean something different to you than to me. A
well-normalized database, with (if appropriate) indexed fields will produce
a decent response time (?fast) for hundreds of thousands of rows. The
version of Access, the version of your OS, the "horsepower" of your PC, the
amount of RAM in your PC, whether or not this is a "split" database, with
data located on a network server, these are also all factors in "fast".

What happens when you try it the two different ways you mentioned?

 

[Jeff Boyce]

?! Define "best expert" ?!

If either approach works quickly, and you can't measure "any significant
difference", the choice seems moot.

One definition of "expert" might be which generates the most "elegant"
solution.

Another might be which generates an accurate solution faster.

And yet another might be which approach solves the problem and allows me to
move on to the next issue. (?<g> can you guess which one I tend to use?!)

For me, it's often easier to find a working solution, move on, and come back
later if performance (or other considerations) become factors... (i.e., new
requirements/new issue)

--
Good luck

Jeff Boyce
<Access MVP>

Jeff
Both of my solutions works fast. I can't measure any signifcant difference
in time. I was just wondering if there was any known "good practice" in such
case. Which one of my solutions would the best expert use.

Hallgeir

"... a lot of records" may mean something different to you than to me. A
well-normalized database, with (if appropriate) indexed fields will produce
a decent response time (?fast) for hundreds of thousands of rows. The
version of Access, the version of your OS, the "horsepower" of your PC, the
amount of RAM in your PC, whether or not this is a "split" database, with
data located on a network server, these are also all factors in "fast".

What happens when you try it the two different ways you mentioned?

--
Good luck

Jeff Boyce
<Access MVP>

or

 

[Lynn Trapp]

One definition of "expert" might be which generates the most "elegant"

solution.

Jeff,
I always heard that the definition of "expert" is "former drip." ;-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Jeff Boyce]

Lynn

See what I mean...?! You and I don't have the same definition! (my
definition involves a briefcase and travel...)

Jeff Boyce
<Access MVP>