Kristoffer Danielsson <[email protected]> wrote: > When I create my own "stored procedures" using > sqlite3_create_function, I get horrible performance (which I > expected) even though the column of interest is INDEXED. > > > Consider this sample (it's stupid, but it shows my problem): > > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); > > > > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside > this function, if I encounter a date greater than my birthday, then I > want to tell SQLite to stop searching, since the date is indexed.
I'm not sure what you mean by "a date greater than my birthday". Presumably, your birthday comes every year, so every date is either your birthday or falls between two birthdays (except dates before the date you were actually born on, but those can't be greater than any of your birthday dates). Besides, there's no guarantee that SQLite would pass dates to your function in any particular order, so seeing a date that is greater than your birthday (whatever that means) doesn't tell you anything about dates you will be called with next. So it's not clear how you can decide where to stop. You could improve your chances by adding an ORDER BY clause. If you have a particular cut-off date in mind, change your query to something like SELECT * FROM MyTable WHERE IndexedDate <= MY_BIRTHDAY(); If this still doesn't utilize the index (SQLite may allow for the possibility that the function is not pure), then this should do it: SELECT * FROM MyTable, (SELECT MY_BIRTHDAY() as bday) WHERE IndexedDate <= bday; Finally, if for some reason you really need to terminate the query early, you could either a) raise an error, or b) notify your application out of band (e.g. by setting a global variable, or using the context structure passed at function creation and available wih sqlite3_user_data). The application code then can simply stop calling sqlite3_step. Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
