Database - SPEED

[Keith]

Hello everyone. With the enormously helpful developers
out there I was able to customize the code below (most of
which was supplied TO me (Thank you)). However, what I'm
finding is that there is a speed delay problem - from when
the button is clicked and the results are displayed.
While I could create a 'progress bar' control - to fill
the time - while the delay is happening - I would like to
find out the source of the delay. Given it is only 5-6
seconds - but as I place more data in the database - I
believe that delay will increase. I did run the SELECT
statement from directly on the Query Analyzer on the PPC
using the same small database (yyg) - and it took no time
at all (Results: 14 row(s) affect - Elapsed time
00:00:00). My conclusion is this delay is coming about
from the Datareader - the While loop - yet I'm not sure.
And that is really the question: Based on the code below -
where do you think the delay is coming from?
Btw, it seems that once the code under this button is run
the 1st time - all subsequent times are much faster.

Here's the code. Thank you all:

Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click

Dim cn As System.Data.SqlServerCe.SqlCeConnection
Dim cmd As System.Data.SqlServerCe.SqlCeCommand
Dim dtr As System.Data.SqlServerCe.SqlCeDataReader

Try
' Open the database.

Dim s As String
cn = New
System.Data.SqlServerCe.SqlCeConnection("DataSource=My
Documents\yyg.sdf")

cn.Open()

' Retrieve a list of the contacts.

cmd = New System.Data.SqlServerCe.SqlCeCommand
("SELECT First, Last FROM Two", cn)

dtr = cmd.ExecuteReader()

' Load the list into the contacts combobox.
ListBox1.Items.Clear()
TextBox2.Text = Mid(s, 1, Len(TextBox1.Text))
While dtr.Read()

s = dtr("First") '& " " & dtr("Last")
'or dtr(0) & " " & dtr(1)

If Mid(s, 1, Len(TextBox1.Text)) =
TextBox1.Text Then
ListBox1.Items.Add(s)
Else
Dim norecord As String = "Not Records
Exit"
ListBox1.Items.Add(norecord)
End If
End While

' Clean-up.
Catch sqlex As SqlCeException
Dim sqlerror As SqlCeError
For Each sqlerror In sqlex.Errors
MessageBox.Show(sqlerror.Message)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
dtr.Close()

cn.Close()
End Try

End Sub

 

[William Ryan eMVP]

You are going to have to test it, but I'd first check the connection.Open().
If that's the source there's not a whole lot you can do about it, but in my
experience, that's taken longer than the query execution. Just comment out
the datareader code and test the time it takes. compare that to the entire
time. Then run a test just on the dtr.Read.

A few other observations. It doesn't look like you have Option Strict On.
IMHO, There shouldn't even be an option to turn it off or at least it should
be on by default. As Dan Appleman points out, Option Strict Off = Option
Slow On.

since you are in a resource constrained environment, fix this if you can.
Another issue is using the nominal lookups.
at each pass, if you use dtr.GetString("FieldName"), Fieldname has to be
resolved. However, you know it before you are in the loop and it's like
calling a function each time to get information you already know. This will
definittely slow you down. I know indexes reduce readability, but you cna
use an enum for instance, and put the enum value in there, this way you get
the best of both worlds. Off the top of my head I can't remember if
GetOrdinal is supported on the CF but you can call this if it is if you
don't want to use the Enum. Bill Vaughn came up with the enum method and it
saves the overhead of GetOrdinal if resources are at a premium.

If nothing else Option Strict On and the index based lookups should speed
things up a bit.. I'm guessing it's opening the connection that's the slow
part but test the proc in isolation so you can narrow it down.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

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

 

[Boris Nienke]

Here's the code. Thank you all:
[...]
' Load the list into the contacts combobox.
ListBox1.Items.Clear()
TextBox2.Text = Mid(s, 1, Len(TextBox1.Text))

Do somthing like: ListBox1.BeginUpdate();

While dtr.Read()

s = dtr("First") '& " " & dtr("Last")
'or dtr(0) & " " & dtr(1)

If Mid(s, 1, Len(TextBox1.Text)) = TextBox1.Text Then
ListBox1.Items.Add(s)
Else
Dim norecord As String = "Not Records Exit"
ListBox1.Items.Add(norecord)
End If
End While

Do somthing like: ListBox1.EndUpdate();

Maybe it's ListBox1.Items.BeginUpdate(); Not sure

Boris

 

[Boris Nienke]

....oh, and don't forget to use indexes where you can! It may be, that the
Select is really quick but then, when try to read the result set, it takes
time to find the next record because there's no index

Boris

 

[Earl]

In addition to other suggestions, try setting your listbox to invisible
before you start the load and then back to visible after the load. This will
stop the listbox from repainting after every item is added. Repainting is
one of the biggest time wasters.