At what throughput should I leave a connection open ?

I am used to writing applications that hit the database "every so often" and am happy with opening and closing the connection to SQL Server for each one.

I am now writing an application that monitors a table where rows are written to it by a 3rd party application at possibly several rows per second. My job is to "pickup" those rows, analyse the data and move them to different tables. This will be done with a timer which is currently set to tick every second.

My question is: At what stage should I start to think about keeping open a permanent connection to the database ?

1 row per second ?

100 rows per second ?

Any suggestions appreciated.

Steve.Can you use a trigger? This way you do not need to poll. Presuming SQL can be used for the analysis.|||Thanks Doug,

It's a good idea, however, the analysis is quite complex and I think it needs to be done within my business object. I'll certainly look into moving as much code as possible into a trigger though.

Your suggestion has given me the idea of using an insert trigger to let the business object know when to run its analysis code rather than using a timer. Why poll and hit the database when there are no rows there ?

Steve.