I am optimizing my inventory check in/check out system to work over Wan. It creates a "usage instance" record every time an item is used. The table is pretty narrow but the record count will get high quickly. If I use a SQL search to find if any "usage instance" record with a matching id_inventory also value of 1 on a field called isActive from a table 100,000 records, would it it still be faster to ask the server to run the sql query and return a result to the client? in this case, it will only need to return an Id field if the record exists or no results.
i know a lot of factors play into this but I have optimized my start up procedures to open to a table with no fields or records and skip the regular client start up procedure. lets assume my server is properly deployed as well.
the reason I ask is executeSQL seems so fast right now on the client but i only have about 15000 records and its mostly running on a Lan. If there is not an easy answer to this, I will just have to run some tests but copies of my DB. Thanks for reading!