Optimizing for speed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Due to time constraint, my last project (first one in VB.NET for us, I might
add) was done 'quickly' rather that right, meaning that it is 'good enough'
for the client but not the kind of project one feels proud of after delivery.

I happen to have some spare time on my hands which I plan to spend
optimizing the whole thing just to improve myself.

One of the things I know we didn't do right, simple as it is, is the display
of SQL.CE based data on grids. We just execute a query, grab all records, put
them in rows and display the grid - slow as hell as you can easily guess.

Given you have the data in one or several tables, what is the fastest way to
display it on a grid? Fastest as in 'fastest availabity to the user', so
retrieving records as needed by user interaction is fine. This is actually my
first idea.

Also, suppose you need one of the fields from the query not for display but
for something else (i.e. you can't just feed the results to the datagrid,
even if it's possible to do so, which I'm not sure).

All suggestions welcome, I have the time to try them all.
 
Carlos:

There's a lot of 'ifs' here that could be affecting things. The first thing
I'd consider is how much data am I loading in the grid vs. what the user
needs. If you're pulling out 1000 records but the user really only uses 10
most of the time, then query size would definitely be something to look at.
Another thing is how many trips to the db are you making? You have chioce 1
of basically grabbing all the data and then storing the dataset as static
property for instance which will front load processing time but save time
over the long haul. In general, the as you need it approach is my leaning.

As far as grids, you can't bind them to datareaders but if I just needed a
value or two, I'd definitely look to using one where possible. Indexes are
another thing. If you're really trying to squeeze out everythign that you
can get, set the BeginLoadData method of any given datatable before you
start loading the data
http://msdn.microsoft.com/library/d...f/html/frlrfsystemdatadatatableclasstopic.asp
and then call EndLoadData
http://msdn.microsoft.com/library/d...f/html/frlrfsystemdatadatatableclasstopic.asp
..I'd also shut of the EnforceConstraints property of the dataset if you have
them
http://msdn.microsoft.com/library/d...ref/html/frlrfSystemDataDataSetClassTopic.asp -
but don't leave them off of course, just set it back when you're done with
the load. Lots less events will get fired if you do this - but I can't
promise a huge performance increase because I don't know where the
bottleneck is on your app. In some instances the diffferences are stunning,
in others where this isn't the bottleneck the differences aren't worth
mentioning.

Those are the first thigns that come to mind, but if you could elaborate on
what you mean by " put
them in rows and display the grid " that may provide some insight into
where the bottleneck is. At least the nice part of CE is that you don't
have to worry about network congestion being the bottleneck ;-)
 
Ryan,

Thanks for your quick reply. I'm going to read through your links carefully,
but in the mean time, this is the typical situation in the application:
Simple query (such as SELECT ID, CODE, STOCK FROM PRODUCTS) which returns
1100 records or so. The grid (which has room for like 10-11 rows) displays ID
and CODE, while I use STOCK to display a msgbox if the user clicks on a
sold-out product.

The query itself is fast, it's actually the loop that reads all the records
and takes the time. I'd like to inmediately display records and allow user
interaction. My idea is to fetch the minimum number of records, display them,
and load the rest as the user moves through the grid with the scrollbar but
maybe there's another way.

Thanks.
 
What loop are you referencing? Are you using a DataReader and adding
everything through it in the while dataReader.Read()? Otherwise if you have
a DataTable, I'd just bind directly to it , or to a DataView and just
manimpulate the RowFilter without changing the bindings.
 
Here's some some code (don't worry about variable names inconsistencies, I
just cut some parts of it so you don't have to see all the fields, etc).

