I have been mentally struggling with the right way to lay out my N-Tier application using typed datasets for some time now.  I am hoping that if I detail my thoughts and problems on here, someone might take a moment and comment.

Using a simple example of a sites database table (office sites for instance) I will try to explain what I mean.

 So my bottom most tier would be my database.  Now, formerly we would use stored procedures for all the queries and gather everything for class files up the chain, however, with typed datasets, are stored procedures necessary, or is it enough to let the typed dataset generate the sql code for the fill and simply do all your queries and data aggregation through the returned datatable using dataviews?

The next tier would be my data access layer.  Using the code (note that I am just writing this from my head, please assume that there will be syntax errors)

Imports sitesDS 'sites dataset 

public class sitesBLL

public function GetData() as sitesdatatable

dim t as new sitestableadapter

return t.getdata()

end function

end class

In the previous example, it would seem that every time someone loads the page the database would be queried, is this true?

Would something like

class sites

private dt as sitesdatatable = ctype(session("sitesdatatable"), sitesdatatable)

public function getdata() as sitesdatatable

if dt is nothing

dim t as new sitestableadapter

t.fill(dt)

end if

return dt

end function

end class

Would something like this work better?  My caveat to the preceeding is that I am using a session("") variable where there really is no session.  Is there a way to store that datatable in a better variable, I have heard that using application variables might work here, but I am using this code behind a web service which then connects to my presentation layer web site. 

Anyone have any thoughts on this?

-Jason

I'll take your points one by one. I hope its ok! Make sure to tell me if I missed anything.

ow, formerly we would use stored procedures for all the queries and gather everything for class files up the chain, however, with typed datasets, are stored procedures necessary, or is it enough to let the typed dataset generate the sql code for the fill and simply do all your queries and data aggregation through the returned datatable using dataviews?

The whole "everything in stored procs" ideology had roots in 10 years old technology. Its over now. With client/server apps, SOA, or web apps, your clients are never directly accessing the database, so it makes stored procs redundant. SQL Server also handles stored procs the same it does sql queries (go ahead and use a benchmark tools, you'll be surprised) so speed isn't an issue anymore.  In Java, between enterprise javabeans persistance layers and ORMs like Hibernate, stored procs haven't been standard in a while. in the Microsoft world its getting there, and things like LINQ will give the final blow. There is a place and time for stored procedures, but starting with the idea that stored procs is the "default" is a bad one: When you have 150-300 tables, your CRUD operations alone would take over a thousand procs: crazy! How do you manage that @.@

So yes, using queries directly in your datasets is a fine DAL. Since your dataset is an in memory replication of your database anyway, it would make things redundant. (I have to deal with stored procs for work now...and ugh, management nightmare!)

The next tier would be my data access layer

No. Your dataset itself is the DAL. The next step is the Business layer. Is that what you meant? Your code starts with BLL in it afterward.

Ok, here is a potential suggestion for your caching issues afterward:

Assuming your datasets is your DAL. Then you make a BLL on top of that, that will allow you to do various operations, including a layer over your CRUD operations. So far so good. Now, one thing thats nice with .NET 2, is you have access to Object Data Source. Which allows you to map CRUD to business objects.

So what you do, is after you have your BLL, you build lightweight "CRUD" objects at the level of your presentation layer (in app_code). Then use an Object Data Source,  which will see your objects in app_code, and you can map your operations to it.  Then you can use databinding in your presentation layer, while keeping your architecture. Whats nice though, is that ObjectDataSource has an internal Caching feature (look in its properties, its actualy quite powerful), thus you won't have to deal with it yourself. That could save you some time, while still cleanly separating your layers.

Otherwise, if you stick your datasets in Session everytime, you will totally overload your server (they take a lot of memory).

If that doesn't do the trick, look into the Cache object. It has features to handle caching, including cache dependencies to SQL Server 2005 (so if a table change, cache is automatically refreshed, etc).
 

My comments:

>So my bottom most tier would be my database.  Now, formerly we would use stored procedures for all the queries and gather everything for class files up the chain, however, with typed datasets, are stored procedures necessary, or is it enough to let the typed dataset generate the sql code for the fill and simply do all your queries and data aggregation through the returned datatable using dataviews?

You can use typed datasets with stored procedures. Using SPs will give you performance benefits compares to using data views.

>public class sitesBLL

I am curious to know: why is BLL appended to this DAL class? Anyways, why do you need to use a Session object? DAL layer should return data when needed, rest of the time you can use Datasets in your business layer to work with disconnected data. It's the responsibility of the UI Page to worry about data getting loaded each time, not the DAL class. You can use datasets stored in Session object in the UI Page code behind.

Hope this helps,

Vivek

vivek_iit:

You can use typed datasets with stored procedures. Using SPs will give you performance benefits compares to using data views.

I'm not quite sure why you are comparing SP's to dataviews?!, Isn't the comparison between SP and "normal" sql?

Well, as shados wrote there isn't really a performance boost using Stored procedures anymore (actually since SQLServer 7.0).

From: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp

"SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements."

Cheers...

Correct. And since you can use any T-SQL statement in a "client side" SQL as you can in a stored procedure, the only difference between the two is:

1) Its more bytes of text to send an SQL statement than calling a stored procedure

