[AccessD] Re: DatePart Question

Drew,

If you want to see a real speed difference, take the join out completely 
and place a.datefield = b.datefield in the WHERE clause.  From the tests I 
have done it is about twice as fast.

Robert

At 02:33 PM 2/5/2004 -0600, you wrote:
>Date: Thu, 5 Feb 2004 12:23:36 -0600
>From: DWUTKA at marlow.com
>Subject: RE: [AccessD] Re: DatePart Question
>To: accessd at databaseadvisors.com
>Message-ID:
>         <2F8793082E00D4119A1700B0D0216BF802227833 at main2.marlow.com>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>I did some testing.  I have a table with ~ 500k records.
>
>Using a date table to look for a specific month is definitely faster then
>looking for Month()=5
>
>Using a date table to look for a month and year is about the same as Between
>(maybe a hair slower)
>
>Using a date table to look for a month AND another criteria within 'data'
>table is the same as Month() (same for Month & year)
>
>Using date table for particular month and year AND another criteria look a
>hair slower then Between.
>
>So yes, searching strictly for dates is faster.  With an inner join (Left
>join changes everything, goes at about the same speed, but can't think of
>any instance where you would have a left join versus an inner join with a
>date dimension table).  However, when you have criteria in the 'data' table,
>the VBA method is just as fast as the date dimension process.
>
>I don't have millisecond times, nor did I test it up the wazoo, but I can
>understand why the date dimension table is faster now. (That was my problem,
>understanding the speed difference). With an inner join, only having
>criteria in the date table forces JET to look at it's indexes first, and
>then only pulling up records from the indexed 'data' table.  That is fast.
>A Left join forces it to look at the data table first, so there is no speed
>difference.  Inner join with WHERE clause for data table negates the speed
>difference with the date table, because it is only looking at the dates
>applied for the records found from the date table.
>
>So I concede on the speed issue relating to date dimensions! <grin> (Don't
>get used to me conceding though....LOL.)
>
>Drew