I created a small database with 5 tables in Pervasive SQL.2000i (max.record number: 300). I wrote a simple SQL statement in SQL Data Manager direct in PCC. I received very fast the result (1 sec). Then I added a sub-select in WHERE condition of SQL statement. The query runs 50 sec. For a comparsion I migrated the Pervasive Db into MS SQL Server 7 Db (same tables, same data). I received the query after 1 sec (!!!). It was the same SQL statement with a sub-select. What is wrong ? Why needs Pervasive ODBC so long ? It would be nice to have a little idea ! Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Bad performance with Pervasive SQL 2000.i ??
- Thread starter holgermol
- Start date
- Thread starter
- #2
In addition to my question (holgermol). I tested some things last day. I did the following for having a better response time: I created logical indexes. I added WHERE conditions which mainly content the created indexes. I increased the response time from 50 sec to 10 sec. (I read some very good ideas in this forum.) I checked out a 3rd party provider for Pervasive ODBC driver (Merant - response time for my report: ca. 2 sec.).
Now I know its very important to have the correct indexes and a complete SQL statement. But I used a small database. If I try to getting a good report by a bigger customer datebase (over 10,000 records)...then its always yet a disaster for "fast" reporting. Here are the results with the customer database (same SQL statement): I cancelled the Pervasive-query after 30 minutes without an answer. In SQL Server: I received the result after 2 sec.
Perhaps any person can give me further ideas to increasing the pesponse time. Thank you very much. Holger
Now I know its very important to have the correct indexes and a complete SQL statement. But I used a small database. If I try to getting a good report by a bigger customer datebase (over 10,000 records)...then its always yet a disaster for "fast" reporting. Here are the results with the customer database (same SQL statement): I cancelled the Pervasive-query after 30 minutes without an answer. In SQL Server: I received the result after 2 sec.
Perhaps any person can give me further ideas to increasing the pesponse time. Thank you very much. Holger
Vancouverite
MIS
Can you show the structure of your query? It would help in defining a quick query, or saying why PSQL will not process this query quickly.
- Thread starter
- #4
I want to get a list of amounts converted in EUR. I have amounts in different currencies in database. Thats the reason, why I need the newest currency rate from table "currency_rate" for calculation. Here is the statement:
SELECT
b.balance_date bdate,
a.curr_code,
c.spot_rate,
b.amount
FROM
balances b,
accounts a,
currency_rate c
WHERE
b.balance_type = 1 AND
b.acc_code = a.acc_code AND
a.curr_code <> 'EUR' AND
c.nature = '0' AND
c.curr_code = a.curr_code AND
c.fx_rate_code = '' AND
c.ref_curr_code = 'EUR' AND
c.xrate_date =
(SELECT MAX(c2.xrate_date)
FROM currency_rate c2
WHERE c2.nature = '0' AND
c2.curr_code = a.curr_code AND
c2.fx_rate_code = '' AND
c2.ref_curr_code = 'EUR')
PSQL needs 1 sec for response without the sub select. The WHERE conditions for the fields "fx_rate_code" and "ref_curr_code" are not really nessecary. But these fields are part of the indexes. PSQL needs 10 sec with these both conditions, and 50 sec without these both. Following number of records: balances 160, accounts 31 and currency_rate 516. Thanks for your help.
Holger
SELECT
b.balance_date bdate,
a.curr_code,
c.spot_rate,
b.amount
FROM
balances b,
accounts a,
currency_rate c
WHERE
b.balance_type = 1 AND
b.acc_code = a.acc_code AND
a.curr_code <> 'EUR' AND
c.nature = '0' AND
c.curr_code = a.curr_code AND
c.fx_rate_code = '' AND
c.ref_curr_code = 'EUR' AND
c.xrate_date =
(SELECT MAX(c2.xrate_date)
FROM currency_rate c2
WHERE c2.nature = '0' AND
c2.curr_code = a.curr_code AND
c2.fx_rate_code = '' AND
c2.ref_curr_code = 'EUR')
PSQL needs 1 sec for response without the sub select. The WHERE conditions for the fields "fx_rate_code" and "ref_curr_code" are not really nessecary. But these fields are part of the indexes. PSQL needs 10 sec with these both conditions, and 50 sec without these both. Following number of records: balances 160, accounts 31 and currency_rate 516. Thanks for your help.
Holger
- Thread starter
- #5
I want to get a list of amounts converted in EUR. I have amounts in different currencies in database. Thats the reason, why I need the newest currency rate from table "currency_rate" for calculation. Here is the statement:
SELECT
b.balance_date bdate,
a.curr_code,
c.spot_rate,
b.amount
FROM
balances b,
accounts a,
currency_rate c
WHERE
b.balance_type = 1 AND
b.acc_code = a.acc_code AND
a.curr_code <> 'EUR' AND
c.nature = '0' AND
c.curr_code = a.curr_code AND
c.fx_rate_code = '' AND
c.ref_curr_code = 'EUR' AND
c.xrate_date =
(SELECT MAX(c2.xrate_date)
FROM currency_rate c2
WHERE c2.nature = '0' AND
c2.curr_code = a.curr_code AND
c2.fx_rate_code = '' AND
c2.ref_curr_code = 'EUR')
PSQL needs 1 sec for response without the sub select. The WHERE conditions for the fields "fx_rate_code" and "ref_curr_code" are not really nessecary. But these fields are part of the indexes. PSQL needs 10 sec with these both conditions, and 50 sec without these both. Following number of records: balances 160, accounts 31 and currency_rate 516. Thanks for your help.
Holger
SELECT
b.balance_date bdate,
a.curr_code,
c.spot_rate,
b.amount
FROM
balances b,
accounts a,
currency_rate c
WHERE
b.balance_type = 1 AND
b.acc_code = a.acc_code AND
a.curr_code <> 'EUR' AND
c.nature = '0' AND
c.curr_code = a.curr_code AND
c.fx_rate_code = '' AND
c.ref_curr_code = 'EUR' AND
c.xrate_date =
(SELECT MAX(c2.xrate_date)
FROM currency_rate c2
WHERE c2.nature = '0' AND
c2.curr_code = a.curr_code AND
c2.fx_rate_code = '' AND
c2.ref_curr_code = 'EUR')
PSQL needs 1 sec for response without the sub select. The WHERE conditions for the fields "fx_rate_code" and "ref_curr_code" are not really nessecary. But these fields are part of the indexes. PSQL needs 10 sec with these both conditions, and 50 sec without these both. Following number of records: balances 160, accounts 31 and currency_rate 516. Thanks for your help.
Holger
Vancouverite
MIS
(1) Try this sub-select. I believe that it will be faster:
(SELECT MAX(c2.xrate_date)
FROM currency_rate c2
WHERE c2.nature = c.nature AND
c2.curr_code = c.curr_code AND
c2.fx_rate_code = c.fx_rate_code AND
c2.ref_curr_code = c.ref_curr_code)
(2) Also, try this subselect:
AND NOT EXISTS (SELECT *
FROM currency_rate c2
WHERE c2.nature = c.nature AND
c2.curr_code = c.curr_code AND
c2.fx_rate_code = c.fx_rate_code AND
c2.ref_curr_code = c.ref_curr_code AND
c2.xrate_date > c.xrate_date)
Let me know if either of these is better. (2) can also be written without a subselect:
SELECT
b.balance_date bdate,
a.curr_code,
c.spot_rate,
b.amount
FROM
balances b
INNER JOIN accounts a ON b.acc_code = a.acc_code
INNER JOIN currency_rate c on c.curr_code = a.curr_code AND
LEFT JOIN currency_rate c2 on c2.nature = c.Nature AND
c2.curr_code = c.curr_code AND
c2.fx_rate_code = c.fx_rate_code AND
c2.ref_curr_code = c.ref_curr_code AND
c2.xrate_date >c.xrate_date
WHERE
b.balance_type = 1 AND
b.acc_code = a.acc_code AND
a.curr_code <> 'EUR' AND
c.nature = '0' AND
c.fx_rate_code = '' AND
c.ref_curr_code = 'EUR' AND
c2.xrate_date Is Null
You may test it as well
(SELECT MAX(c2.xrate_date)
FROM currency_rate c2
WHERE c2.nature = c.nature AND
c2.curr_code = c.curr_code AND
c2.fx_rate_code = c.fx_rate_code AND
c2.ref_curr_code = c.ref_curr_code)
(2) Also, try this subselect:
AND NOT EXISTS (SELECT *
FROM currency_rate c2
WHERE c2.nature = c.nature AND
c2.curr_code = c.curr_code AND
c2.fx_rate_code = c.fx_rate_code AND
c2.ref_curr_code = c.ref_curr_code AND
c2.xrate_date > c.xrate_date)
Let me know if either of these is better. (2) can also be written without a subselect:
SELECT
b.balance_date bdate,
a.curr_code,
c.spot_rate,
b.amount
FROM
balances b
INNER JOIN accounts a ON b.acc_code = a.acc_code
INNER JOIN currency_rate c on c.curr_code = a.curr_code AND
LEFT JOIN currency_rate c2 on c2.nature = c.Nature AND
c2.curr_code = c.curr_code AND
c2.fx_rate_code = c.fx_rate_code AND
c2.ref_curr_code = c.ref_curr_code AND
c2.xrate_date >c.xrate_date
WHERE
b.balance_type = 1 AND
b.acc_code = a.acc_code AND
a.curr_code <> 'EUR' AND
c.nature = '0' AND
c.fx_rate_code = '' AND
c.ref_curr_code = 'EUR' AND
c2.xrate_date Is Null
You may test it as well
- Thread starter
- #7
The first one need only 7 sec. And the second one is faster: only 3 sec. Its fine. The last one with JOINs doesn't work. I'm looking for the reason. I didn't work with INNER JOIN, LEFT JOIN etc. before. This are very good advices. I will try out the new possiblities in other statements. Thank you very much! Holger
Vancouverite
MIS
The last one doesn't work because I left in an inappropriate AND - at the "INNER JOIN currency_rate c on c.curr_code = a.curr_code >>>AND<<<" line. Try removing that one.
Glad I could help.
Glad I could help.
Vancouverite
MIS
The last one doesn't work because I left in an inappropriate AND - at the "INNER JOIN currency_rate c on c.curr_code = a.curr_code >>>AND<<<" line. Try removing that one.
Glad I could help.
Glad I could help.
- Thread starter
- #10
- Status
Similar threads
- Locked
- Question