I have a program that executes a query. It is an INET_ program for the web. Every time someone logs into the web app, it executes this program. I have noticed engines that at least once a day, there would be spawned engines that were just "Processing". When I would debug the engine, it would show that @Reccount was the entire table - 416,000 records. I was able to reproduce it simply by logging in at the same time on two sessions using the same information (queries were the same). One query worked fine, the other ended up selecting the entire table.

Is there something I can do to prevent this from occurring? Should I track when that query is running and delay other logins till the process is done?

Selecting on indexed fields - one is a Date field

OI 8.0.6

Network Driver 4.6.0.2

I now see that it does not have to be the same queries running, it can be different. It will still cause one query to select the entire table.

OK, with the help of Armando Moreno, we narrowed down the cause. My original query was:

Command = "SELECT LAB_ACTIVITY_LOG WITH LOCATION = ":Quote(client):" AND WITH DATE > ":Quote(OConv(SearchDate,'D4/')):" BY-DSND DATE"

ClearSelect

Call Rlist(Command,5)

We ended up making it:

ClearSelect

Command = "SELECT LAB_ACTIVITY_LOG WITH LOCATION = ":Quote(client)

Call Rlist(Command,5)

Command = "SELECT LAB_ACTIVITY_LOG WITH DATE > ":Quote(OConv(SearchDate,'D4/'))

Call Rlist(Command,5)

Command = "SELECT LAB_ACTIVITY_LOG BY-DSND DATE"

So while it is great that we fixed it, why does one even need to do this??? Now I need to go through my code and "fix" all my queries.

I have seen issues like this before, but can't remember all the reasons.

On an aside, have you tried with a column name not named DATE, eg LAL_DATE ??

Simon

I believe there was a problem when you mixed Btree fields with non-indexed fields in the same SELECT statement.

When I upgraded customers to Arev32 I had to split some of those statements to get them to work.

I believe this has been addressed since v 8, you might examine the README for oi 9.3.

Dave Harmacek

Harmacek Database Systems

Wouldn't this be considered a bug, that would be fixed for all versions?

Hi Clay

There have been lots of little fixes over the past few releases to make OI well behaved when many oengines run on a single machine. I vaguely recall the Issue Dave mentions. I also recall making a change to the get.sort.file routine to reduce the likelihood of name collisions. Adding the process id to @station to make it unique is another change. IDX_SETS2, the dot net based IDX_SETs used by Btree.Extract is another item. You might want to test an Eval copy of 9.3, see if your problem has been fixed.

View this thread on the Works forum...