Viewing large amounts of data

We have a data management application writen in Delphi. This app shows
all tables in the database, and display all data in a given table in a
datagrid. We are using an ODBC connection for this. This functionality
is not unlike what is seen in e.g. Enterprise manager.

Now even with 400K of rows, this datagrid is "filled" is mere seconds.
I am aware that the underlying dataset only retrieves a subset of rows,
and dynamically retrieves and unloads rows as needed, thereby giving an
illusion of retrieving the entire table.

We are now moving to .net and I find myself having a great deal of
difficulty getting the same functionality out of ado.net. The ODBC
components we are using in our Delphi app is connected whereas ado.net
is disconnected.

What I basically need is to retrieve say 100 rows at a time, and
dynamically unload and retrieve new rows and filling them into a
datagrid as the user scrolls back and forth through the datagrid.

How do I achieve this?

Please dont lecture about filtering and whatnot. This is the
functionality we need.

--
Thomas Due
Posted with XanaNews version 1.16.5.2

"If you can't convince them, confuse them."
-- Harry S. Truman

Joyjit
Fri Dec 03 03:49:17 CST 2004

Hi,

I think you need an API Server side cursor that will do your work most
efficiently. The Ole DB, ODBC, ADO library API support mapping cursors over
the result sets. You didn't mention which database & provider you are
planning to use in your .NET environment but, follow the link below for an
SQL OLE DB implementation.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7xpv.asp

Let me know if you need anything else.

Thanks
Joyjit

"Thomas Due" <tdue@mail_remove_.dk> wrote in message
news:uyZdanR2EHA.3236@TK2MSFTNGP15.phx.gbl...
> We have a data management application writen in Delphi. This app shows
> all tables in the database, and display all data in a given table in a
> datagrid. We are using an ODBC connection for this. This functionality
> is not unlike what is seen in e.g. Enterprise manager.
>
> Now even with 400K of rows, this datagrid is "filled" is mere seconds.
> I am aware that the underlying dataset only retrieves a subset of rows,
> and dynamically retrieves and unloads rows as needed, thereby giving an
> illusion of retrieving the entire table.
>
> We are now moving to .net and I find myself having a great deal of
> difficulty getting the same functionality out of ado.net. The ODBC
> components we are using in our Delphi app is connected whereas ado.net
> is disconnected.
>
> What I basically need is to retrieve say 100 rows at a time, and
> dynamically unload and retrieve new rows and filling them into a
> datagrid as the user scrolls back and forth through the datagrid.
>
> How do I achieve this?
>
> Please dont lecture about filtering and whatnot. This is the
> functionality we need.
>
> --
> Thomas Due
> Posted with XanaNews version 1.16.5.2
>
> "If you can't convince them, confuse them."
> -- Harry S. Truman

Thomas
Fri Dec 03 04:01:02 CST 2004

Joyjit Mukherjee wrote:

> I think you need an API Server side cursor that will do your work most
> efficiently. The Ole DB, ODBC, ADO library API support mapping
> cursors over the result sets. You didn't mention which database &
> provider you are planning to use in your .NET environment but, follow
> the link below for an SQL OLE DB implementation.

I will definitely be using a MS SQL Server 2000 (or MSDE), and
therefore mostly likely the SqlClient Data Provider.

Thanks for the link, I will look into it.

--
Thomas Due
Posted with XanaNews version 1.16.5.2

"If you can't convince them, confuse them."
-- Harry S. Truman

Sahil
Fri Dec 03 09:50:07 CST 2004

Thomas,

This is a challenge. Luckily you are dealing with only one table at a time,
so it isn't that bad.

In short - out of the box, dataset and datadapter.fill and databind will not
work for you - since out of the box it will not support the sort of paged
behavior you need.

You need to write your own databindable object that mimics a datatable. I
have done something like this before and here is how I did it -

I had an arraylist inside of datatables.
To start with I'd fetch two datatables, from 1-100 rows, and then 101 to 200
rows.
Soon as I'd get a request to return the 101's row, I'd fill up the 201-300
th row datatable. and so on so forth.
If the user were to page suddenly to row #541, I'd fill in datatables
401-500,501-600 and 601-700.

Okay great - now that I had stuff in my class - I would simply mimic
datatables behavior so the user of my class would simply think that it was
indeed dealing with a datatable (like) class. I supported the bare set of
functionalities that I needed out of a datatable.

.. this might not be pertinitent to you .. but then I carried the concept
further and created a class which would read all this data and essentially
page that data from and to a filestream ... essentially act as a paged
datatable .. (I had promised Cor Lightert that I'd write up a sample and
email him, but I never find the time to do so .. but anyway, the above
information is more than enough).

