, , , ,

Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community

Queries "Bumping" into Each other? (OpenInsight 32-bit)

At 13 JAN 2012 06:06:33PM cborne wrote:

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

At 16 JAN 2012 04:00PM cborne wrote:

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.

At 17 JAN 2012 10:42AM cborne wrote:

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.

At 17 JAN 2012 10:49AM keith alvey wrote:

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

At 17 JAN 2012 11:44AM Dave Harmacek wrote:

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

At 17 JAN 2012 11:45AM cborne wrote:

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

At 17 JAN 2012 03:27PM bob carten wrote:

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...