avoiding temp table creation

  • Thread starter Thread starter Corey Wirun
  • Start date Start date
C

Corey Wirun

Hi All,

I'm migrating a legacy, reporting-type application (ODBC) to use ADO.NET in
order to facilaite ASP.NET delivery. The problem I have is that some of the
reports this old app runs relies heavily on creating temporary tables to
hold intermediate results of complex select expressions. The temporary
tables are then joined in order to get the final data set, and the temp
tables are deleted when done.

Needless to say, performance wasn't really stellar.

Now that I'm re-wrinting the app, is there any advice people could give me
to leverage ADO.NET technology to improve performance? Can I somehow get
rid of the temp table creation and bring these intermediate query results
into DataAdapters/DataSets AND be able to join them?

Another constraint is that I have to support a variety of db's (SQLServer,
Oracle, Jet), so I can't rely on the features of any specific provider.

Thanks for any advice.
Corey.
 
You can use DataRelations to link DataTables together and these datatables
can come from totally disparate data. However, I don't think this is going
to yield dramatically increased performance but it may - I say it may
because lets say that you have a Orders table with 5 fields one of which is
order_code. Lets just say that you have 5000 records and 10 distinct
order_code s. Now Order code is linked to an Order_Description table and
that table only have two fields, order_code and orderid_description. Let's
say this description was really long. If you joined them in a temp table,
you'd have a ton of redundant data. However, if you used a DataRelation,
you'd have 5,000 records with 5 fields, and 10 records with 2 fields,
instead of 5000 with 6 fields. If the joined fields are big, the difference
may be notable. However, loading datatables locally isn't designed to be an
answer to speed issues. It may be depending on circumstances, but if you are
dealing with queries large enough to be adversely affected by using temp
tables then I'd think that you are way over what you want to try with
datatables (typically 5,000 records is the beginning of where you want to
start getting a little more conservative, and over 10k even moreso.

Depending on how you are building these reports, I'm guessing you aren't
using crystal, you can possibly use a datareader to two to build these
queries and that will give you better performance than data tables will.

HTH,i

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
Hi William,

But doesn't a DataRelation just give you an association between two
identified columns in two DataTables? If I have two DataTables in a DataSet
(that correspond to my legacy, physical, temp tables), and I need to perform
a SELECT between them, the column list and the WHERE clause give me what I
need - which a DataRelation can't help me, or does it?

Thanks for your reply!
Corey.
 
Hi Corey:

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Corey Wirun said:
Hi William,

But doesn't a DataRelation just give you an association between two
identified columns in two DataTables? If I have two DataTables in a DataSet
(that correspond to my legacy, physical, temp tables), and I need to perform
a SELECT between them, the column list and the WHERE clause give me what I
need - which a DataRelation can't help me, or does it?
Click to expand...
I'm not entirely sure I understand your question but I'll take a stab at
what I think you're asking. By defining a Datarelation, you can select
against either table and you can access values in the parent from the child
or vice versa. There are other ways too, using a dataview for instance but
this tends to be the most straightforward.

If this isn't what you are asking, could you just show two tables with one
commmon field, we don't need but two or three other columns and what you'd
need to show?

Thanks,

Bill
 
Sure:

DataTable1 has col1, col2, col3,...col20
DataTable2 has colA, colB, colC,...colX

Say these DataTables correspond to what used to be the temp tables the
legacy reporter generated in the database.

Now, I need to to something like this:

select col1, colB, colD, col9, col10 from datatable1, datatable2 where col6
= colL, and colB = 1, and ....

See what I mean? Once these tables are in a DataSet, I need to execute a
complex select on both of them to get the data I need. And writing the
select in C# would be a huge waste of time.

C.
 
Hi Corey,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to do a complex JOIN and
filter on the data which have been filled to a DataSet. If there is any
misunderstanding, please feel free to let me know.

As far as I know, we cannot do such a complex join in a DataSet object.
However, if the DataTables have been associated by DataRelation objects, we
can get parent or child row values by calling GetParentRow or GetChildRows
on a DataRow object. Just as Bill mentioned, whether the performance
increases depends on how the data in the joint table are accessed. For
example, if they are being read forward-only, I think join data on the
server and use a DataReader is a good choice.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top