Array Coolness

[Kay C Lan]

On Thu, Mar 5, 2009 at 1:32 AM, Bob Sneidar <bobs at twft.com> wrote:

>
> It would be easier to hard code a query if I was coding for a specific
> application that only did one thing, but I am making generic functions to
> query the database based upon fields that I have dropped into a form using a
> Field Dropper utility I made.


Although there's no doubt that it would be easier to code for a specific
application, and certainly only you know the 'repeat loop and branching OUCH
factor' vs 'easy code', but it may be better to do it now rather than forced
to due to the need for speed later on.

In my case, I had to go back and change the names of fields/buttons to
EXACTLY match the column names in my db - so much easier and faster than
having to remove prefixes. Then if one of the 100+ buttons/fields were
changed, it's name was added to a list. Depending on mode, Add or Update, a
check is carried out to confirm all mandatory data is entered, or only that
data which has changed is updated - much much faster than a generic call for
all data and updating all columns.

The order the data is changed is not important to me, but the list is
created in that order, and I can see that it could be easily adapted to fit
your requirement. If your Dropper Utility after it sets the ListVisibility
to true, then adds that columnName to a list (probably kept in a custom
property), it is extremely easy to use that list as part of a:

put "SELECT " & myListOfVisibleColumns & " FROM " & myTable into myDbQuery

If you are making a 'generic' solution, and you don't care what the data is,
then consider this, a three column table, 1 = id, 2 = movieName, 3 =
binaryData (typically 4GB in size). There are 500 rows. The user only wants
to look at the movieName column. How long will it take to SELECT * (2TB+)
compared to SELECT movieName (<2KB)?

Again, you know your product so you may know you're never going to be
dealing with so much data but I can definitely envisage situations were
'all' columns will be 1000's of times larger than 'visible' data.