The Glade 4.0

One of the things I've inherited as a consequence of my new position is a rickety old CRM (Customer Relations Management) tool that was cobbled together by designing custom forms and writing macros in Access 2000. Today, my boss said to me: "It's behaving strangely, see if you can fix it".

Specifically, one of the reports will produce results in a pretty timely fashion if asked to query the database for a 1-month period but gets badly hung if asked for results from a 1- or 2-day period.

Anyone have any clue why a report would perform well when the date range is long and poorly when it's short?

_________________
Go back to zero, take a pill, and get well ~ Lemmy Kilmister

It might be that when asked for say 3 - 5 June 2012, it's searching for the 3 - 5 time period prior to searching for the month and year.

_________________
"If you haven't got anything nice to say about anybody, come sit next to me." - Alice R. Longworth

"Good? Bad? I'm the guy with the gun." - Ash Williams

I am going to bank on Access crazy poor architecture and imho very bad sql support. Less than helpful I know, but if you can post the query source code we could poke at it.

_________________

Where's the data? In Access, or in an external database?

Like Mr. Hopwin suggested though, it'd be easier if you have a code snippet, even a genericized one.

_________________

Update - this should teach me never to trust the user, even when the user is my boss and the only other developer on the program in question.

Turns out the problem isn't that queries for long timeframes run just fine while short timeframes bog the system down. The problem occurs when the user selects the option that adds the following to the query:
"AND table1.date = table2.date"

When the user selects that option, the query is forced to cross-check values between 2 very large tables. One table has over 2 million entries while the other has over 6 million. We could easily remedy the problem by deleting everything prior to 2011, but the powers that be refuse to allow this.

The only solution appears to be loading records that occur within the requested date range into temp tables and then using the temp tables for the fine-tuning query, but it's sloppy as hell and neither I nor my boss like it.

_________________
Go back to zero, take a pill, and get well ~ Lemmy Kilmister

Depending on where the tables live, you could look at proper indexing.

You could compromise with the "powers that be" and archive the old records out to a new table, if they need to stay around for compliance reasons or something.

_________________

^ That

_________________

I suggested it, and was told that it's already been suggested and rejected. The problem, as my boss explained it to me, is that this program and the database were developed by a rival company that we acquired last year. The powers that be are afraid to do anything to the data because they don't understand exactly what it is and they don't want to run the risk of violating any compliance laws.

Sometime in the next year or so, this entire thing is going to be ported to a web-based solution, and the fees that will be paid for the porting and subsequent storage of all this data promise to be daunting, but even that fact hasn't caused the higher-ups to re-think their position. So, we (my boss and I) just have to live with it until it's not our problem anymore.

_________________
Go back to zero, take a pill, and get well ~ Lemmy Kilmister