So in your application if you'd code up such a sample, you'd most probably
need a different fill routine; other than that this thing can be made to
look and work so much like a datatable that your datagrid front end will
never find out who it was dealing with :).

HTH

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
http://blogs.apress.com/authors.php?author=Sahil Malik





"Thomas Due" <tdue@mail_remove_.dk> wrote in message
news:uyZdanR2EHA.3236@TK2MSFTNGP15.phx.gbl...
> We have a data management application writen in Delphi. This app shows
> all tables in the database, and display all data in a given table in a
> datagrid. We are using an ODBC connection for this. This functionality
> is not unlike what is seen in e.g. Enterprise manager.
>
> Now even with 400K of rows, this datagrid is "filled" is mere seconds.
> I am aware that the underlying dataset only retrieves a subset of rows,
> and dynamically retrieves and unloads rows as needed, thereby giving an
> illusion of retrieving the entire table.
>
> We are now moving to .net and I find myself having a great deal of
> difficulty getting the same functionality out of ado.net. The ODBC
> components we are using in our Delphi app is connected whereas ado.net
> is disconnected.
>
> What I basically need is to retrieve say 100 rows at a time, and
> dynamically unload and retrieve new rows and filling them into a
> datagrid as the user scrolls back and forth through the datagrid.
>
> How do I achieve this?
>
> Please dont lecture about filtering and whatnot. This is the
> functionality we need.
>
> --
> Thomas Due
> Posted with XanaNews version 1.16.5.2
>
> "If you can't convince them, confuse them."
> -- Harry S. Truman

Thomas
Mon Dec 06 02:18:07 CST 2004

Sahil Malik wrote:

> You need to write your own databindable object that mimics a
> datatable. I have done something like this before and here is how I
> did it -

[SNIP Good Advice]

Ok, this approach is pretty much what I expected was necessary. My
problem is though that I am not well versed enough in T-SQL to figure
out how to do the SQL. I know of the TOP criteria of course, but that
would only be useful for the first 100 rows.

How would I go about getting the next 100?

My problem is that most of our tables have alphanumeric keys, and
although a given is usually numeric only, the column is still
alphanumeric, and as such will behave very differently when adding 100
to the key.

I cant just add 100 to say 3000 and expect to get the right 100 rows,
so to speak. Plus there is a chance that the key will contain
characters. How do you add 100 to e.g. 'F100-A'?

Furthemore, how do I guarantee that I get exactly 100 rows from the
following queries? Of course I can use SET ROWCOUNT 100, but is that
the way to go?

--
Thomas Due
Posted with XanaNews version 1.16.5.2

"To fight and conquer in all your battles is not supreme excellence;
supreme excellence consists in breaking the enemy's resistance without
fighting."
-- Sun-Tzu

Sahil
Mon Dec 06 14:52:58 CST 2004

Thomas,

One way is, you can use various overloads of DataAdapter.Fill to get paged
results back - that would be much better than Select Top 100 etc.

The place where this logic would go would be in the indexor
function/property.

About Alphanumeric keys - there has to be some sort order of the
alphanumeric keys - you have to use that sort order to essentially translate
the alphanumeric keys to numeric digits and pass those to the
dataadapter.fill.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
http://blogs.apress.com/authors.php?author=Sahil Malik




"Thomas Due" <tdue@mail_remove_.dk> wrote in message
news:#pbcew22EHA.3616@TK2MSFTNGP11.phx.gbl...
> Sahil Malik wrote:
>
> > You need to write your own databindable object that mimics a
> > datatable. I have done something like this before and here is how I
> > did it -
>
> [SNIP Good Advice]
>
> Ok, this approach is pretty much what I expected was necessary. My
> problem is though that I am not well versed enough in T-SQL to figure
> out how to do the SQL. I know of the TOP criteria of course, but that
> would only be useful for the first 100 rows.
>
> How would I go about getting the next 100?
>
> My problem is that most of our tables have alphanumeric keys, and
> although a given is usually numeric only, the column is still
> alphanumeric, and as such will behave very differently when adding 100
> to the key.
>
> I cant just add 100 to say 3000 and expect to get the right 100 rows,
> so to speak. Plus there is a chance that the key will contain
> characters. How do you add 100 to e.g. 'F100-A'?
>
> Furthemore, how do I guarantee that I get exactly 100 rows from the
> following queries? Of course I can use SET ROWCOUNT 100, but is that
> the way to go?
>
> --
> Thomas Due
> Posted with XanaNews version 1.16.5.2
>
> "To fight and conquer in all your battles is not supreme excellence;
> supreme excellence consists in breaking the enemy's resistance without
> fighting."
> -- Sun-Tzu

