Subject | Slow query question |
---|---|
Author | Nico Callewaert |
Post date | 2001-04-03T10:57:38Z |
Hi,
I have something strange. I have a query that executes in less then a
second when I test it interactively in IBConsole, when I prepare the query
in the IB_Query component it's still fast, but when I prepare the query when
my program it takes 10 seconds !! The connection to the database was
already open. Then to set Active := True, still 4 seconds. Here is the
SQl code :
SELECT A.DealerNo,
A.Name,
A.Contact,
A.Alfa,
A.Title,
A.Address1,
A.Address2,
A.Zip,
A.City,
A.Phone,
A.Fax,
A.E_Mail,
A.Website,
A.Comment,
A.Vat,
A.Country_EG,
A.Reduction,
A.BankAccount,
A.Foreign_BankCode,
A.Foreign_BankName,
A.Foreign_BankCity,
A.Foreign_BankSwift,
A.General_Acc,
A.Coin,
B.Name,
C.Province,
D.Descr,
E.Descr_1,
F.Descr_1,
G.Language_Descr
FROM Dealer A
LEFT OUTER JOIN Country B ON B.Code = A.Country
LEFT OUTER JOIN State_Prov C ON C.Code = A.State_Prov
LEFT OUTER JOIN Coin D ON D.Code = A.Coin
LEFT OUTER JOIN Deliv_Cond E ON E.Code = A.Deliv_Cond
LEFT OUTER JOIN Payment_Cond F ON F.Code = A.Payment_Cond
LEFT OUTER JOIN Language G ON G.Language = A.Language
WHERE A.DealerNo = :qDealerNo
The plan seems ok to me :
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (RDB$PRIMARY52),B INDEX
(RDB$PRIMARY28)),C INDEX (RDB$PRIMARY39)),D INDEX (RDB$PRIMARY10)),E INDEX
(RDB$PRIMARY35)),F INDEX (PAYMENT_COND_CODE)),G INDEX (RDB$PRIMARY4))
Just strange that interactive everything is OK, but inside the program it's
acting weird. Changing from LEFT OUTER JOIN to JOIN doesn't make any
difference.
Any hints are very welcome
Thanks a lot in advance,
Nico Callewaert
I have something strange. I have a query that executes in less then a
second when I test it interactively in IBConsole, when I prepare the query
in the IB_Query component it's still fast, but when I prepare the query when
my program it takes 10 seconds !! The connection to the database was
already open. Then to set Active := True, still 4 seconds. Here is the
SQl code :
SELECT A.DealerNo,
A.Name,
A.Contact,
A.Alfa,
A.Title,
A.Address1,
A.Address2,
A.Zip,
A.City,
A.Phone,
A.Fax,
A.E_Mail,
A.Website,
A.Comment,
A.Vat,
A.Country_EG,
A.Reduction,
A.BankAccount,
A.Foreign_BankCode,
A.Foreign_BankName,
A.Foreign_BankCity,
A.Foreign_BankSwift,
A.General_Acc,
A.Coin,
B.Name,
C.Province,
D.Descr,
E.Descr_1,
F.Descr_1,
G.Language_Descr
FROM Dealer A
LEFT OUTER JOIN Country B ON B.Code = A.Country
LEFT OUTER JOIN State_Prov C ON C.Code = A.State_Prov
LEFT OUTER JOIN Coin D ON D.Code = A.Coin
LEFT OUTER JOIN Deliv_Cond E ON E.Code = A.Deliv_Cond
LEFT OUTER JOIN Payment_Cond F ON F.Code = A.Payment_Cond
LEFT OUTER JOIN Language G ON G.Language = A.Language
WHERE A.DealerNo = :qDealerNo
The plan seems ok to me :
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (RDB$PRIMARY52),B INDEX
(RDB$PRIMARY28)),C INDEX (RDB$PRIMARY39)),D INDEX (RDB$PRIMARY10)),E INDEX
(RDB$PRIMARY35)),F INDEX (PAYMENT_COND_CODE)),G INDEX (RDB$PRIMARY4))
Just strange that interactive everything is OK, but inside the program it's
acting weird. Changing from LEFT OUTER JOIN to JOIN doesn't make any
difference.
Any hints are very welcome
Thanks a lot in advance,
Nico Callewaert