On Friday 24 October 2003 15:49, Brenden Walker wrote:

> > -----Original Message-----
> > From: Yves Glodt [mailto:yg@...]
> > Sent: Friday, October 24, 2003 9:22 AM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Index question
> >
> >
> >
> > Hello,
> >
> > we are trying to enhance performance of an application we
> > resell. It has about 50 tables, and one of them contains many
> > rows of calculated data (about 1300000). The field IPN is the
> > persons id who the data belongs to. With 45 persons, that
> > makes ~28000 records per person. Selects are mostly made of
> > the 5th column, with the first 4 columns in the where-clause.
>
> You'd probably get better help by sending a query that seems to be
> slow, along with the plan that FB used.
>
> Without example queries it's very difficult to offer index
> suggestion.

ok, I have some more info. I also attached an excerpt of the table data.
These are the queries made (en gros, of course the provided integers
vary in reality):

SELECT * from WT_CPT_VAL c WHERE c.IPN = 31 AND c.COUNTER_VALUE_GROUP =
500 AND c.COUNTER_VALUE_NUMBER = 9 AND c.COUNTER_VALUE_DATE >= 145000
AND c.COUNTER_VALUE_DATE <= 146000

The column COUNTER_VALUE_DATE represents a day value. The range can be
small (like 145000 and 145030), or not, more like 145000 and
147000.

Also there are deletes like this:
DELETE FROM WT_CPT_VAL WHERE IPN = 31 AND COUNTER_VALUE_GROUP = 500 AND
COUNTER_VALUE_NUMBER = 9 and COUNTER_VALUE_DATE = 145000

or over a date range with:
... (COUNTER_VALUE_DATE >= 145000 AND COUNTER_VALUE_DATE <= 145030)


Generally, there are calculations made over a specific period of time,
which can vary between days and sometimes even years.

The app then deletes all the counter values that exist for a specific
day, and then inserts them one by one again, like this:

DELETE FROM WT_CPT_VAL WHERE IPN = 31 AND COUNTER_VALUE_GROUP = 500 AND
COUNTER_VALUE_NUMBER = 9 and (COUNTER_VALUE_DATE >= 145000 AND
COUNTER_VALUE_DATE <= 145030)

and then:

INSERT INTO WT_CPT_VAL VALUES (31, 500, 9, 145123, xxxxx);
INSERT INTO WT_CPT_VAL VALUES (31, 500, 10, 145123, xxxxx);
repeat 20 times...
INSERT INTO WT_CPT_VAL VALUES (31, 500, 9, 145124, xxxxx);
INSERT INTO WT_CPT_VAL VALUES (31, 500, 10, 145124, xxxxx);
repeat 20 times...
etc

This is repeated for every day the calculations are made (145000,
145001, 145002, ...). And it's this calculation process that appears
slow.

In some special cases, when no counters exist at all, only the inserts
are made.

I don't know if and how transactions are used, as the app has been coded
in TPW, and uses the 16bit BDE (V2.51).
gds.dll from 1995 is the IB client... :-| We have no access to the
source, so I can only create, add or modify indexes. I can provide an
example DB in case of need.

Can we improve speed like this, or is it out of our reach?

regards,
Yves

> ------------------------ Yahoo! Groups Sponsor
> ---------------------~--> Rent DVDs from home.
> Over 14,500 titles. Free Shipping
> & No Late Fees. Try Netflix for FREE!
> http://us.click.yahoo.com/mk9osC/hP.FAA/3jkFAA/67folB/TM
> ---------------------------------------------------------------------
>~->
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/

--
Linux 2.4.22-1-k7 #1 Sat Sep 6 02:13:04 EST 2003 i686
22:15:09 up 2:41, 1 user, load average: 0.52, 0.29, 0.29

----------

SQL> select * from WT_CPT_VAL where IPN=31;

IPN COUNTER_VALUE_GROUP COUNTER_VALUE_NUMBER COUNTER_VALUE_DATE COUNTER_VALUE_VALUE
============ =================== ==================== ================== =======================
31 530 3 147456 3.500000000000000
31 550 1 147456 25.50000000000000
31 540 11 147463 13272.00000000000
31 540 9 147463 13272.00000000000
31 540 4000 147457 -28800.00000000000
31 500 2 147457 4644.000000000000
31 500 4 147457 0.000000000000000
31 500 5 147457 0.000000000000000
31 500 6 147457 0.000000000000000
31 500 7 147457 1.000000000000000
31 500 9 147457 16.00000000000000
31 500 10 147457 0.000000000000000
31 500 11 147457 -28800.00000000000
31 500 12 147457 28800.00000000000
31 500 14 147457 167.0000000000000
31 530 3 147457 3.500000000000000
31 550 1 147457 25.50000000000000
31 520 8 147455 0.000000000000000
31 520 2 147457 0.000000000000000
31 520 3 147457 0.000000000000000

SQL> select count(*) from WT_CPT_VAL where IPN=31;

COUNT
============

45821


[Non-text portions of this message have been removed]