AW: Select WHERE questions

[Rudolf Bargholz]

Hi Das,

How do you do a WHERE clause where you want to search for character strings that are blank.. you want to find the fields that are empty.. how do you phrase that?

SELECT * FROM TABLE where coalesce(COL,'')=''

How do you do from a certain string forward.. like you want George and all names that alphabetically follow George..

SELECT * FROM TABLE where coalesce(COL,'')>='George'

The last SQL has a lot of implicit limitations/conditions. It depends on how the database orders characters in the character set that you use. You would have to test to see how lower case characters are handled in this context.

You really should not have to log off and log on to the database to keep the performance at a stable level. As Doug, I think it was, mentioned, there must be some SQL you can execute in SQLLite that will perform some type of FLUSH that will have the same effect. Are you issuing a commit at regular intervals? Does this not help with your performance issue?

A quick search for performance tuning of SQLite returned the following info:

https://phiresky.github.io/blog/2020/sqlite-performance-tuning/

Perhaps running the "pragma optimize;" at regular intervals would prevent you from having to log off/on at regular intervals.

Regards

Rudolf Bargholz

-----Ursprüngliche Nachricht-----
Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Das Goravani
Gesendet: Montag, 24. August 2020 18:56
An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Betreff: Select WHERE questions


How do you do a WHERE clause where you want to search for character strings that are blank.. you want to find the fields that are empty.. how do you phrase that?

How do you do from a certain string forward.. like you want George and all names that alphabetically follow George.. 

Do you do.  >=.  ?

And if you are chugging along in a repeat loop using a certain Select List of rows that you are fetching one at a time.. and you want to re log on every 10,000 rows to refresh the speed of the process.. how do find where you were in the records and how do you continue.. because the select table will get blown away when you re log on right?

I have found that if you log off and log on again it makes the process stay speedy.. it clears caches or something.. so anyways.. isn’t your select table inside the logged on statement object or session object such that when you log off you loose your place in the records.. you loose your select table.. isn’t that correct?  So you need to Select again but this time midway through your records because you already did some.. and this happens every 10,000 records say, so you have to do it over and over and it has to start up in the right place and continue.. I have a hard time knowing how to write that secondary select statement that has to start up halfway through the records.. I can only do it with a primary key field, and save the one we were on, and use that number to start up again midway through.. that’s easy.. if it’s just the primary key only that’s easy.. but if it’s a moire complex select statement that you need to reproduce midway through the records that’s harder or impossible.. what do you all do about this?  

I realize it’s kind of a rare situation.. one more attuned to dealing with data one time, at first, getting it into the databases.. that’s what I’m doing.. I’m massaging big data lists to get them into my program in the right way with all the right data.. and I’m moving through these lists over and over doing things to them as I go and I face the above problems and the associated questions re how to make good queries that work


_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com