MS Access SQL Query optimization?
Select all the rows from an arbitrary table Remove ampersands from gen_id Save every row from that arbitrary table in a collection Then, for each item in that collection Select three columns from __MOTHER_ISSUE_TABLE_7 by issue_id Update values in that collection with values from those three columns Since you say you're just storing the strings to update a GUI, then you almost certainly need to change this: ResultSet rs = statement. ExecuteQuery("SELECT `" + genid + "`, `" + issueid + "`, `" + issueexpression + "`, `" + issuedetails + "` FROM " + tablename) to return exactly what your mdbrecognition object needs in order to do its job. You should see a breathtaking speedup if you do that.
Select all the rows from an arbitrary table. Remove ampersands from gen_id. Save every row from that arbitrary table in a collection.
Then, for each item in that collection, Select three columns from __MOTHER_ISSUE_TABLE_7 by issue_id. Update values in that collection with values from those three columns. Since you say you're just storing the strings to update a GUI, then you almost certainly need to change this: ResultSet rs = statement.
ExecuteQuery("SELECT `" + genid + "`, `" + issueid + "`, `" + issueexpression + "`, `" + issuedetails + "` FROM " + tablename); to return exactly what your mdbrecognition object needs in order to do its job. You should see a breathtaking speedup if you do that.
Thanks for your reply. You got it correct what I'm trying to do, thanks for making it clearer. I don't want to update any fields in the database, just fetch the values and add them to the collection.
With that said, I'm going to read a bit about SQL UPDATE statements, if you wish your more than welcome to give me some concrete example. – Emil Mar 3 at 11:23 @Emil: I'm just guessing about the UPDATE; you're the only one who knows what that mdbRecognition object is used for. – Catcall Mar 3 at 11:31 Oh right, sorry for being so unclear.
The mdbRecognition object is used ONLY to store a bunch of String values, to be displayed in a GUI. So what I need to do is fetch the strings, and use those strings as arguments on the mdbRecognition setMethods. – Emil Mar 3 at 11:39 You're executing a query inside a loop, generally not a recipe for good performance.
MOTHER_ISSUE_TABLE_7. Issue_id is indexed? – Tim Mar 3 at 12:44.
Better to rewrite the first query, joining the mother issue table there.
Build query strings dynamically, as demonstrated in this search form. Particularly where the user will enter only a few of the criteria options you give them, this radically simplifies the criteria. Access applies filters intelligently, i.e.
A Filter or WhereCondition is typically applied before it requests the records from the back end. Avoid multiple tables on the outer side of a JOIN, as JET can misinterpret these. To control the order of execution, save one query and use it as an input 'table' to another query (stacked queries.) This is important, as JET does not honor the bracketing in the FROM clause when it compiles an execution plan.
Subqueries are generally less efficient than other techniques (such as JOINs or stacked queries), but more efficient than using domain aggregate functions. For suggestions on crosstab queries, see Crosstab Techniques. Query optimization is a huge topic.
For further reading, see SQL Queries for Mere Mortals by Michael Hernandez and John Viescas.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.