Optimizeing my Table

[gkvi...@gmail.com]

Hello

I have defined the following table

CREATE TABLE ALARME (
  SPS_NODE SMALLINT NOT NULL,
  TYP SMALLINT NOT NULL,
  ELEMENT SMALLINT NOT NULL,
  KOMMT TIMESTAMP NOT NULL,
  GEHT TIMESTAMP,
  QUITTIERT TIMESTAMP,
  GRUND SMALLINT);


ALTER TABLE ALARME ADD CONSTRAINT PK_ALARME PRIMARY KEY (SPS_NODE,TYP,ELEMENT,KOMMT);

Adding a new Alarm: The Insert statement are the follow

INSERT INTO alarme (SPS_Node,Typ,Element,Kommt,Grund) VALUES(%d, %d, %d,''%s'',%d)

Ack the Alarme: the update statement are the follow

UPDATE alarme SET Geht=''%s'', Quittiert=''%s'' WHERE (SPS_Node=%d AND Typ=%d AND Element=%d AND Kommt IS NOT NULL AND Geht IS NULL)

The insert statement are fast, but the Update statement are sometimes pretty slow

Does somebody have an idea how to optimize the table definition, like adding an index?

The Table "alarme" has about 1'400'000 rows

Grateful for any advice

Gregor

[Mark Rotteveel]

The condition `Kommt IS NOT NULL` is not necessary: KOMMT is declared as
NOT NULL.

What is the plan of the update statement? What is the output of `gstat
<your-database> -u sysdba -r -t ALARME`?

Not related to performance, but your use of %d and %s suggests you're
not using parameters, and instead interpolating values directly into
your statement strings, which likely makes your code vulnerable to SQL
injection.

Mark
--
Mark Rotteveel

[gkvi...@gmail.com]

Plan:
PLAN (ALARME INDEX (IDX_ALARME))
Adapted plan:

PLAN (ALARME INDEX (IDX_ALARME))

gstat:

Gstat execution time Wed Jul 27 12:58:02 2022

Database header page information:
        Flags                   0
        Generation              6411996
        System Change Number    0
        Page size               4096
        ODS version             12.0
        Oldest transaction      6414870
        Oldest active           6414871
        Oldest snapshot         6414871
        Next transaction        6419495
        Sequence number         0
        Next attachment ID      675
        Implementation          HW=AMD/Intel/x64 little-endian OS=Windows CC=MSVC
        Shadow count            0
        Page buffers            32768
        Next header page        0
        Database dialect        3
        Creation date           Jul 23, 2022 10:46:35
        Attributes              force write

    Variable header data:
        Sweep interval:         200000
        *END*


Database file sequence:
File D:\LSYS\DHZ_LSYS.FDB is the only file

Analyzing database pages ...
ALARME (217)
    Primary pointer page: 480, Index root page: 481
    Total formats: 1, used formats: 1
    Average record length: 36.94, total records: 1319238
    Average version length: 0.00, total versions: 0, max versions: 0
    Average fragment length: 14.00, total fragments: 2, max fragments: 1
    Average unpacked length: 40.00, compression ratio: 1.08
    Pointer pages: 32, data page slots: 25392
    Data pages: 25384, average fill: 69%
    Primary pages: 25382, secondary pages: 2, swept pages: 25370
    Empty pages: 12, full pages: 25368
    Fill distribution:
         0 - 19% = 14
        20 - 39% = 0
        40 - 59% = 1
        60 - 79% = 25369
        80 - 99% = 0

    Index FK_ALARME (2)
        Root page: 2155694, depth: 3, leaf buckets: 1622, nodes: 1319238
        Average node length: 4.92, total dup: 1319207, max dup: 1034012
        Average key length: 2.00, compression ratio: 1.03
        Average prefix length: 2.06, average data length: 0.00
        Clustering factor: 69867, ratio: 0.05
        Fill distribution:
             0 - 19% = 0
            20 - 39% = 0
            40 - 59% = 15
            60 - 79% = 1
            80 - 99% = 1606

    Index PK_ALARME (0)
        Root page: 1973861, depth: 3, leaf buckets: 3135, nodes: 1319238
        Average node length: 9.29, total dup: 0, max dup: 0
        Average key length: 6.38, compression ratio: 3.92
        Average prefix length: 21.61, average data length: 3.38
        Clustering factor: 364631, ratio: 0.28
        Fill distribution:
             0 - 19% = 0
            20 - 39% = 51
            40 - 59% = 7
            60 - 79% = 59
            80 - 99% = 3018

