I have a database table that has about 400 rows. I need to access the value of one of the columns in specific rows. The rows I need to access aren’t contiguous so I can’t just put it in a loop and use MoveNext. Right now I am calling a select statement and specifying the row in the database I want to access. I’m ending up doing 40 different select statements.
It seems like it would be more efficient to call one select statement and grab the whole table, the pic the specific row, but I don’t think you can do that. And it seems just as inefficient to loop through the entire table and checking the value of the row index and skipping it if it’s not one I want.
You can’t just say you want rows 4, 6, 12, 33. Rows in a resultset from any SQL engine have no intrinsic order and the engine can return them in any order it chooses.
You don’t need the single quotes around the indx.
I think you can do like:
SELECT Image FROM Icons WHERE Indx in (4, 6, 12, 33)
What I recommend doing is looking at how @Kem_Tekinay has implemented his “MoveToRecord” function in his excellent Data-On-Demand Listbox on his downloads page.
In a nutshell, he encapsulates the logic to move to a given record by tracking the “current index” and computing the most efficient way to get there by moving one record at a time from either the current index, or shifting to the top or bottom before starting to move.
You simply assign your recordset to his My_RecordSet class, and then you can use .MoveToRecord(index) and it becomes the current record. I’ve used his listbox class even with large record sets against a remote Postgres server and the performance is astounding. You don’t necessarily have to use his classes to populate a ListBox subclass. You can use his logic to get the equivalent of what you are asking for, providing the recordset supports movement (not all record sets support anything other than moving forward).
I think it comes down to whether or not you know ahead of the original select statement which rows you will be wanting. If so, definitely use the IN ( … ) clause on the WHERE statement. I was trying to address how to achieve the equivalent of the OP request to move to a specific row within a larger record set. And Kem’s logic is the best way that I know of to accomplish that.
Because it is a database of icons that I have set in advance that is distributed with the program and is non-editable. I know the index position of every button in that database.
I’m not sure what you mean, Wayne. The database is a collection of icons. When I created the database a long time ago, I have a field for the image data and a field called Indx. Those are fixed and never change. If I select a value of Indx, I ALWAYS will get a specific image. There is also a field assigning the icon to a specific category. That database is copied into the resources folder for every build of my app.
These icons are used to program a remote control panel. The user puts the remote control panel into “learning” mode and clicks the button they want to program. I have a window that pops up that then loads all these icons into a listbox and categorizes them by their categories. Normally, I load them all into the listbox and then when the user selects what they want, that icon is then stored in the main program database (if it’s not already there) for that remote panel.
In my case right now that I am doing, I am working on programming some remote control panels automatically. I’ve determined a certain set of buttons and their icons that I want to use for the remote control panel. Now instead of a user having to manually enter the icon, and remote control command for say 40 buttons, I do it all for them. Takes about 4 seconds instead of 10 minutes.
Here’s a screen shot of the database in an editor:
Yeah, that works but it’s not really any faster than selecting an image at a time. I branched my code and changed things to use the SQL IN function. I then profiled my code. It took 4607 milliseconds to run my entire programming routine.
I then went back to the original branch and profiled the code. That took 4593 milliseconds to run the programming routine.
Basically no difference between doing about 40 Select Statements vs. one Select Statement and moving through the resulting row set.
Oh, it’s not the Query that takes that long. It’s a whole bunch of other stuff going on. I think the database operations isn’t the slow part of it. That’s the lesson.
What I am thinking now that I have a bigger picture is that you could define the buttons for the remote in a table and use a view with a join to link the button to the indx column.
