Board index » delphi » InterBase 6.1 verses 4.1

InterBase 6.1 verses 4.1

Hello,

We are having some major problems with version 6.1. We converted our
database from version 4.1 to 6.1 about a week ago and are having performance
problems. Some SQL queries that ran before in 15 seconds are now taking 7
minutes and our "like" statements that were performing in reasonable times
are off the chart, taking 6 to 7 times longer to run. No changes were made
except the conversion to InterBase 6.1. The following query took 15 seconds
on InterBase 4.1 and takes over 7 minutes on the current InterBase 6.1.

************************************
 Select
C.CERT,
C.PROCESS_DATE,
C.CANCEL_DATE,
C.CANCEL_REASON,
C.CANCEL_MILEAGE,
C.CANCEL_FEE,
C.CANCEL_METHOD,
C.RESERVE,
C.INSURANCE,
C.ADMFEE,
C.AUL,
C.AGENT1_ID,
C.AGENT1_COMMISSION,
C.AGENT2_ID,
C.AGENT2_COMMISSION,
C.AGENT3_ID,
C.AGENT3_COMMISSION,
C.TOTAL,
C.NET_REFUND,
C.REFUND_PCNT,
C.CHECK_AMT,
C.CHECK_DATE,
C.REFUND,
(S.CUSTOMER_FIRST || ' ' || S.CUSTOMER_LAST) CustomerName,
S.DEALER_ID,
S.ADMINISTRATOR,
S.PLAN_TYPE,
D.DEALER_NAME,
D.ADDRESS,
(D.CITY || '   ' || D.STATE || ' ' || D.ZIP) CityStateZip
from AllCancel C, AllSales S, Dealers D
where C.Cert = S.Cert
and S.Dealer_Id = D.Dealer_Id
and C.Process_Date >= ' 2/ 1/01 00:00:00'  and C.Process_Date <= ' 2/28/01
23:59:59'
 and ((S.Administrator = 'AAO'))
order by Dealer_Name, Dealer_Id, Process_Date

*************************************************

Is there some options that I should have configured? I've already tried
changing the page size from 1026 to 4096.
Is it possible to convert back to version 4.1?

Their after me,
Randy

 

Re:InterBase 6.1 verses 4.1


Quote
Rand Monroe wrote:
> Hello,

> I'm having trouble with InterBase 6.1. For example, the following code runs
> on InterBase 4.1 in 17 seconds, and with InterBase 6.1 it takes 7 minutes.
> Is there something that I need to do.

  Query optimizer was seriously changed in 5.x and 6 from 4. Check plan that IB
build for your query in 4 and 6, if 4 is unavailable already, think about right
indexes by yourself. After that you can try to re-write your join accordingly
SQL92 (that don't help me in similar situation, 0.06/260sec ratio) or force IB
to use wanted indexes by PLAN JOIN clause. If I remember right, that  was
discussed in PLAN STATEMENTS thread on 18.02. You can look it in newsgroup
archieve if interested. Can't say about LIKE, I use STARTING and CONTAINING
only.

Re:InterBase 6.1 verses 4.1


Try using join statements to join your tables AllCancel, AllSales, and
Dealers instead of using the where clause to join the tables.  Also,  make
sure your fields Cert, Dealer_Id, and Process_Date are indexed.  It might
not hurt to have Dealer_Name indexed either for the sake of the order by.

Other Threads