Where to query

[jonnod123]

Hi All,

Just a quick question to see what others think is the best preactise...

You have an Windows application that connects to a SQL Server DB. You use a dataset to represent the database locally. Is it better to write queries locally in your dataset, or in SQL Server itself? In other words, do you write and save queries in your Visual Studio project, or in SQL Server and then copy them over as part of the dataset?

Hope that makes sense! I'd be intersted to know what others do.

Thanks,
Jonno

[lolafuertes]

I try to isolate the data manipulation fom the application layer, so I write all the queries in SQL.

Also I mainly use stored procedures for Insert, Update and Delete. This is specially useful when doing complex (multi table related) inserts, updates and deletes thinking in terms of transaction isolation.

Hope his helps

[jonnod123]

Many thanks Lolafuertes.

Do you separate data manipulation from the application layer for logical or performance reasons? My understanding being that if you use a dataset, you will in effect be running any queries locally anyway, even if they are written/saved on the server. I guess this is why you stored procedures instead, so that you can force them to be run on the server. Is this correct?

Thanks again.

[lolafuertes]

If you use a dataset, the data adapter will fill it with the data according to the select.

If the select references a query in the server, the server executes the query and returns the result to the datase.

Instead, if you fill the dataset with all the data from the table, and then apply a view to filter the data, you are wasting memory and bandwith.

Lets do some examples. Imagine you have a nice table of 16GB of data with an structure of a recordId, recordDate, recordAmount and recordQuantity. Then you need to retireve only the rows having the highest quantity (a priori you don't know wich one is/are)

If you fill a datatable with 16Gb of data ... well you know what will happen to your application.

Instead, you can fill the table using 2 techniques to obtain the desired result:
+ Create a query in the server that always returns the rows having the highest quantity [SELECT * FROM MyLargeTable WHERE recordQuantity = MAX(recordQauntity)] and use a SELECT * FROM MyServerQuery for the dataadapter to fill your datatable or
+ Create an adhoc query like SELECT * FROM MyLargeTable WHERE recordQuantity = MAX(recordQauntity) for the data adapter.

While the first solution is partially known by the server, already compiled, permissions tested and the execution plan is loaded the first time you execute it when you send the adhoc query for SELECT * FROm MyServerQuery, the second one will send the adhoc query to the server, it will analyze sintactically, analyze the permissions, create an execution plan an execute it, each time you call it.

So, in general, having the queries defined at the server, and also the stored procedures for insert, update and delete, will result in a best performance, and will reduce the traffic between the client and the server.

Yes, a long rant. Sorry

Hope this helps

[jonnod123]

Many thanks lolafuertes. Your long rant was very usesful, no need to apologise!

Reading up on it, I think that there is a debate about which is better - stored procedures or queries in the DB. But, what does seem clear is that it's better to separate the application layer from the data layer as much as possible, i.e. query data in the database not the application.

Thanks again,
Jonno

[Venjense]

Answer this in two statements, create a business access layer and a data access layer. In true OO fashion, your UI does not need to know how it gets the data and your data accessors just know how to get the data and not use them.

The real question is, can you reasonably guarantee a data connection on the network? If the answer to this is yes, then using stored procs/web services to access and update the data is the way to go.

If the answer is no, then you have to copy the data local, rely on something to track changes and apply those changes (with stored procs/web services) when the connection becomes available.

Always try to use a DAL so that you can separate out the data connectivity from the real work of the application. .Net Application Architecture : The Data Access Layer is a good start.

[jonnod123]

Thanks Venjense,

The DAL article you recommended is really useful too.