Triggers and stored procedures in C#

[FP]

Hi,
i have read that with Visual Studio 2005 it's possible to create stored
procedures and triggers for a Sql Server 2005 database.
I must admit that i have already written the stored procedures and triggers
for my database using T-SQL,but i noticed bad performance (its kinda
slow),so i ask:writing them in C# would i achieve a (considerable)
performance gain?
Thanks in advance

 

[Nicholas Paldino [.NET/C# MVP]]

FP,

It really depends on what you are doing in the database. If you are
performing a lot of computational code (as opposed to set operations), then
yes, you could see a performance increase. However, if you are performing
what are really set-based operations, then using .NET in SQL Server isn't
going to help. Rather, you need to look at optimizing your queries and the
underlying database (indexes on tables the queries access) to improve
performance.

Can you give an example of something that is performing slow (a stored
procedure or trigger)? The actual code would be helpful.

 

[Olmo]

Nicholas Paldino [.NET/C# MVP] ha scritto:

It really depends on what you are doing in the database. If you are
performing a lot of computational code (as opposed to set operations), then
yes, you could see a performance increase. However, if you are performing
what are really set-based operations, then using .NET in SQL Server isn't
going to help. Rather, you need to look at optimizing your queries and the
underlying database (indexes on tables the queries access) to improve
performance.

First of all thanks for your quick reply Nicholas
Unfortunately now i am at another computer,so i cant copy some code.
I am really a newbie to database programming,so please forgive this silly
question:what do you exactly mean with computational code?Complex
arithmetical computations?
In my T-SQL code,the only computational operations i do are using the AVG
command somewhere,but it is mostly based on select,update,insert
queries,tables creation and several types conversions (i have to deal a lot
with datetime type).I also have some while loops.
So i think my T-SQL code might be enough.Do you confirm this?
Thanks again for your help

 

[FP]

Sorry i used the wrong identity (Olmo) to reply

 

[Nicholas Paldino [.NET/C# MVP]]

FP (Olmo),

When I say computational, I mean things like processing strings
(splitting a string apart, for example) rather than seeing if a value exists
in a column in a table (set operation).

Based on what you said, it seems like you need to create better queries
and possibly index in some places. It doesn't sound like you are doing
anything computationally intensive (at least in terms of what T-SQL is
doing), just set-based operations.

Also, if you have while loops, you might be able to condense that into a
single operation, depending on what you do in the while loop.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Nicholas Paldino [.NET/C# MVP] ha scritto:

It really depends on what you are doing in the database. If you are
performing a lot of computational code (as opposed to set operations),
then
yes, you could see a performance increase. However, if you are
performing
what are really set-based operations, then using .NET in SQL Server isn't
going to help. Rather, you need to look at optimizing your queries and
the
underlying database (indexes on tables the queries access) to improve
performance.

First of all thanks for your quick reply Nicholas
Unfortunately now i am at another computer,so i cant copy some code.
I am really a newbie to database programming,so please forgive this silly
question:what do you exactly mean with computational code?Complex
arithmetical computations?
In my T-SQL code,the only computational operations i do are using the AVG
command somewhere,but it is mostly based on select,update,insert
queries,tables creation and several types conversions (i have to deal a
lot
with datetime type).I also have some while loops.
So i think my T-SQL code might be enough.Do you confirm this?
Thanks again for your help

 

[Guest]

I hope I'm not barging in on the general theme here as I've already read
Nick's replies. But my approach has been that I only will look at using
CLR-hosted stored procs that call into .NET managed code when there is no
compatible functionality in T-SQL. For example, if you need to be able to get
an RSS Feed and store it in a database table, there is no native T-SQL code
that will do this, so you would write a GetRSSFeed (url) CLR-hosted assembly
/ class with a T-SQL front end that would do this and install it in Sql
Server 2005 +.
---Peter
"Inside every large program, there is a small program trying to get out."
http://www.eggheadcafe.com
http://petesbloggerama.blogspot.com
http://www.blogmetafinder.com

 

[Marc Gravell]

Further to the other replies... remember that your database is the
hardest thing to scale out, and scaling up gets really expensive
really quickly...

Unless there is a significant reason to use .NET in the database (i.e.
you absolutely need something that T-SQL doesn't provide, such as zip/
crypto-stream support), then stick with T-SQL and let your database
worry about set-based data operations. There are /lots/ of ways to
write better T-SQL to improve performance, but it takes time to learn
the tricks and when each trick can help.

Marc