Thread: Time Efficiency
12-10-12, 21:18 #1Registered User
- Join Date
- Dec 2012
Unanswered: Time Efficiency
With the database that I'm working with I'm finding that having to search through ~100,000 records (and growing with each day) requires some time that significantly slows down work; there is a Yes/No value that would allow me to filter relevant vs. irrelevant records for general purpose day-to-day queries, while still keeping the older records to preserve their IDs and data in case we need them for future reference, but I'm not sure if the application that I'm going to try for will make it any more efficient. Would putting in what's essentially an "if 'no', ignore and move on" step before the "does this record have the ID number 19383" step make the searching any more efficient?
Question is how do I code a button (checkbox) to 'ignore if Yes/No', depending on the checkbox being ticked or not, sending that info from the search form to the relevant query, and would that make the searching process any faster?
Last edited by RedFredHunter; 12-10-12 at 21:30.
12-11-12, 05:24 #2Grumpy old man (training)
Provided Answers: 11
- Join Date
- Sep 2006
- Surrey, UK
Try a Google search for SQL Optimisation. I haven't done a lot on it yet, but I think that putting the boolean field earlier in the WHERE clause will speed things up for you.10% of magic is knowing something that no-one else does. The rest is misdirection.
Beers earned: 2
12-11-12, 06:45 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
a standard checkbox has one of two values either yes or no (or more accurately as far as VBA is concerned true / false
the issue is how you retrieve the data
you could modify the underlying SQL or you could apply a filter
you could use a command button and toggle the text on the button
for now I'd suggest trying to apply a filter (on the grounds its minimum coding effort)
using the command button approach
..in that controsl on click event
if mycommandbutton.caption = "Show all Records" then 'we want to apply the filter to remove historic rows me.filter = 'mycolumn = ' & vbtrue me.filteron=true 'appl;y the filter 'you may need to refresh the form... if so me.refresh mycommandbutton.caption = "Show current records" else 'we want to remove the filter me.fitleron = false mycommandbutton.caption = "Show all records" endifI'd rather be riding on the Tiger 800 or the Norton