Triggers or SQL Batch - Performance!

[Antzz]

Hi Guys,
I have a general performance question. We are in the process of building an application which requires a lot of SQL Server processing on incoming data before clients can use it. Incoming data is actually stored in a table.

This data can be processed in two ways -

1) have a trigger on the table which executes the stored procedure as soon as a row is inserted into the table

OR

2) create a job which has the stored procedure and the job executes every minute and processes all the new rows in the table.

During processing, data is retrieved, compared, updated, deleted or inserted based on various business rules involving atleast a set of 20 tables. It is imperative that processing should happen as early as possible from the time data was inserted in the table.

I have also toyed with the idea of an external application doing this processing but this idea is not really fun since lots of data is needed during comparison of each row.

I would love to hear opinions on which option might be better or if there is a third option to accomplish this. I am in the process of testing both these options right now.

Thanks and appreciate all your comments

R

 

[vbkris]

i would go ahead with the idea of a trigger, as this is exactly why triggers have been created for tables...

Known is handfull, Unknown is worldfull

 

[aolb]

I would go with triggers as well. A point to consider is is the db configured for online update performance or reporting performance?

You may want to consider configuring the db for online performance and have another db configured for reporting that is updated by the online db. depends on if this is a consideration.

 

[Antzz]

This is great. Thanks a lot for the inputs guys. Also a fact to consider might be that, data is inserted into the table real fast - sometimes almost at the rate of 2 rows/second.

Do you think this will have an adverse effect? Also there will be atleast 150/200 users connected to the server.

 

[SQLSister]

Go with the trigger but make sure to write it to handle multi-row inserts or updates. If business rules are critical they should always be in triggers or constraints on fields as data is not always inserted through the user interface.

If something can be done through the correct selection of a datatype or through a constraint on the field or a unique index, that is generally a better more efficient way to enforce business rules than a trigger. So if you just want to make sure the data is always a valid date, set the field type to datetime or smalldatetime. If you want to make sure that the values of a field are unique, set a unique index; if you want to make sure that the values are within a constrained group of values (Say <10) then use a constraint. If you want to fill in col_b based the value of col_a and there will be differnt rules depending on which value col_a has, then use a trigger.

And just because you have something in a trigger doesn't mean you shouldn't check it through the user interface first. Often you can constrain values in the user interface by using a pull-down list that is limited to the list or check for certain things (such as whether all required fields are filled in) at the interface level before sending the data to the database. This can cut down on network round trips by only sending good data to be inserted.

And if you do you use the trigger make sure your interface knows if the data gets rejected. Otherwise your users will think the data went in when it did not.



Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Antzz]

Actually, the logic being evaulated against the row is quite complex. Here is a little summary of it since I cannot give out all the details:

As a row is inserted into the table(assuming the evaulation logic is in an insert trigger), following tasks take place:

1) The data in the newly inserted row is normalized. Normalization involves around 10 tables right now. While normalizing data, we also perform inserts in dictionary tables if we find that the columns which we assign to be dictionary items are new to that table. So it does involve comparison to determine if the values are existing or new ones.

2) We have a rules engine which determines special data assignments with this newly inserted row. Right now we have 20 sets of rules which are evaluated to see which rule is satisfied. If a rule is satisfied, no more rules are evaluated in that list.

3) We have a Message broker component(which is different that SQL Message Broker) which determines if the new exam needs to be pushed out to any clients. The business rules which determines this, are again quite complex. A user can have a list of data open in his client computer. The list is a essentially a query. The newly inserted row is checked if it satisfies the query. If so, then it is sent to all users who have this list up in their computers.

As, you can see this is really intensive processing. Since now you have a better idea, would still recommend implementing this on a trigger?

Also, it is essential that all these processes are executed as soon as possible once the new row is inserted.

Right now it takes about a couple of seconds to process one newly inserted row, during which time atleast 5 rows can be inserted into the table. Of course there are peak and off-peak hours and the process does a good job in keeping the list up-to-date.

This definitely is a long post. But I think it will help you in having a deeper insight to the issue and advising me.