|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | SQL Performance during Data Window Inquiries | |
| dvlander 8/30/2010 12:05:03 PM | Hi Jeff. Out of curiosity, what causes SQL once in a while to take an inordinate amount of time to show results on a routine query? In some cases, you can do the exact same query afterwards and the results might be instantaneous. I'm just wondering if there is anything I can do to make the hangups less frequent. Thx, Dale
| |
| jeff 8/31/2010 12:22:56 PM | I'm making an educated guess it's related to the way the Jet 4.0 db driver behaves when a machine runs out of memory and stops to write everything to the swap file. At some point during the write to swap file routine the db driver appears to lose its connection to the database and just "hangs" until the app running the query is shut down. Some types of sql expressions and queries lend themselves to making the db driver load the entire database into memory - thus increasing the chances the machine will need to write to the swap file during a db query. Sql expression requiring the db driver to make logical and math evaluations are probably the worst offenders. Day(Date) = 3 used like the following is a perfect example: SELECT * FROM STARTERHISTORY WHERE DAY(DATE) = 3 AND YEAR = 2010 AND MONTH = 8 The above expression causes every record in the table to be loaded into memory and then evaluated as to whether or not it meets the = 3 criteria. Changing the expression to the following causes the database driver to use a lot less memory: SELECT * FROM STARTERHISTORY WHERE MONTH = 8 AND YEAR = 2010 AND DAY(DATE) = 3 Why? Because the database driver can first fetch records where the month is 8 and from those find the ones where the year is 2010 before ever starting to evaluate whether or not the day of the month = 3. Hope I explained that in a way that makes sense. -jp . ~Edited by: jeff on: 8/31/2010 at: 12:22:56 PM~
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |