"Seek"ing to data

[David W. Simmonds]

I have a table that has a unique numeric field named ID. It want to build a
list of records that match a criteria. I would rather not store all the
record contents in the list. I would like to just store the ID value in a
list. Then when I need the full record data, I would like to "Seek" to that
record and read the data. Using ADO.NET, how can I get to a particular
record without having to execute a SQL command like "SELECT * FROM tblData
WHERE ID=xxx" where xxx is the ID. Is there a fast seek method of some sort?

 

[Bernie Yaeger]

Hi Dave,

Yes, there are several ways to do this. Since you're referring to a unique
id column, I'll assume it's the primary key of the table in question, in
which case the following should get you started:
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")

Dim ocmd As New SqlCommand("select * from evcodes", oconn)

Dim oda As New SqlDataAdapter(ocmd)

Dim ods As New DataSet("Event Codes")

Try

oconn.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oda.Fill(ods, "Event Codes")

Dim irow As DataRow

Dim colpk(0) As DataColumn

colpk(0) = ods.Tables(0).Columns("eventcd")

ods.Tables(0).PrimaryKey = colpk

irow = ods.Tables(0).Rows.Find("2")

If irow Is Nothing Then

MessageBox.Show("Not found")

Else

MessageBox.Show(irow("descrip"))

End If

If, on the other hand, it's not a primary key column, use a dataview in the
following fashion:

Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")

Dim ocmd As New SqlCommand("select * from histd_", oconn)

Dim oda As New SqlDataAdapter(ocmd)

Dim ods As New DataSet("History Details")

Try

oconn.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oda.Fill(ods, "History Details") ' this name must be same as 6 lines below

Dim vue As New DataView(ods.Tables(0))

vue.Sort = "title"

Dim i As Integer = vue.Find("natural health")

If i = -1 Then

MessageBox.Show("not found")

Else

MessageBox.Show(vue(i)("issuecode"))

End If

HTH,

Bernie Yaeger

 

[David W. Simmonds]

The ID field is a primary key. With the code below, you do a select *. That
selects every field. Then you fill a data adapter with the results. Does
this not fill the entire collection with all records from the select
statement? Or is the actual data only returned when it is accessed?

Also, I am using an Access database, not a SQL Server type. I would assume
that I would use OleDbConnection, OleDbCommand, and OleDbAdapter instead of
the Sql ones, right?

 

[Bernie Yaeger]

Hi David,

Re your first question, yes, it fills the datatable inside the dataset with
all the data immediately (it's all in memory).

Yes, correct, you will use the oledb connection object, oledbcommand, etc.
But the methods are the same and the structure is the same.

HTH,

Bernie

 

[David W. Simmonds]

I was trying to avoid filling the entire dataset with the contents of the
entire record contents. I wanted to create a list with just the ID fields
since it takes way less memory. Then when I need more data, I could just go
get the detailed information using the ID field. Issuing many SQL SELECT
statements would be bad.

Is there a way to accomplish this?

 

[Bernie Yaeger]

Hi David,

I'm not sure that executing many sql selects really matters. Nothing is
retained in memory after it's no longer used and the garbage collector
discards it once it's out of scope.

However, you can use executescalar thus:
Dim gsprecount As String

Dim gsprecountnum As Integer

gsprecount = "select count(*) from gspre"

ocmd = New SqlCommand(gsprecount, oconn)

gsprecountnum = ocmd.ExecuteScalar()

You'll note however that executescalar relies upon a sql select, so you are
back to the same thing.



But let's talk more practically: if you had a arraylist of id's (very easy
to do), you would still have to use it to generate a sql select for that id
or an executescalar as above, so I don't know whether either approach is
superior to the other.



..Net is very creative about memory. I don't know all the details, but I
suspect if writes to .xml to essentially 'page out' when memory resources
are a problem. I have written apps that open 15 - 20 datatables at once,
many of which are pretty large, and I have not experienced a memory problem
or unreasonable processing delays.



HTH,



Bernie

 

[Kevin Yu [MSFT]]

Thanks for Bernie's quick response.

Hi David,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to seek records from an ID
list. If there is any misunderstanding, please feel free to let me know.

I agree with Bernie's idea that if we only get the IDs in memory, we have
to execute a "SQL command like "SELECT * FROM tblData WHERE ID=xxx" each
time we need to seek the record with ID. We have to do this because the
records are not in memory. So we are not able to get the whole record
unless make a trip back to database with a SELECT statement.

As Bernie metioned, .Net is very creative about memory. When your no
reference are pointing to the DataSet, or the references are out of scope,
the memory block containing the DataSet will soon be collected. So if your
database table is not very very big, it is recommended to fill the whole
table to the DataSet. Seeking in the in-memory object will be much more
faster than go back to the database every time.

Does this answer your question? 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."

 

[David W. Simmonds]

What if the amount of records is large? My select will return 1,500 records.
I have a photo database and the thumbnails are presented on the screen 15 at
a time. There are links above the thumbnails to allow the user to move to
the other pages of thumbnails. It seems to perform excellently. I just
thought that there might be a more efficient way to do it.

 

[Bernie Yaeger]

Hi Daivd,

I open - regularly - datatables with 150,000 rows, often more.

HTH,

Bernie

 

[Kevin Yu [MSFT]]

Hi David,

Based on my experience, a table with 1,500 records isn't a large one. Do
you store the photos in the database? Or just store a file link? If the
pictures are not stored in database or only the thumbnails are stored in
database, I think it's better to fill all the records into a DataSet and
manipulate on the it. As it is an in-memory object, we will get better
performance without making a trip back to the data source each time.

Does this answer your question? If anything is unclear, please feel free to
let me know.

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

 

[Cor]

Hi David,

I read this full thread, because it did intrest me, and added it below Kevin
while it is an answer for you.

I think you have made the best solution you can do (except when it is a
webservice).

Cor

 

[Bernie Yaeger]

Hi David,

Last point - the only problem loading a large table - anything greater than,
say, 50,000 rows - is that it takes some time to load into memory. For
example, a table of 150,000 rows, say 20 cols (its width matters also), may
take about 35 - 55 seconds to load (depending upon the state of your
machine, ram, etc). Once in memory, however, it's quite quickly accessed,
especially re a .find method, pk or non pk.

This load time is my only gripe with the disconnected data model, but I
doubt that much can or ever will be done about it. So I perform many, many
tricks to narrow my datatables and to filter the rows where possible.

1500 rows, again, is quite small. It will load in a second or two if the
connection is already open, maybe 6 - 8 seconds otherwise.

HTH,

Bernie Yaeger