Gstat completion time Wed Jul 27 12:58:03 2022

I'm not avoid of code injection, i'ts a delphi w32 app and run on a customer PC

Regards

Gregor

[Elmar Haneke]

You should have al look at the execution plan for your query.

The Part "SPS_Node=%d AND Typ=%d AND Element=%d" can use the primary key index for "Kommt IS NOT NULL AND Geht IS NULL" there is no index available. In May be the case that optimizer decides not to use index at all.

You should try to materialize "Kommt IS NOT NULL AND Geht IS NULL" to an additional Column (e.g. "offen") type small int which is 1 on condition true and 0 otherwise.

This allows an Index in "SPS_NODE,TYP,ELEMENT,OFFEN" and your query becomes

UPDATE alarme SET Geht=''%s'', Quittiert=''%s'' WHERE (SPS_Node=%d AND Typ=%d AND Element=%d AND Offen=1)

which should definitely use the index created.

Am 27.07.22 um 10:00 schrieb gkvi...@gmail.com:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/0e665278-5f4c-45d5-a520-8c048d9e2f54n%40googlegroups.com.

-- 
Mit freundlichen Grüßen

Elmar Haneke

===========================================================
|Dr. Elmar Haneke                   Tel: +49-2241-39749-0 |
|                                   Fax: +49-2241-39749-30|
|Haneke Software                    WWW: www.haneke.de    |
|Dr. Elmar und Hubertus Haneke      Mail: el...@haneke.de |
|Johannesstraße 41                                        |
|53721 Siegburg                     USt-IdNr. DE 123387209|
===========================================================

[gkvi...@gmail.com]

Thanks for your tip. :-)

That with the index is clear to me so far. Also that the column "GEHT" is not indexed. Are the @Timestamp columns not indexed even if they are defined in the index? Does it have to be a SmallInt or Integer column? Or is the indexed timestamp column just not performant?

[gkvi...@gmail.com]

My idea are to add a index

CREATE INDEX IDX_ALARME ON ALARME(SPS_NODE,TYP,ELEMENT,GEHT);

i hope that should help.

now the plan are

Plan:
PLAN (ALARME INDEX (IDX_ALARME))
Adapted plan:
PLAN (ALARME INDEX (IDX_ALARME))

it runs on my developper PC, will also test it on the live system

[Karol Bieniaszewski]

Hi

 

Your PK is „SPS_NODE,TYP,ELEMENT,KOMMT”

And your update work on the part of it „SPS_NODE,TYP,ELEMENT”.

Then the main question is how many records are updated during such slow update?

And any triggers ON UPDATE on this table?

 

Regards,

Karol Bieniaszewski

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3779f79a-0eae-4434-b8af-24f84b8b8892n%40googlegroups.com.

 

[gkvi...@gmail.com]

the update affects 1-4 records

Trigger

  Post_Event 'trgAlarme_Change';

that triggers a select statment in my program to show the current active alarms

SELECT
  ALARME.SPS_NODE,
  ALARME.TYP,
  ALARME.ELEMENT,
  ALARME.KOMMT,
  ALARME.GEHT,
  ALARME.QUITTIERT,
  ALARMGRUPPENTEXTE.BEZEICHNUNG AS ALARMGRUPPE_BEZEICHNUNG,
  ELEMENTE.JOBID,
  ELEMENTE.BEZEICHNUNG AS ELEMENT_BEZEICHNUNG,
  SPS_NODE.BEZEICHNUNG AS SPS_BEZEICHNUNG,
  ALARMGRUND.BEZEICHNUNG AS ALARMGRUND_BEZEICHNUNG
