"Craig S" <cscheets@dontspam.kc.rr.com> wrote in message
news:OIG$CfXDGHA.1544@TK2MSFTNGP10.phx.gbl...
> I'm upgrading an app I wrote from SQL 2000 to 2005 looking for some
> high-level design advice, just to determine how much should be T-SQL vs.
> CLR, and the best approach to take for performance.  For background, I'm a
> strong c# developer, but with only average SQL skills and I'm just getting
> into SQL 2005.
>
> My Current (SQL 2000) Setup:
> --------------------------------
> My C# app regularly collects performance information, then inserts it into
> a table.  On insert in that table I have a trigger to kick off a couple
> stored procs that compare the new data to other tables where thresholds
> are set. As a simple example, it essentially does a "SELECT * FROM
> Thresholds WHERE @NewlyInsertedValue > ThresholdValue" to determine if the
> current value is in violation of any thresholds.  (There are a couple more
> columns that are considered that makes the query a little more intensive,
> but this is the general idea)
>
> If thresholds are violated, it inserts a record into an 'alerts' table and
> generates an email (by another stored proc).  If no thresholds are
> violated, it runs a stored proc to clear any alerts that might have been
> created previously since we're not in violation anymore.  Since this
> happens EVERY time a new value is collected, I really want to optimize
> this as much as possible.
>
> My Desired (SQL 2005) Setup:
> --------------------------------
> I'm going to replace certain obvious functions like the "send email"
> function from old SQL objects to net CLR objects.  However, I want to
> further optimize the system that compares incoming data with thresholds
> and generates alerts as necessary.  It seems very wasteful to do selects
> every time a new value is inserted, and as the tables grow even with
> proper indexing it is going to get expensive.  Is there a way I can take
> advantage of CLR to maybe keep these thresholds in memory to compare them
> against? Any other ideas about how to accomplish this most efficiently?
>
> Craig
>
> PS - I can post DDL if you really want it, I was just trying to get high
> level design ideas from someone that's done a lot of SQL CLR work already.
>

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:O9eKD9XDGHA.3444@TK2MSFTNGP10.phx.gbl...
> For general data manipulation especially Inserts, Updates, Deletes and
> normal Selects TSQL will always be the best choice.  The CLR is useful for
> heavy calculations / aggregations or when you simply can't do something
> with TSQL. Of coarse anytime you need to visit the filesystem or need the
> equivalent of an extended sp the CLR is prime for that as well. AS for the
> mail component you can use the new DatabaseMail which works pretty well
> and a lot less troublesome than the MAPI version in 2000.  But in general
> I suggest you use stored procedures for this instead of triggers. You have
> a fair amount of processing to do and you should keep triggers as trim as
> possible.  I don't see any reason this logic can not be put into a
> standard TSQL stored procedure. But as for checking the values why not do
> that in the front end or middle tier before you even attempt the insert?
> That kind of information is pretty much static and can be cached very
> easily in the middle tier or front end. If these inserts are many you can
> save a lot of processing etc. on the back end by validating the data
> before you send it in.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Craig S" <cscheets@dontspam.kc.rr.com> wrote in message
> news:OIG$CfXDGHA.1544@TK2MSFTNGP10.phx.gbl...
>> I'm upgrading an app I wrote from SQL 2000 to 2005 looking for some
>> high-level design advice, just to determine how much should be T-SQL vs.
>> CLR, and the best approach to take for performance.  For background, I'm
>> a strong c# developer, but with only average SQL skills and I'm just
>> getting into SQL 2005.
>>
>> My Current (SQL 2000) Setup:
>> --------------------------------
>> My C# app regularly collects performance information, then inserts it
>> into a table.  On insert in that table I have a trigger to kick off a
>> couple stored procs that compare the new data to other tables where
>> thresholds are set. As a simple example, it essentially does a "SELECT *
>> FROM Thresholds WHERE @NewlyInsertedValue > ThresholdValue" to determine
>> if the current value is in violation of any thresholds.  (There are a
>> couple more columns that are considered that makes the query a little
>> more intensive, but this is the general idea)
>>
>> If thresholds are violated, it inserts a record into an 'alerts' table
>> and generates an email (by another stored proc).  If no thresholds are
>> violated, it runs a stored proc to clear any alerts that might have been
>> created previously since we're not in violation anymore.  Since this
>> happens EVERY time a new value is collected, I really want to optimize
>> this as much as possible.
>>
>> My Desired (SQL 2005) Setup:
>> --------------------------------
>> I'm going to replace certain obvious functions like the "send email"
>> function from old SQL objects to net CLR objects.  However, I want to
>> further optimize the system that compares incoming data with thresholds
>> and generates alerts as necessary.  It seems very wasteful to do selects
>> every time a new value is inserted, and as the tables grow even with
>> proper indexing it is going to get expensive.  Is there a way I can take
>> advantage of CLR to maybe keep these thresholds in memory to compare them
>> against? Any other ideas about how to accomplish this most efficiently?
>>
>> Craig
>>
>> PS - I can post DDL if you really want it, I was just trying to get high
>> level design ideas from someone that's done a lot of SQL CLR work
>> already.
>>
>
>