View Incident Report

Serious

Reported By: Markku Nevalainen
Reported On: 10/16/2004
For: Version 4.12 Build 1

# 1874 SQL SELECT Queries with Joins that Contain a Single Un-Optimized Condition on One Table are Too Slow

I finally switched from 3.24 to 3.30, and everything worked like a charm for a week. Now I got some unexpected symptoms:

- Some of the queries that had 2 second response times with 3.24 over network now take about 10 seconds with 3.30

- Slowness appears only when running database on file share mode. It does not appear in C/S mode and not when running the app locally.

- I have compiled exactly the same app with 3.24 and 3.30 versions. And I now have two separate EXEs, one taking 2 seconds and the other taking 10 seconds. Slowness does not appear with all queries, only with some of them.

Select distinct O.OrderNo, O.OrdDate,O.ShipDate,
O.EmpInitials,O.OrderMark1,O.OState,
O.CustNo, O.ShipCustNo,
(O.CuItemsTotal+O.CuTaxTotal) as AmountTotal,
O.ProjNo,
O.Terms,O.CuCostTotal,
C.CustNo
FROM CUSTOMER C,ORDERS O ,ITEMS I
where O.CUSTNO = C.CUSTNO
and O.Orderno = I.OrderNo
and I.Delivered < I.Qty AND
O.OrderNo >=6000001 and O.Orderno <8000000 AND
O.OrdDate >= '2001-01-01'
and O.OrdDate <= '2004-10-01'
Order By ORDERNO DESC

The condition causing the problem is the following condition:

I.Delivered < I.Qty

Comments

The same update in 3.30 that caused this issue was also present in 4.x.

Resolution

Fixed Problem on 10/17/2004 in version 4.13 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap