Re: Data Limitations
From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 05/18/04
- Next message: David: "Re: xml data vs. ODBC text driver"
- Previous message: Gabe: "Typed DataSet and XmlDataDocument in VS/VB .NET 2003"
- In reply to: Jenna Simmer: "Data Limitations"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 18 May 2004 16:38:39 -0400
Hi Jenna:
"Jenna Simmer" <anonymous@discussions.microsoft.com> wrote in message
news:17FE26E2-0EB7-4E98-9711-AD0449ACF0B1@microsoft.com...
> Hi. My database layer is getting a major upgrade due to .Net capabilities.
> Before, I had a lot of decision making and I was doing some heavy
calculations/parsing in PLSQL procedures. For example, a customer entity
would have data in a general information table, an address table, a delivery
table and a notification table (requirements). So, to retrieve a member
information page, I had to write 4 select statements and return some of the
fields in these tables; I also had a lot of conditional logic in there (eg
if general_information.status = -5 then do not perform notification table
select).
--IMHO this is a good problem to have. A while back I had to write a Web
App for HIPAA reporting and everyone needed the same data shown a different
way. Since security was critical, I had to use procs (which I would have
done anyway) and dynamically determine what people could see on the fly. I
also needed to resuse this logic for a client side desktop app. So I had
two implementations of essentially the same stuff. In the one instance, as
soon as I authenticated the user, I hit about 12 different tables and pulled
back all of thier view/permission information and stored it in a business
object which was stored in session state. Internally we used Windows
Authentication so I could trust their identities. However, one of the
features of the desktop piece was that supervisors and mgt could 'switch'
where they were and the app was supposed to behave just like they were that
actual user. To accomodate this, I pulled all of the config into locally.
There were a lot of tables but never more than 35-50 records so this wasn't
a big deal. Another thing I ran into was that due to the VPN that we had
with one of the clients, we lost all network access when connected in, so
making another trip to the db wasn't an option. By Storing all of the logic
up front, I had a slight lag in load time the first time the user started a
session. On the web this was about the same time b/c the difference between
pulling one small record and 50 small records over is trivial unless you are
really resource constrained. Once you grab all of the data, you hold it
as a Static property and you can access it all over the app without making a
trip back to the db. By providing the names of all of the stored
procedures the user could execute, we could field calls and make
modifications to what the user would see while we were on the phone with
them. A simple refresh and the whole view they were looking at could be
changed to accomodate their needs. Essentially, it was a situation where
you were using SQL statements to call SQL Statements and it was flexible
beyond belief. The other super benefit was that you could change logic and
fix many errors without changing a thing on the client side, but you still
had the power and feature rich environment of the desktop. To a large
degree you can get their with Smart client apps, but this methodology can be
used in conjunction with that framework.
Now, typically you'll want to model your db schema locally but that may or
may not be the case. Since you are using Oracle which supports batch
queries, you can send them all at once with one DataAdatper.Fill - if you
had a four piece query it would return four datatables. You know the order
of the tables so you can add datarelations if appropriate and you can do
pretty much whatever you want. Moreover, by batching them together, you
minimze trips to the db which can be quite important in many scenarios
You can do a Tremendous amount of things with
ADO.NET/DataSet/DataView/Datatables but the key to remember is that you
don't want to get crazy with query sizes. If you want to stick 200,000
records in a middle tier object, I'd advise against it. I'd also advise
against pulling 200,000 records over in one sitting in almost any situation.
The point is that as long as you keep your queries judicious, I doubt you'll
see any performance problems. Ideally back end logic has the best
performance and security stories, but those too can be melded together here.
As far as 'is it better'... like everything that depends but as long as you
keep record sizes in sane bounds, you can do qtuie a bit and ADO.NET's
filtering abilities are kick a33. In doing so, you might take a little more
of a hit b/c you are pulling over a bit more data, but you make it up later
by not having to head over to the db all the time, keeping stress of the
network and keeping stress of the db.
HTH,
Bill
www.devbuzz.com
www.knowdotnet.com
>
> After reading the MS docs I thought about having 4 select statements in a
PLSQL stored procedure as ref cursors, pass them back to the business-logic
layer and parse and apply the conditional logic. Is this better from a
performance point of view than having select statements in the logic layer?
Even so, I don't know how well the business-logic layer will work with so
much data (Oracle was great before); I am afraid it will be slow.
>
> Thanks
> JS
>
- Next message: David: "Re: xml data vs. ODBC text driver"
- Previous message: Gabe: "Typed DataSet and XmlDataDocument in VS/VB .NET 2003"
- In reply to: Jenna Simmer: "Data Limitations"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
