| The Glade 4.0 https://gladerebooted.net/ |
|
| SQL ... MS Access ... VBA https://gladerebooted.net/viewtopic.php?f=5&t=8677 |
Page 1 of 1 |
| Author: | Shelgeyr [ Wed Jun 06, 2012 3:32 pm ] |
| Post subject: | SQL ... MS Access ... VBA |
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? |
|
| Author: | Kairtane [ Wed Jun 06, 2012 8:19 pm ] |
| Post subject: | Re: SQL ... MS Access ... VBA |
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. |
|
| Author: | Hopwin [ Wed Jun 06, 2012 9:23 pm ] |
| Post subject: | |
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. |
|
| Author: | Vladimirr [ Thu Jun 07, 2012 12:17 pm ] |
| Post subject: | |
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. |
|
| Author: | Shelgeyr [ Thu Jun 07, 2012 3:45 pm ] |
| Post subject: | |
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. |
|
| Author: | Vladimirr [ Fri Jun 08, 2012 6:55 am ] |
| Post subject: | |
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. |
|
| Author: | Hopwin [ Fri Jun 08, 2012 11:05 am ] |
| Post subject: | Re: |
Vladimirr wrote: 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 |
|
| Author: | Shelgeyr [ Sat Jun 09, 2012 8:06 am ] |
| Post subject: | Re: Re: |
Hopwin wrote: Vladimirr wrote: 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. ^ ThatSometime 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. |
|
| Page 1 of 1 | All times are UTC - 6 hours [ DST ] |
| Powered by phpBB® Forum Software © phpBB Group https://www.phpbb.com/ |
|