2)While you can set very granular permissions on a table, they won't protect a user from themselves (to delete an entire table through stored procedures, you have to call the procedure a billion times. While you could connect to the table in MS Access, and delete it all in one shot if you have access to the table)

3) Management: Do you rather deal with the database server, or with .NET source files, do you use a lot of dynamic sql, do you...you get the idea. This is where the toss up is 99% of the time, and why both solutions (SQL on the server as stored procedure, or SQL in the code) are useful, depending on the situation.

Performance, unless a query is called so often that 100 extra bytes of text going through the wire makes a significant performance hit, is virtualy never part of the decision anymore. Protecting the users against themselves (irrelevent in web apps usualy) and code maintenance (toss up between the 2) is the only areas that should affect a decision here.

>I'm not quite sure why you are comparing SP's to dataviews?!, Isn't the comparison between SP and "normal" sql?

Many people tend to use DataViews to get "filtered" data instead of using a query/SP. I thought that the original question was related to this practice and hence my answer. Also, just because SQL Server 2000 precompiles statements does not mean that SPs have lost their edge.

I would instead advocate a mixed approach: using inline SQL for CRUD and SPs for data intensive batch operations. Ofcourse SPs are still being used to improve performance and there is no reason to assume that "there is no performance boost using SPs".

Hope this helps,

Vivek

Not to start a flamewar, but the whole SP being used to improve performance is usualy simply because a lot of architects and projects managers do not know how they are being processed. Internally, they -literally- simply are read, then passed the same place where normal queries go through. There is literally no difference in the way a SP or a query is being processed, therefore there is simply no possibilities for them to improve performance, except for bandwith.

I recently heard the same thing, people swearing the stored procs improved performance. You take the exact content of the most intensive stored procedure you can find, copy and paste it in a string in code, execute it through an SQL command, and with the tools in System.Diagnostic compare the two. There literally aren't any difference, unless the query is 700 lines long and you're passing it over a 56k/s wire.

>Not to start a flamewar...

:-)

I think I am enjoying reading your views and presenting my own. Let's take this discussion as a healthy debate so that we all can learn (I think here I am learning!).

Now my views:

>You take the exact content of the most intensive stored procedure you can find, copy and paste it in a string in code, execute it through an SQL command, and with the tools in System.Diagnostic compare the two. There literally aren't any difference, unless the query is 700 lines long and you're passing it over a 56k/s wire.

I beg to differ here. I had a 650 line copy SP (which perfomed a deep copy) and I simply couldn;t convert it to an SQL, it was not at all worth the effort (besides being a maintenance nightmare). Also, one of my friends did a performance check: he executed 10 queries from code and then batched them in an SP an executed the same. Ofcourse the SP version was more faster.

We need to talk realistically here. No one would copy even a 300 line SP in SQL and try to execute it inline.

Let me know your views,

Vivek

Good, I love healthy debates. You have no idea how many people get offended if you dare want to discuss something. 

I beg to differ here. I had a 650 line copy SP (which perfomed a deep copy) and I simply couldn;t convert it to an SQL, it was not at all worth the effort (besides being a maintenance nightmare).