n00b
Fri Dec 10 19:49:02 CST 2004

I have the same problem with the way my company has been making software.
Former management decided it was in the best interest of the company to make
front-end Access apps with SQL Server back-ends. Everything is linked up
ODBC and the users demand table view at times. They can browse through gobs
of data with Datasheet view and still have the rules based interface thats
needed underneath. We have not made the leap to .NET and right now I'm
playing around with converting a simple Access data-entry form and report to
.NET. The first thing that hit me was that I was not ever going to be able
to use the .NET data form wizard and the data binding code it produced. The
table that the Access form wrote to has about 15,000 records in it and the
data adapter (as coded by the wizard) would fill the dataset with all 15,000
records. The form was set up to allow the user to scroll the underlying
table and do data entry. A typical Access type data entry window. So, I
began making my own form control out of a panel control to mimic the
functionality of the Access form without the necessity of using a server side
cursor like ODBC is using. The strategy I used was to only allow the dataset
to be filled with one record at a time. The custom "form" handles what
record is actually next and previous and generates a select statement for the
data adapter. For instance, to find the next record, I use the current value
of the primary key as the sort key for the data. The next record is:

SELECT TOP 1 * FROM [YourTable] WHERE PK_Field > [Current PK Value]

The previous record is:

SELECT TOP 1 * FROM [YourTable] WHERE PK_Field < [Current PK Value]

The last record is:

SELECT * FROM [YourTable] WHERE PK_Field = [Max PK Value]

The first record is:

SELECT * FROM [YourTable] WHERE PK_Field = [Min PK Value]

Using this strategy, I made a custom control that could traverse the entire
table, one record at a time. This works for replicating an Access form, but
not a datasheet. The custom control works very well, but generates a lot of
select statements when a user scrolls through the data. If the scrolling is
throttled (one record retrieved every 10 milliseconds), the server reacts
about the same as it would with a server side cursor via ODBC. From what I
understand, the technique i used is very similar to the amount of resources
SQL Server would use if a cursor was used. Right now I am thinking about
scrapping the technique I used and just using a server side cursor. I found
an EXCELLENT example of using a server side cursor with .NET and replicating
some of the features of a Recordset in this article:

http://www.codeproject.com/vb/net/SimulatingRecordsets.asp

The Code Project site is a wonderful resource for code examples- all with
full source! I am currently in the process of modifying my code to use the
"recordset" class in his sample to see how it performs.


n00b
aka Steve Naumann



"Thomas Due" wrote:

> We have a data management application writen in Delphi. This app shows
> all tables in the database, and display all data in a given table in a
> datagrid. We are using an ODBC connection for this. This functionality
> is not unlike what is seen in e.g. Enterprise manager.
>
> Now even with 400K of rows, this datagrid is "filled" is mere seconds.
> I am aware that the underlying dataset only retrieves a subset of rows,
> and dynamically retrieves and unloads rows as needed, thereby giving an
> illusion of retrieving the entire table.
>
> We are now moving to .net and I find myself having a great deal of
> difficulty getting the same functionality out of ado.net. The ODBC
> components we are using in our Delphi app is connected whereas ado.net
> is disconnected.
>
> What I basically need is to retrieve say 100 rows at a time, and
> dynamically unload and retrieve new rows and filling them into a
> datagrid as the user scrolls back and forth through the datagrid.
>
> How do I achieve this?
>
> Please dont lecture about filtering and whatnot. This is the
> functionality we need.
>
> --
> Thomas Due
> Posted with XanaNews version 1.16.5.2
>
> "If you can't convince them, confuse them."
> -- Harry S. Truman
>

n00b
Sat Dec 11 23:17:23 CST 2004

I have been thinking about the paging grid today when I saw the light.
Consider Google, you type in some criteria and you get paged results,
10 at a time. The user has to click "next" to get the next page of 10.
This is it! One has to replicate this behavior in a datagrid and this
is what I came up with...

1. Create an inherited datagrid that removes the pesky little scroll
bars as so:

Public Class NSBDataGrid
Inherits Windows.Forms.DataGrid

Public Sub New()

Me.HorizScrollBar.Width = 0
Me.HorizScrollBar.Height = 0
Me.VertScrollBar.Width = 0
Me.VertScrollBar.Height = 0

End Sub

End Class

2. Use the data form wizard to generate a data form with a grid. Open
the code window to the form and replace all instances of
System.Windows.Forms.Datagrid with NSBDataGrid (NSB = No Scroll Bars.)


