Faster Querying??- Threads merged by Pat

[ThePhantom]

Complex Query Execution

Hi,

I have a table having more than 500,000 records. I have created indexes on all the major fields..... However, MS Access only allows creating indexes on the columns themselves, rather their parts too.....

What I mean is, I can create an index on PayDate, however, not on DATEPART('M', PayDate). My reason for mentioning this is directly related to my problem..... I have a report, and have several TextBoxes with 'DLookUps', which run a complex query based on this table, with the result that it really takes an enormous amount of time for the report to be previewed.....

Can anyone please suggest a way to execute my queries faster?? I have several complex queries (queries which use 'DATEPART', 'IIF', etc. extensively), and would like to minimize their execution time.....

Thanks,

Aashish

 

[quest4]

Just a few suggestions, DLookUp is aways slow and in a large dbase you would be better off going to recordsets and recordsetclones. Also I have been told that if youbring the query into code it will run faster. Another thought might be, sense your reports take a long time, once you get them run correctly, run them in a macro. That way you can run them remotely at night when few aor noone is on the system. Just a couple of random thoughts from the edge. hth.

 

[ThePhantom]

I acknowledge your point regarding the recordsets..... However, if I have 5 DLookUps on a single query, would not that query execute just once rather than 5 times??

Also, the reports are such that, a user selects month, year, department, etc. from a 'Report Menu' form, and then selects a report to preview..... Hence, the lot of IIFs, DATEPARTs, etc......

Can anyone suggest some way to faster execution of queries?? Also, what is the typical limit of a large MS Access table??

 

[quest4]

To the first question, yes. Remember code runs fastest and queries will be next. There is no reason why you can not put your query into code I have seen it done alot. hth.

 

[ThePhantom]

Faster Querying??

Hi,

I have a table having more than 500,000 records. I have created indexes on all the major fields..... However, MS Access only allows creating indexes on the columns themselves, rather their parts too.....

What I mean is, I can create an index on PayDate, however, not on DATEPART('M', PayDate). My reason for mentioning this is directly related to my problem..... I have a report, and have several TextBoxes with 'DLookUps', which run a complex query based on this table, with the result that it really takes an enormous amount of time for the report to be previewed.....

Can anyone please suggest a way to execute my queries faster?? I have several complex queries (queries which use 'DATEPART', 'IIF', etc. extensively), and would like to minimize their execution time.....

Thanks,

 

[The_Doc_Man]

The other part of your problem is that DatePart and DLookup are BOTH functions that require dynamic evaluation at run time. This occurs because Access does not have a built-in calendar table that says, "9-Dec-04 is Thursday." It recomputes this fact each time it hits that date. In fact, if the date is stored as text, it has to first compute the day number and then reverse the computation to get the weekday number. A double whammy!

So...

If you want to use various DATE criteria for reports, the only REALLY fast way to do this is to pre-build a table of nothing but the ELEMENTARY-LEVEL date fields you want to use in your sorts, lookups, etc. Then join your big table to the pre-built table and sort according to the pre-built table's fields.

In essence, this is a data design issue. The general principle that you violated is that you didn't store data that you were going to use frequently for sorting. It is one of the very rare cases where you DO store the value of a field that you can calculate dynamically. When the cost of the dynamic computation becomes prohibitive, don't do it dynamically.

The way I would build this of course depends on what I wanted to do with it. For my own scheduling table, it might be

tblMyCalendar
fldTheDate, date, prime key
fldTheDayNumber, integer or long, days since reference date
fldTheMonth, integer, 1-12
fldTheDayOfMonth, integer, 1-31
fldTheYear, integer, 1900+
fldTheJulian, integer, Julian day-of-year, 1-366
fldTheYearOfCent, integer two-digit-year-number, 0-99
fldTheWeekDayName, text, day name, Sat, Sun, Mon, etc.
fldTheWeekDay, integer, day number (Access default: Sunday = 0)
fldIsWeekEnd, yes/no
fldIsHoliday, yes/no
etc. etc.

Now, you might not need all of these fields. Probably will never use some of them. Leave out what isn't relevant to save space if you wish. But the point is that by making this table first and pre-calculating everything, you only have to compute it all once. Then just do a join on TheDate with your date field and you can test any criterion you want in the query.

This is EXPLICITLY an optimization where you violate a general rule about not storing computed fields in order to avoid repeating a very costly computation that kills performance for a large table. In other words, it is a case where strict normalization rules and practices need to be, not broken but at least carefully bent.

How many days do you want in that table? Your call, not mine. How do you populate it? There are ways. Like, define the date by hand for the first entry. Then do an append query that uses DateAdd to add 1 day to DMax( fldTheDate, tblMyCalendar) (with appropriate syntax, of course), then go back with an update query and fill in the rest of the blanks for any record that does not have the Julian date field >0. You do this computation once for each record and never have to do it again. Heck, populate it so that the date is anything up to the last day of 5-10 years from now. For 10 years it won't exceed 3653 records (depends on number of leap years; could be just 3652).

Good luck!

 

[The_Doc_Man]

Please do not post the same question in two threads. You waste time by doing that. Look at my answer in the general thread for this same question.

 

[The_Doc_Man]

As to your second question, which WAS new: you didn't tell us the version of Access so I'll answer that both ways.

Under Ac97 and earlier, use Help to look up keyword Limits

Under Ac2K and later, use Help to look up keyword Specifications.

From these Help topics, you can find your answers about size issues.

 

[Pauldohert]

Pat can you give an example of replacing Dlookup with left joins to the lookup table.

Thanks Paul

 

[ThePhantom]

You have been most helpful..... By the time I got ur reply, I had done precisely what u have outlined..... I created an intermediate table based on my complex query, and now, I have to refer to only around 1000 records, rather than 0.5 million of them!!

 

[The_Doc_Man]

A further optimization is possible (or maybe you have already done this).

When your query involves date-only criteria, build the date-selection query on the date table alone. THEN build a second query that joins the result set of the first query to your larger table. In other words, execute the query so that a shorter, narrower table is queried FIRST. Then as a second, later operation, do the JOIN only on the result-set dates. And you control this order of operation by nesting the queries.

According to hypergeometric theory, this should be the fastest possible way to do the JOIN you really wanted. You can build a query/table JOIN in the second query's design grid even if Access doesn't make the association itself.

 

[Pauldohert]

Thanks Pat