DWUTKA at marlow.com
DWUTKA at marlow.com
Thu Feb 5 12:23:36 CST 2004
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 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Wednesday, February 04, 2004 1:23 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Re: DatePart Question You're way off, Drew. <G> The reason for those divisions is because data warehouses are used to query data, period. So the user wants to see all fact records within a particular month for the past 5 years. The point of a dimension table is that you don't need to do any date calculations in the query. All you need do is specify the month number (no function required), and the year number. The join filters out the appropriate records. You need do no date math at all to filter the records because any date math was done when the dimension table was created. Since SQL is much faster than code, why would you expect this Month([MyDateField]) = 5 And Year([MyDateField]) BetweenYear(Now())-5 And Year(Now()) to be faster than this [MonthNo]=5 And YearNo Between 5 And 10 Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Wednesday, February 04, 2004 10:45 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Re: DatePart Question I understand more 'complex' date 'info', such as holidays, fiscal period, etc. I still don't understand the reason for basic date information, such as day, month, year, day of week, etc. I have never actually run any tests, but my gut says that a query where I wanted all records in the month of May (ANY year), that if I put Month([MyDateField])=5 in the Where clause, that it would be faster then having a relationship to a date dimension table. Data entry, or data warehousing, the speed should still be a factor, correct? Or am I way off on my gut feeling (really too busy to build an appropriate test.) Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Tuesday, February 03, 2004 4:38 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Re: DatePart Question The essential difference is that the date dimension table is generally used in data warehouse applications, not in regular data entry databases. They can be useful in the latter for reporting purposes only, but you can get by nicely if you've never had to slice and dice very large tables based on a bunch of date criteria. Note that fact tables in a datawarehouse are usually not normalized in the same way as regular database tables either (they are commonly 1NF), so dimension tables give you flexibility that it's hard to get any other way. Data warehousing may be slightly off-topic, but only because Access developers don't have to deal with it very often. I have, so I can appreciate both sides of the discussion. Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Tuesday, February 03, 2004 2:05 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Re: DatePart Question Nothing personal, but I have to agree with Gustav's point of view. I can see (and I believe he does too), where a table would help certain situations. However, I know first hand, the extreme lack of understanding on how a date works. I'm not saying you don't understand that, however, to a computer, it is MUCH faster for many functions, to just let the processor do a logic operation on a number, then to have it pull other data up, and compare that. Holidays, etc, those require heavier logic, so a table could be faster (depending on the amount of data). I'm not knocking your approach. But in my experience, I have never needed to do anything like that. We better be careful that this doesn't turn into another bound/unbound issue. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Robert L. Stewart Sent: Tuesday, February 03, 2004 1:24 PM To: accessd at databaseadvisors.com Cc: hoopesg at hotmail.com Subject: [AccessD] Re: DatePart Question Ahh Grasshopper, And how will you learn if you do not create it? I mean, I could send it to you, or post the table to the files section, but that would not help you or anyone else understand the use of the functions that went in to create it. I teach an MS Access Developer's Workshop and have been doing that for about 8 years. One of the things I do in there is come up with an idea for a function, process, etc. and have them as a group build it. And about the holiday and fiscal year "modifications," since I leave that up to the user to define after the table is created, there are not really any modifications to make to the data for me. Now, having said all of that, I did send the MDB to John Colby, but that was primarily because I know he could have created it in his sleep and because he has shared he work rather freely with us and the Access development world in general. And, it is not reinventing the wheel. It is learning how to build a wheel so you can build the cart that goes with the wheel. ;-) Besides, you may build it quite differently from the way I do it or the way John would do it. Here is another example of "thinking outside of the box." How do you do entry for addresses? Well, I use a table of Zip codes. From that, I get the city and state. So they enter the zip code first and then they may have to select the correct city but the state will always be correct. I use a data-limited combobox for the city so that, for example, they can select one of the 4 "towns" that use 77418 (my zip code). I also default each of the limited comboboxes to the first value in their list, so if there is only one city (77002, Houston) in the list, they do not have to do anything else, thus saving many keystrokes. Actually, you would get both Jan 2004 and Jan 2003, etc. data because it is looking at only the month with what you were looking at doing. What I do is give the user a form for selecting a beginning month, beginning year, ending month, and ending year for reports. If they leave the month off, I only look at the year. If they only give the beginning month and year, I do a year-to-date. And so on. Since I dynamically build the where clause of the SQL statement, I just implement it as a business rule and explain to the users how their entries will power the criteria for their reports. The end result is that the Date Dimension table will give you a lot of control and flexibility in being able to gather criteria and validating it. Robert At 03:46 AM 2/3/2004 -0600, you wrote: >Date: Mon, 02 Feb 2004 12:12:19 -0600 >From: "Gina Hoopes" <hoopesg at hotmail.com> >Subject: [AccessD] Re: DatePart Question >To: accessd at databaseadvisors.com >Message-ID: <Sea1-F130hhDpB8ZdL300037f77 at hotmail.com> >Content-Type: text/plain; format=flowed > >Thanks to you and to Gustav for giving me more good ideas. I guess >your question kind of goes back to my original one. If it looks at the >whole date before giving me the "1" for the month, then I'll get Jan 05 >data rather than Jan 04 data. I guess the question is senseless since >both Joe and Gustav have given me good alternatives, but I may still >have to plug some fake data just to see what I get back. > >I have been following the dates table discussion and I've got another >(stupid) question. Except for fiscal years and holiday schedule >modifications, is this not the type of table that one of the people who >has already spent the time to create it could just put out for everyone >to use? I'm afraid I started following the discussion a bit late, so >maybe I missed something. It's not that I mind a day spent creating >something really useful, it just seems a bit like re-inventing the >wheel. > >Gina _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com