FROM
  ALARME
  INNER JOIN ALARMGRUPPEN ON (ALARME.SPS_NODE = ALARMGRUPPEN.SPS_NODE)
  AND (ALARME.TYP = ALARMGRUPPEN.TYP)
  AND (ALARME.ELEMENT = ALARMGRUPPEN.ELEMENT)
  INNER JOIN ELEMENTE ON (ALARME.SPS_NODE = ELEMENTE.SPS_NODE)
  AND (ALARME.TYP = ELEMENTE.TYP)
  AND (ALARME.ELEMENT = ELEMENTE.ELEMENT)
  INNER JOIN ALARMGRUPPENTEXTE ON (ALARMGRUPPEN.GRUPPE = ALARMGRUPPENTEXTE.GRUPPE)
  INNER JOIN SPS_NODE ON (ALARME.SPS_NODE = SPS_NODE.SPS_NODE)
  INNER JOIN ALARMGRUND ON (ALARME.GRUND = ALARMGRUND.GRUND)
WHERE
  (ALARME.GEHT IS NULL OR
  ALARME.QUITTIERT IS NULL) AND
  ELEMENTE.JOBID = 0 AND
  (ALARME.SPS_NODE = 30 OR
  ALARME.SPS_NODE = 31 OR
  ALARME.SPS_NODE = 101 OR
  ALARME.SPS_NODE = 102 OR
  ALARME.SPS_NODE = 103 OR
  ALARME.SPS_NODE = 150 OR
  ALARME.SPS_NODE = 151)
ORDER BY
  ALARME.KOMMT DESC

ROWS 1

Plan:
PLAN SORT (JOIN (ALARME INDEX (PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME), SPS_NODE INDEX (PK_SPS_NODE), ALARMGRUND INDEX (PK_ALARMGRUND), ALARMGRUPPEN INDEX (PK_ALARMGRUPPEN), ALARMGRUPPENTEXTE INDEX (PK_ALARMGRUPPENTEXTE), ELEMENTE INDEX (PK_ELEMENTE)))
Adapted plan:
PLAN SORT (JOIN (ALARME INDEX (PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME, PK_ALARME), SPS_NODE INDEX (PK_SPS_NODE), ALARMGRUND INDEX (PK_ALARMGRUND), ALARMGRUPPEN INDEX (PK_ALARMGRUPPEN), ALARMGRUPPENTEXTE INDEX (PK_ALARMGRUPPENTEXTE), ELEMENTE INDEX (PK_ELEMENTE)))

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 27.07.2022 17:21:
> the update affects 1-4 records

In this case you must use Firebird Audit to find out full stats of your
query. Update of 1-4 records with such plan cannot be so slow for nothing.
Either you have heavy triggers or initiate heavy garbage collection (may be both).

--
WBR, SD.

[gkvi...@gmail.com]

OK, thx for all the tipps

Gregor

[Svein Erling Tysvær]

How many records in ALARME has SPS_NODE 30, 31, 101, 102, 103, 150 or 151? My guess is that your update query is OK, but that the trigger with this OR statement isn't selective enough. Though it is just a guess. The easy way to tell whether the query or trigger is to blame is to deactivate the trigger and see if that speeds up things. If it does, then the trigger is guilty and you can dig deeper and check whether or not my guess is correct or not, if things are equally slow with the index deactivated, then the trigger is innocent.

HTH,

Set

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/bc25051d-a80f-4f5b-8c63-a064b3ce80edn%40googlegroups.com.

[gkvi...@gmail.com]

The Table "alarme" has about 1'400'000 rows

I will try that. thanks for your sugestion.