I've got an ASP.NET app (VB.NET) that I'm building for our company to use
in-house. Just for background info - the prototype of this program was a
windows-based app, but we want to make it web-based. The program has a web
form that displays line item data for one record at a time with navigation
buttons (like a rolodex) and also summary data for the found set.
The problem I'm having has to do with getting summary data from SQL 2000 to
display in my app. If I pull down the same line item data in a Crystal
Report for my web-based report viewer and do the summaries in the report, it
works fast. When I pull the same data down for my web-form it chokes my app,
so I can't do the summaries in my asp.net app. I decided to pull down the
whole row of data for just the record i'm going to display and use subqueries
in my stored proc to pull down the summaries in SQL. The stored proc is
still real slow in retrieving the data, and the query in the stored proc is
so big that I've had to break it down into multiple results sets (I was
gettting the 'maximum number of tables in a query (256) was exceeded' error).
The things that are making the queries so big is having to provide for 37
parameters in the WHERE part of the SELECT statements and the same 37
parameters in the ORDER BY part of the SELECT statement.
This is the first time I've had to pull down line item detail and summary
data. What is the best way to do this? Is there a data control that handles
data well for doing a user form like this? I like the way the datagrid can
sort the data easily and navigates, but my interface is not like a columnar
report.