RE: SQL-92 query issue

From: Paul Moser

Date: 9 Apr 2008

Terry,

I believe the subsequent queries running faster has to do with the data
already being in the database server's buffers (-B parameter) assuming no
intervening reads had overwritten it.

I also believe that your queries' choice of indexes is based on the
specifics of the pick parameters.  If you have a where field = value
appearing early in the query, I think it will try to use an index that has
that field, otherwise it might use a "default" index which may or may not be
more efficient.

My most likely thought on the run time error would be a record lock?  I
always use READ UNCOMMITTED for read only odbc queries.  But since you
mentioned "time out" this may be irrelevant.

Paul Moser
Phone 502 498 4672
Cell 502 291 4493
Fax 502 413 5431 
-----Original Message-----
From:  [mailto:] On Behalf Of Vaughn,
Terry
Sent: Wednesday, April 09, 2008 9:44 AM
To: 
Subject: SQL-92 query issue

I am seeing some strange behavior with using the SQL engine on one
particular table of QAD.  The table is the ar_mstr.
I have been using openlink ODBC v4.0 for some time now, but my latest query
(to the ar_mstr) is causing the ODBC to kick out an RPC timeout error.
I believe the problem is within the SQL-92 engine itself for the following
reasons :

Note 1:
If I go into the progress procedure editor (V9.1D), and I execute the
following SQL statement:
Select ar_nbr, ar_amt from ar_mstr where ar_type <> "P" and ar_type <> "A"
and ar_type <> "M" and ar_date >= 04/01/08 and ar_date <= 04/01/08 and
ar_bill = "CUST1234" . it takes an unusually long time before the result are
displayed.  Most interestingly, once it executes once, it executes faster
for subsequent executions (all day long).  Every morning, when I first
attempt the execution, I see the same pattern of behavior.  I assume there's
a caching mechanism going on here, but I don't know enough about the SQL-92
engine to positively ID the issue.  Can someone explain this ?

Note 2:  I have also notice that If I write the query as :
Select ar_nbr, ar_amt from ar_mstr where ar_type <> "P" and ar_type <> "A"
and ar_type <> "M" ......this statement
 will execute faster than this statement:
Select ar_nbr, ar_amt from ar_mstr where ar_type = "I".
Not sure of this behavior as well.

Right now...I'm more concerned about the first note.

Terry E. Vaughn
IT Manager
AVM Industries
Hwy 76 East
Marion, SC 29571
EMAIL:� ae0b643c-b939-8280-de11-7303617f051f
PHONE: 843-464-5430
CELL: 843-206-9365