Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a we
ek.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
.................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know wha
t
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using
a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need a
t
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain,
a
> C# program is probabaly better than an extended stored procedure. Extend
ed
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL.
So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation o
n
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>