3. In the properties for the OleDbDataAdapter, change the SQL code in
the select command text to start with SELECT TOP 25 instead of just
SELECT.

4. Add two buttons to the form and name them btnUp and btnDown.


5. In my form, I was using a table called tblItemMaster with a Primary
Key of ItemID (ItemID is an identity column.) My dataset was called
objItemMaster. You'll have to replace the primary key in my btnDown
event with your own and replace the objItemMaster with the dataset name
on your form.


This is my btnDown click event code:

Private Sub btnDown_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDown.Click

Dim intMaxID As Integer
Dim strSQL As String
Dim intWherePos As Integer

intMaxID =
objItemMaster.Tables(0).Rows(objItemMaster.Tables(0).Rows.Count - 1)(0)

strSQL = OleDbDataAdapter1.SelectCommand.CommandText

intWherePos = InStr(strSQL, " WHERE")

If intWherePos > 0 Then

strSQL = Mid(strSQL, 1, intWherePos - 1)

End If

strSQL &= " WHERE ItemID > " & intMaxID

OleDbDataAdapter1.SelectCommand.CommandText = strSQL

LoadDataSet()

End Sub


This btnDown event gets the next 25 records and displays them in the
datagrid.


The btnUp is similar, but a bit more tricky.

Private Sub btnUp_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUp.Click

Dim intMinID As Integer
Dim strSQL As String
Dim intWherePos As Integer
Dim daMin As OleDb.OleDbDataAdapter
Dim dsMin As New DataSet
Dim intAnchorID As Integer

intMinID = objItemMaster.Tables(0).Rows(0)(0)

daMin = New OleDb.OleDbDataAdapter("SELECT MIN(T1.ItemID) FROM (SELECT
TOP 25 ItemID FROM tblItemMaster2 WHERE ItemID < " & intMinID & " ORDER
BY ItemID DESC) AS T1", OleDbConnection1)

daMin.Fill(dsMin)

If Not IsDBNull(dsMin.Tables(0).Rows(0)(0)) Then

intAnchorID = dsMin.Tables(0).Rows(0)(0)

strSQL = OleDbDataAdapter1.SelectCommand.CommandText

intWherePos = InStr(strSQL, " WHERE")

If intWherePos > 0 Then

strSQL = Mid(strSQL, 1, intWherePos - 1)

End If

strSQL &= " WHERE ItemID >= " & intAnchorID

OleDbDataAdapter1.SelectCommand.CommandText = strSQL

LoadDataSet()

End If

End Sub


Since you have to sort descending to get the previous 25 records, the
datagrid would be sorted wrong, so I use a subquery to get the previous
25 primary keys, get the min ItemID from the subquery, then retrieve
the 25 records in sorted order using the minimum as the anchor point.

SELECT MIN(T1.ItemID) FROM (SELECT TOP 25 ItemID FROM tblItemMaster
WHERE ItemID < 100 ORDER BY ItemID DESC) AS T1

Tricky, but it works.

Now the user can manually page the data and it won't download the whole
table to the client machine all at one time.

Sahil
Sun Dec 12 11:28:04 CST 2004

Good stuff man !! :)

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


