[Solved] Speeding up TRIGGERS

[dreamquartz]

Hi All,

As you all will understand, more checks = slower database.
I am coming across that more and more.
I recently introduced 4 TRIGGERS and they are all related to an Address that needs to be verified (see: viewtopic.php?f=13&t=90308).
The problem is obvious. All 4 TRIGGERS need to run before the Address can be accepted or not.
I am now looking to speed up this process, because the resultset is taking at least 10s longer per address entry.

This is of course not acceptable, so what can be done?
Questions I hope to get some feedback on:
Q1. Is there a way to combine TRIGGERS?
I tried to combine them in different ways, but that does not seem to work. I am coming across the situation that if a record is empty, but is part of the TRIGGER, the TRIGGER does not react. The examples below are all related to table "tAddressBare" [see below]. The table records the different addresses, and will therefore have "NULL" records. This is acceptable.

Example 1:
Address 1: 1234 Rempel Street, London
Address 2: 5647 Cambridge High E., Newcastle
The difference between the 2 is "E." (standing for "East").
The design decision was to create a separate record ("tAddressBare"."StreetDesignate") because our Client has a lot of clients where the addresses are as Address 2, in the Example.

To cover both Address examples I created 2 TRIGGERS.
However they are almost identical and that suggests that they can be combined into one.
I would love to be able to do so, but do not see how.

Example 2:
Address 3: PO Box 789, Darwin
The PO Box is recorded in a different record ( "tAddressBare"."POBox") and therefore I assume it needs its own TRIGGER.

Example 3:
Address 4: 5626 Coastal View, Munich
Address 5: 5626 Coastal View, Trenton
Here "Trenton" is a neighbourhood of "Munich" (The neighbourhood is recorded in the table "tCity" [see below], where "tCity"."AreaID" refers to "tCity"."CityID" if the "tCity"."City" is considered to be a neighbourhood of the referenced City).
Because people do write addresses like in this example "Address 5", the database needs to verify against each other, because they are the same address in reality.

Because it is in the same table "tAddressBare", I would love to combine all 4 TRIGGERS into one.

Q2. Can someone provide me with some guidance in the right direction?

All responses are really appreciated.

Dream

tAddressBare.PNG (12.2 KiB) Viewed 1358 times

tCity.PNG (4.34 KiB) Viewed 1358 times

[eremmel]

I doubt it will make much difference to have one iso 4 triggers. Its all about the code you are executing. You need to profile if possible with those non-commercial databases.

You are likely using SQL statements to check against tables. Optimize those queries. Find out if you can improve the queries (Look to execution plans) by adding (full containing) indexes. (Tip: analyze and run the queries outside the trigger, that might be more easy to do.)

General: Prevent to use loops in triggers, but work set oriented and not record oriented.

I had a quick look to your four triggers. It is possible to put them in one trigger. You build the triggers around 'programming by example', but did not build the SQL around the issue you like to solve. Hire a sound SQL programmer for your firm, you will save much time (and likely money as well).

Hint: When your data in tAddressBase doubles; each trigger will double in time as well. Before you created the trigger, you should have observed this by testing the query.

[dreamquartz]

I doubt it will make much difference to have one iso 4 triggers. Its all about the code you are executing. You need to profile if possible with those non-commercial databases.

You are likely using SQL statements to check against tables. Optimize those queries. Find out if you can improve the queries (Look to execution plans) by adding (full containing) indexes. (Tip: analyze and run the queries outside the trigger, that might be more easy to do.)

General: Prevent to use loops in triggers, but work set oriented and not record oriented.

I had a quick look to your four triggers. It is possible to put them in one trigger. You build the triggers around 'programming by example', but did not build the SQL around the issue you like to solve. Hire a sound SQL programmer for your firm, you will save much time (and likely money as well).

Hint: When your data in tAddressBase doubles; each trigger will double in time as well. Before you created the trigger, you should have observed this by testing the query.

Hi eremmel,

This is the feedback I appreciate.

As always I follow the steps as you describe as much as possible.
The coding is based on your input (see: viewtopic.php?f=13&t=86906#p409009 and I still donate to the Red Cross ).
I am continuously using Execution Plans to see where improvements can be made.
I run the Queries outside the TRIGGERS, to determine the results. However TRIGGERS are more limited in their coding flexibility than a Query by the looks of it.

We still can not afford a sound SQL programmer (you and many of the volunteers are of tremendous help).
The project was thrown in our laps, and we went for it. Our Client is very thankful for the help.

The database of our Client is growing by the day and from their perspective very fast.
Yesterday just an other 34 entries, today over 90. These are new People and their related information. The rate is around 200 entries per month, with spikes like these.
Of course the execution speed of Searches/Queries/TRIGGERS are all affected by the growing database.
Our Client is now (as of writing) just shy of 10,000 Person Information entries.

The entries are made manually, because the information is provided using hand written forms (a legal requirement for our Client at this day and age).
Because potential "repeat" entries are spread over such a long time (mostly 3 years, but also 2 years or more), the checks must be very thorough.
All information must be verified. People do not always use the same information (see: viewtopic.php?f=13&t=90308 and OP)

Some of the Queries our Client uses are very big.
Because of handwriting, variation an/or change in information provided, and requirement for accuracy, these Queries are checking against anything provided; even a simple StreetNumber in an address, to determine if the Person related information is already in the database.

Our Client is not really interested in a high speed performance, as long there is a very high level of accuracy.

Thanks,

Dream