Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: Using a Text Box as a Criteria is too slow...

    I am currently storing a date in a text box on forms so that multiple queries can reference this date so I don't have to enter them over and over by hand.

    This saves time in entry but the queries take longer than when I enter the values directly or even using a prompt (manually though).

    Is there a quicker way to feed criteria to multiple queries from one location? Would the extra effort required to do this in VB instead of the Access Macros speed things up?

    Any suggestions on how to speed this up would be greatly appreciated as it can take as long as 30 minutes for some of my databases to process.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I just tested a table that I have full of invoices. 46,000 records, and I selected 2277 records (from 2/1/2001 to 19/31/2001) in less than 1/2 a second. The two dates were on a form. So, how many records are you dealing with that you can notice that the query is slower or faster?

  3. #3
    Join Date
    Jan 2006
    Posts
    47
    Quote Originally Posted by GolferGuy
    I just tested a table that I have full of invoices. 46,000 records, and I selected 2277 records (from 2/1/2001 to 19/31/2001) in less than 1/2 a second. The two dates were on a form. So, how many records are you dealing with that you can notice that the query is slower or faster?
    Between 300,000 and 1,000,000 depending on the database. I realize that this is also part of the problem but it is unavoidable at this time.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    pulling a couple of dates from a form should take a few milliseconds. i am amazed that you can see a difference!

    the data is on the local client PC in a JET table, or ...?
    these 'dates' are Access-datetimes, or ...?
    are these date-fields indexed?
    +/- how many different dates in your 1,000,000 records ? 1,000, 100,000 ?
    you say 'multiple queries' - how are you arranging for the multiple queries run?
    some example SQL would be interesting.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I've got a number of tables with upwards of a million records, and I often use form controls for queries. I've never had a performance problem related to that. Are you saying that if you run the same query with a form control in the criteria, you see a big performance difference vs [enter criteria]?
    Paul

  6. #6
    Join Date
    Jan 2006
    Posts
    47

    Thank you... maybee I was mistaken but still very slow...

    After some more thourough testing I am thinking that It was other changes that made me think this was the problem.

    It seems that indexing is having the most effect but the problem is that I also have to import this data also. Thus any speed I save on the queries seems to slow the import and any changed on the import slows the queries.

    If anyone has any other advice it would be appreciated and thank you for the responses.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    There is a trade-off. Indexes speed up the retrieval of data, but slow down the manipulation of it (append, update & delete). I recall a recent thread somewhere and the poster had decided on a process that deleted indexes prior to heavy data manipulation, then created them again after.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •