DISTINCT or GROUP By - whats quicker ?

[LittleSmudge]

I'm taking data from an external candidate testing system and conducting analysis on the data

When new questions are added to the remote system I need to get the Ids of those new question.

The tblImportQuestion supplies me with all questions and their list of optional answer Ids ( it's a multi-choice type test ) that were presented to all candidates in a given period.

I've written :-

CurrentDb.Execute ("INSERT INTO tblQuestion ( QuestionId ) " _
                 & "SELECT DISTINCT tblImportQuestion.QuestionId " _
                 & "FROM tblImportQuestion " _
                 & "LEFT JOIN tblQuestion " _
                 & "ON tblImportQuestion.QuestionId = tblQuestion.QuestionId " _
                 & "WHERE (tblQuestion.QuestionId Is Null);")

I need DISTINCT because many candidates could get the same question so QuestionId can repeat in the tblImportQuestion table.

However I could just as easily used something like

CurrentDb.Execute ("INSERT INTO tblQuestion ( QuestionId ) " _
                 & "SELECT tblImportQuestion.QuestionId " _
                 & "FROM tblImportQuestion " _
                 & "LEFT JOIN tblQuestion " _
                 & "ON tblImportQuestion.QuestionId = tblQuestion.QuestionId " _
                 & "WHERE (tblQuestion.QuestionId Is Null) " _
                 & "GROUP BY tblImportQuestion.QuestionId;")

Is there any performance difference in the two approaches ?

I'll need to use the same approach to gather the OptionalAnswerIds into the tblOptions which will be a much bigger ( more records ) task so I want to sort out the most effective route.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[DotNetGnat]

Distinct is quicker...this is what i know...

The GROUP BY clause can be used with or without an aggregate function. But if you want optimum performance, don't use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster.

-DNG