"n00b" <advinn@email.com> wrote in message
news:1102828643.774712.268790@c13g2000cwb.googlegroups.com...
>I have been thinking about the paging grid today when I saw the light.
> Consider Google, you type in some criteria and you get paged results,
> 10 at a time. The user has to click "next" to get the next page of 10.
> This is it! One has to replicate this behavior in a datagrid and this
> is what I came up with...
>
> 1. Create an inherited datagrid that removes the pesky little scroll
> bars as so:
>
> Public Class NSBDataGrid
> Inherits Windows.Forms.DataGrid
>
> Public Sub New()
>
> Me.HorizScrollBar.Width = 0
> Me.HorizScrollBar.Height = 0
> Me.VertScrollBar.Width = 0
> Me.VertScrollBar.Height = 0
>
> End Sub
>
> End Class
>
> 2. Use the data form wizard to generate a data form with a grid. Open
> the code window to the form and replace all instances of
> System.Windows.Forms.Datagrid with NSBDataGrid (NSB = No Scroll Bars.)
>
>
> 3. In the properties for the OleDbDataAdapter, change the SQL code in
> the select command text to start with SELECT TOP 25 instead of just
> SELECT.
>
> 4. Add two buttons to the form and name them btnUp and btnDown.
>
>
> 5. In my form, I was using a table called tblItemMaster with a Primary
> Key of ItemID (ItemID is an identity column.) My dataset was called
> objItemMaster. You'll have to replace the primary key in my btnDown
> event with your own and replace the objItemMaster with the dataset name
> on your form.
>
>
> This is my btnDown click event code:
>
> Private Sub btnDown_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnDown.Click
>
> Dim intMaxID As Integer
> Dim strSQL As String
> Dim intWherePos As Integer
>
> intMaxID =
> objItemMaster.Tables(0).Rows(objItemMaster.Tables(0).Rows.Count - 1)(0)
>
> strSQL = OleDbDataAdapter1.SelectCommand.CommandText
>
> intWherePos = InStr(strSQL, " WHERE")
>
> If intWherePos > 0 Then
>
> strSQL = Mid(strSQL, 1, intWherePos - 1)
>
> End If
>
> strSQL &= " WHERE ItemID > " & intMaxID
>
> OleDbDataAdapter1.SelectCommand.CommandText = strSQL
>
> LoadDataSet()
>
> End Sub
>
>
> This btnDown event gets the next 25 records and displays them in the
> datagrid.
>
>
> The btnUp is similar, but a bit more tricky.
>
> Private Sub btnUp_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnUp.Click
>
> Dim intMinID As Integer
> Dim strSQL As String
> Dim intWherePos As Integer
> Dim daMin As OleDb.OleDbDataAdapter
> Dim dsMin As New DataSet
> Dim intAnchorID As Integer
>
> intMinID = objItemMaster.Tables(0).Rows(0)(0)
>
> daMin = New OleDb.OleDbDataAdapter("SELECT MIN(T1.ItemID) FROM (SELECT
> TOP 25 ItemID FROM tblItemMaster2 WHERE ItemID < " & intMinID & " ORDER
> BY ItemID DESC) AS T1", OleDbConnection1)
>
> daMin.Fill(dsMin)
>
> If Not IsDBNull(dsMin.Tables(0).Rows(0)(0)) Then
>
> intAnchorID = dsMin.Tables(0).Rows(0)(0)
>
> strSQL = OleDbDataAdapter1.SelectCommand.CommandText
>
> intWherePos = InStr(strSQL, " WHERE")
>
> If intWherePos > 0 Then
>
> strSQL = Mid(strSQL, 1, intWherePos - 1)
>
> End If
>
> strSQL &= " WHERE ItemID >= " & intAnchorID
>
> OleDbDataAdapter1.SelectCommand.CommandText = strSQL
>
> LoadDataSet()
>
> End If
>
> End Sub
>
>
> Since you have to sort descending to get the previous 25 records, the
> datagrid would be sorted wrong, so I use a subquery to get the previous
> 25 primary keys, get the min ItemID from the subquery, then retrieve
> the 25 records in sorted order using the minimum as the anchor point.
>
> SELECT MIN(T1.ItemID) FROM (SELECT TOP 25 ItemID FROM tblItemMaster
> WHERE ItemID < 100 ORDER BY ItemID DESC) AS T1
>
> Tricky, but it works.
>
> Now the user can manually page the data and it won't download the whole
> table to the client machine all at one time.
>

Thomas
Mon Dec 13 05:36:49 CST 2004

Hiya,

Thank you all for the answers. The various methods put forth here seems
very promising, so I will look at them when I get the chance.

Thanks for your input and ideas.

--
Thomas Due
Posted with XanaNews version 1.17.0.0

"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche

Thomas
Fri Jan 21 04:30:09 CST 2005

Sahil Malik wrote:

> One way is, you can use various overloads of DataAdapter.Fill to get
> paged results back - that would be much better than Select Top 100
> etc.
>
> The place where this logic would go would be in the indexor
> function/property.

I have now had time to review this idea. Maybe I misunderstand your
idea, but I tested it on a table with 400,000 rows. I paged the fill
with 25 records per page. Like this:

private void LoadData() {
data.Clear();
adapter.Fill(data, _start, 25, "DATATBL");
listView1.Items.Clear();
foreach(DataRow row in data.Tables["DATATBL"].Rows) {
listView1.Items.Add(
new ListViewItem(
new string[]{
row[0].ToString(),
row[1].ToString(),
row[2].ToString()}));
}
}

I use a ListView to represent the data here, but it is the same with a
DataGrid, or even worse.

First of all the app is a very long time starting. (I open the
dataadapter on initialization, that can be moved of course).

Each page switch is takes 10-20 seconds!

With that amount of records, it seems to be that I have to handle it
server side, e.g. with a server side cursor, or by using TOP.

Any ideas?

I should add, that our application does not have that many users at a
time, so server side cursors would hit the server on performance.

--
Thomas Due
Posted with XanaNews version 1.17.1.2

"Out of life's school of war: What does not destroy me, makes me
stronger."
-- Friedrich Nietzsche