myDataSet = New DataSet("ds_itinerarios")
tItin = New DataTable("itinerarios")
Dim cCodCliente As New DataColumn("CODE")
Dim cNomCliente As New DataColumn("NAME")
tItin.Columns.Add(cCodCliente)
tItin.Columns.Add(cNomCliente)
myDataSet.Tables.Add(tItin)
Dim MyCursor As SqlServerCe.SqlCeDataReader
MyCursor = dbc.createCursor("SELECT CODE,NAME FROM PRODUCTS")
While MyCursor.Read()
Dim fila As DataRow
fila = tItin.NewRow
Dim codigo As String
Dim nombre As String
codigo = MyCursor("CODE")
nombre = MyCursor("NAME")
fila("CODIGO") = codigo
fila("NOMBRE") = nombre
tItin.Rows.Add(fila)
End While ' End of cursor read
DataGrid1.RowHeadersVisible = False
DataGrid1.DataSource = tItin
 
Carlos Fernandez said:
Here's some some code (don't worry about variable names inconsistencies, I
just cut some parts of it so you don't have to see all the fields, etc).

myDataSet = New DataSet("ds_itinerarios")
tItin = New DataTable("itinerarios")
Dim cCodCliente As New DataColumn("CODE")
Dim cNomCliente As New DataColumn("NAME")
tItin.Columns.Add(cCodCliente)
tItin.Columns.Add(cNomCliente)
myDataSet.Tables.Add(tItin)
Dim MyCursor As SqlServerCe.SqlCeDataReader
MyCursor = dbc.createCursor("SELECT CODE,NAME FROM PRODUCTS")
While MyCursor.Read()
Dim fila As DataRow
fila = tItin.NewRow
Dim codigo As String
Dim nombre As String
codigo = MyCursor("CODE")
nombre = MyCursor("NAME")
fila("CODIGO") = codigo
fila("NOMBRE") = nombre
tItin.Rows.Add(fila)
End While ' End of cursor read
DataGrid1.RowHeadersVisible = False
DataGrid1.DataSource = tItin
Click to expand...
You may also take a look at DataAdapter ( namely SqlCeDataAdapter (c#
naming convention ) ). I heard that it is the fastest way to fill
DataTable and use
it extensively in my apps.
 
Carlos Fernandez,
optimizing for speed is one of my most important concern too.
I noticed that you are filling the datatable with a datareader, i
believe that it will me much simpler and perhaps faster to use the
dataAdapter.Fill method.
If you want to use the a datareader, use the listview, and while reading
from the datareader you can add items and subitems.
If you are using the same datatable with the same data often, then keep
the datatable and use it instead of querying from the database, that can
reduce time.

and another thing, instead of closing a form that u r going to open
later, hide it and save a reference to it, then use the reference to
show it again, this will save the time for loading the form and the
controls.

you mentioned the following :
suppose you need one of the fields from the query not for display but
for something else .

you specify all the fields that you want in the select query, then you
use the tableStyle to specify only the fields you want to display in the
datagrid. here's an example:
dg is the datagrid

Dim ts1 As New DataGridTableStyle
ts1.MappingName = "datatable name"

Dim col As New DataGridTextBoxColumn
col.MappingName = "DataColumnName"
col.HeaderText = "Column Header in the datagrid"
col.NullText = ""
col.Width = number
ts1.GridColumnStyles.Add(col)

Me.dg.TableStyles.Add(ts1)

for each field you want to be displayed, you create a
DataGridTextBoxColumn and add it to the GridColumnStyles collection,
then u add he whole to the TableStyles collection
 
DataReader is faster than DataAdaper if you populating the DataSet.
In fact, DataAdapter uses DataReader to load data. DataAdaper.Fill() spends
about 70-80% in DataReader.
Thus, switching from easy to use DataAdaper to more complex and less
flexible DataReader won't give you that much.

Populating ListView control from DataReader instead of using DataGrid bound
to a DataSet/DataTable is a very bad idea from performance point of view.
Expect it to be way slower compared to DataSet populated from DataAdapter
and bound to a DataGrid.
Difference quickly increases with number of rows loaded. It's about 5 times
for couple hundred records.

For more details please see this (you might have to combine URL from two or
more lines):
http://groups.google.com/groups?hl=en&lr=&selm=cfSAqAZIEHA.4060@cpmsftngxa
06.phx.gbl&rnum=7

Best regards,

Ilya

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

--------------------
 
Back
Top