I have a situation i can't find an explanation for, here it is. (I'll use hypothetical info since the original are really big.)
I have a table, let's say:
table_a
-------------
name
last name
dept
status
notes
And this table has a trigger on insert, which does a lot of validation to the info to change the status field of the new record according to the results of the validation, some of the validations are:
- check for the name existing in a dictionary
- check for the last name existing in a dictionary
- check that fields (name,last name,dept) aren't already inserted in table_b
- ... and so on
The thing is, if I do an insert on the table via query, like
insert into table_a
(name,last_name,dept,status,notes)
values
('john','smith',1,0,'new');
it takes only 173 ms to do all the validation process, update the status field and insert the record in the table. (the validation process does all the searches via indexes)
But if I try this via SQLloader, reading a file with 5000 records, it takes like 40 minutes to validate and insert 149 records (of course i killed it...)
So I tried loading the data disabling the trigger (to check speed) and I got that it loads like all the records in less than 10 seconds.
So my question is, what can I do to improve this process? My only theory is that I could be saturating the database because it loads so fast and launches many instances of the trigger, but I really don't know.
My objective is to load around 60 files with info and validate them through the process in the trigger (willing to try other options though).
I would really appreciatte any help you can provide!!
COMPLEMENT---------------------------------------------------------------------------------
Thanks for the answer, now i'll read all about this, now hope you can help me with this part. let me explain some of the functionality i need (and i used a trigger cause i couldn't think of anything else)
so the table data comes with this (important) fields:
pid name lastname birthdate dept cicle notes
the data comes like this
name lastname birthdate dept
now, the trigger does this to the data:
Calls a function to calculate the pid (is calculated based on the name, lastname and birthdate with an algorithm)
Calls a function to check for the names on the dictionary (thats because in my dictionary i have single names, meaning if a person is named john aaron smith jones the function splits john aaron in two, and searches for john and aaron in the dictionary in separate querys, thats why i didn't use a foreign key [to avoid having lots of combinations john aaron,john alan,john pierce..etc]) --->kinda stuck on how to implement this one with keys without changing the dictionary...maybe with a CHECK?, the lastname foreign key would be good idea.
Gets the cicle from another table according to the dept and the current date (because a person can appear twice in the table in the same dept but in different cicle) --->how could i get this cicle value in a more efficient way to do the correct search?
And finally, after all this validation is done, i need to know exactly which validation wasn't met (thus the field notes) so the trigger concatenates all the strings of failed validations, like this:
lastname not in dictionary, cannot calculate pid (invalid date), name not in dictionary
i know that if the constraint check isn't met all i could do is insert the record in another table with the constraint-failed error message, but that only leaves me with one validation, am i right? but i need to validate all of them and send the report to other department so they can review the data and make all the necessary adjustments to it.
Anyway, this is my situation right now, i'll explore possibilities and hope you can share some light on the overall process, Thank you very much for your time.