People,

I have a strange question. When I write to .NET newsgroups everybody
recommend me to use as simple queries as possible to do something and work
with data directly in memory. They write me - write a class where you will
retrieve data using as simple query as you can and then work with data. But
if we're really having a very powerful SQL language with subqueries, we can
JOIN tables, ORDER, GROUP, etc., why I should not use this powerful language
to retrieve only the data that I need and then display it, save, etc.? Does
it breaks the flexibility of the classes at all? Is it better to add another
one method to the class to get something then to fight with data in our own
code?

I don't think that this is offtopic here, more it's a problem of style. We
have two solutions: 1) we can use a very complicated query to get only
required data or 2) use a simple set of queries and then manipulate with the
data manually with our own code?

Where is the truth?-)

Thanks,
Dmitri

[quoted text, click to view]

If you are getting the data only once, then Method 1 may be better.
However if you have to get the data in different variations many times, then
Method 2 may be better to minimize round trips to the server. This becomes
especially evident and crucial if you work across the internet with narrow
bandwidth and long latency. How many simultaneous clients you have also
matters.
It also depends how much data you are fetching. I would not try to bring 10
million rows down to the client for manipulation.
Having said all this, some of the ado.net people just like to chant the
party line without thinking or knowing.

Hi,

[quoted text, click to view]
Only once to show the data on ASPX page.

[quoted text, click to view]
Many times per minute, second? If this is a WebApp, then the SqlServer
should work at the same machine or on the LAN, all remote users should have
an access through LAN or Internet. My case One server and Internet. So, if I
send a query to the server then it will get only the data that I need,
correct? But if I retrieve the whole bunch of data and should manipulate
with this data on the server side it a worst solution because of RAM,
resources etc. Correct? For example if I use a simple subquery to group some
data using one property, then I get the list of IDs and using this list I
retrieve the real latest data for each group it optimized and will be done
quickly. If I need to join two or more tables and get the resulting DataSet
and then to show it it seems to me reasonable. If I need some statistics I
can get that from SQL Server, etc. And I don't want to retrieve two or more
tables separately just to find required pairs in memory myself and then show
them. It's possible to it will take a very long time, much longer that I use
a correct SQL. And it will also require an additional RAM. 1000 clients and
the server is dead.

[quoted text, click to view]
In this case we can use LIKE '%...%' etc, or just browsing to limit the
stream.

[quoted text, click to view]
Via dialup connection to PDA...))) Funny!-)

[quoted text, click to view]
And this is a huge problem. I came to SQL many years ago from a real-time
data processing on very poor machines, and even now I try to optimize every
line of code, because I used to write in such manner, maybe I'm wrong
thinking on 100 steps ahead...)

But is it really bad to use a strong subquery getting only required data if
the server is remote and all job should be done on this server, then
displayed to the user on ASPX pages? I don't think so. But people say about
flexibility of .NET classes, harder maintanability of subqueries and other
things.

By the way, one of the additional methods is to create a temporary table or
view to retrieve only required data and save it easier. Is it a very popular
method now? To copy a table into a temporary table - it's a very long
process!

Thanks,
Dmitri

[quoted text, click to view]

In the case you are describing it does seem doing the manipulation within
SQL server is better.
Makes little sense to transfer the data to IIS (whether same or different
machine) and then do it there.
A program is a program and there much reason to think SQL server code is
more efficient than IIS ado.net code.

By "get the data in different variations many times", I was thinking of the
following situation (just for example):
1) Need to get all the orders for a given client for the current week - some
small number like 5-20.
2) Need to browse through them with a Windows app.
Better to get the complete data for the week once and manipulate on the
client than send 5-20 queries to SQL to browse through them.

[quoted text, click to view]

When I was working in time-critial signal processing, I did similar, but
only on the repetitive loops that undergo many iterations.
Usually not worth optimizing every "line", but certainly some of them.

[quoted text, click to view]

Many people would say leave the complicated subqueries in SQL stored
procedures (as opposed to client side SQL) and pass in the parameters.
For data manipulation (of the joining, sorting, etc. variety) transact SQL
is certainly clearer and more easy to maintain than ado.net data
obfustication.

After the next version of ado.net comes out, you will start to hear the same
people complaining about how bad the old version was, how much better the
new version is, and why it is the ultimate solution. A substantial portion
of the community gets their education from Microsoft marketing, instead of
professionals or critical thinking.

[quoted text, click to view]

Not sure what you mean by this use of temp tables. I think many people may
disagree with this approach to using SQL server.

[quoted text, click to view]

Dmitri,

Can you provide an example of what you have asked and what alternative
is suggested, ideally where there is a well-specified task to be
solved? Your question is rather vague.

Steve

[quoted text, click to view]