There is nothing to convert, however. Memory tables, variable declarations, exec statements, -everything- you can do in T-SQL can be done in line. They both go through the same parser. The only thing you need to change to "convert" a stored procs to inline, is remove the create/alter statement at the top, and remove the parameters (when you do the sqlcommand's parameters.add, it automaticaly gets added in an in-line sql). It will work no matter what. All keywords, all constructs, everything.

Also, for your friend: were the 10 queries sent as -ONE- statement, or 10 separate statement? There is nothing preventing you from sending them as a single statement.

There is also more to think about: T-SQL is incredibly slow when it comes to string operation and imperative statements. So if you have a lot of those (like if you have to build a dynamic sort clause), you're better off building it client side. In a similar fashion, if you have a table with 300 fields, and you randomly only need 10 of those, you're better off in-line, for example. But that part really goes in the whole maintenance thing, and is all debatable.

My only point is: If you take a stored procs, and execute it in line, except for the bandwith that it takes to send the inline statement through the wire, there cannot be, as per SQL Server's architecture, any performance difference in the execution, as they go through the exact same "pipe" :) And there is no function, statement, or method that cannot be used in line that can in a stored procs.

Can you please explain what you meant then, by :  "I simply couldn;t convert it to an SQL".

Its literally: cut, paste. Thats it. No change in syntax at all, except for removing the "Create...()" at the top. 

vivek_iit:

Also, just because SQL Server 2000 precompiles statements does not mean that SPs have lost their edge.

Read my post there isn't any precompiling going on (not sp nor sql statements).

"A stored procedure is compiled at execution time, like any other Transact-SQL statement"

And.

"SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans."

I'm for SPs when doing calculations on large resultsets, doing datamining, BI etc.

Cheers

I'd like to add a link to a post I put in the FAQ forum:

http://forums.asp.net/thread/1453868.aspx

This only use a very simple example, but it serves the point: It seems most people do not realise that you can use all T-SQL statements in an inline query. Again: anything, no exception, that can be used in a stored procedure (not counting system here), can be done inline. It will parse, it will execute, it will work. Thus literally, the difference is the bandwith to get the text through. You can declare temp tables, begin and end statements, declare variables, have temporary result sets, return multiple result sets, everything works inline. 

>There is nothing to convert, however. Memory tables, variable declarations, exec statements, -everything- you can do in T-SQL can be done in line. They both go through the same parser. The only thing you need to change to "convert" a stored procs to inline, is remove the create/alter statement at the top, and remove the parameters (when you do the sqlcommand's parameters.add, it automaticaly gets added in an in-line sql). It will work no matter what. All keywords, all constructs, everything.

>Its literally: cut, paste. Thats it. No change in syntax at all, except for removing the "Create...()" at the top. 

I wish things were that simple! Problem is that real world business requirements rarely give you the opportunity to have simple queries. You have cursors, convoluted joins, etc etc..you just cant maintain such a procedure inline in SQL (I am talking about string formatting here). 

But I tend to agree with your point. SPs should not be used "blindly".

Regards,

Vivek 

Yes Vivek :) That comes in "maintenance" here. However, it doesn't change my point: your cursors, convoluted joins, etc, they will all -work- inline. The maintenance is an issue, but we're talking about performance here, not maintenance, since my -original- point stated that maintenance was the PRIMARY reason behind stored procedures (the inline SQL equivalent for this, is ORM, thus why its a toss up. ORM makes things easier to manage in a lot of circonstances).

The query, however, will work. Cursors, convoluted joins, everything. It will -work-.

Except (which I forgot), the return statement. That is the one exception. You can, however, have output parameters. 

Ok. I think that when most people talk about inline SQL queries vs SPs, they generally don't assume inline SQL being the code of SP set inside a string variable! People use inline queries as a group of one or max 2 queries or a set of queries fired separately or something related, but definitely I haven't seen anyone embedding a 300 line SP inside an SQL statement!! (Or may be simply I am wrong and people are using such huge queries inline!)

Ofcourse, you run such "SP Query", the only performance hit I can think of would be n/w latency or data transfer to the DB.

-Vivek

Correct. Most people only use ANSI Sql or minimal T-SQL when doing inline queries. Usualy because they don't even know you can! (for example, for paging. All the tutorials say you HAVE to use a stored proc, because you need multiple statements, variables, etc. But you don't! ). In that case, Stored procedures WOULD have -extreme- speed benifit (Can you imagine doing paging in 1 query without the SQL Server 2005 rownum operator? That means at least 3 nested queries! EWWWWW SLOW!). But since you -can- use everything, then there isn't a benifit.

Well, now, according to your last statement, we agree. So there's nothing more to debate over! Damnit.

Let me think of something...hrm...hrm...hrm.....

Lets debate over.... Oh yes! MySQL vs Postgres vs SQL Server vs PHP vs PERL vs Ruby On Rail vs .NET vs Java!! FLAMEWAR!

Just kidding :) Going to bed now, hahaha.