speed-up question

  • Thread starter Thread starter Jon Brunson
  • Start date Start date
J

Jon Brunson

Morning all,

I just want some advice really. I've got the following VB.NET code, and
it seems rather slow, and I was wondering if anyone could see a/some
way/s of speeding it up a little/lot.

Thanks in advance


[VB.NET]
' Me.barCurrent is a ProgressBar

Dim sqlLocal As New SqlCeConnection("Data Source='\Program
Files\TestApp\Data.sdf'")
sqlLocal.Open()

Dim sLastUpdateTimes As New Text.StringBuilder
Dim cmdLocalTableCount As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLocalTables As New SqlCeCommand("SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLastUpdateExists As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME =
'LastUpdate'", sqlLocal)
Dim cmdLastUpdatedTime As New SqlCeCommand("", sqlLocal)
cmdLastUpdateExists.Parameters.Add("@TableName", SqlDbType.NVarChar)

Me.barCurrent.Value = 0
Dim iTable As Integer = 0, iTableCount As Integer =
CInt(cmdLocalTableCount.ExecuteScalar())
Dim drLocalTables As SqlCeDataReader = cmdLocalTables.ExecuteReader()
While drLocalTables.Read()
Dim sTableName As String = drLocalTables.GetString(0)
cmdLastUpdateExists.Parameters(0).Value = sTableName

If CInt(cmdLastUpdateExists.ExecuteScalar()) = 1 Then
cmdLastUpdatedTime.CommandText = "SELECT MAX(LastUpdate) FROM """ +
sTableName + """"
Dim LastUpdate As Object = cmdLastUpdatedTime.ExecuteScalar()

If Not (LastUpdate Is DBNull.Value) Then
If sLastUpdateTimes.Length > 0 Then _
sLastUpdateTimes.Append(",")
sLastUpdateTimes.Append(sTableName + "=")
sLastUpdateTimes.Append(CDate(LastUpdate).ToString("yyyy-MM-dd
HH\:mm\:ss.fff"))
End If

End If

iTable += 1
Me.barCurrent.Value = (iTable / iTableCount) * 100

End While

drLocalTables.Close()
sqlLocal.Close()

[/VB.NET]
 
Sorry I don't have time to measure your function and optimise it. Use the
Stopwatch to see which areas are slow. I am assuming you determined that
this function is the bottleneck for your application, right?

Anyway, reason for replying is that you have a classic oversight in your
piece of code:
sLastUpdateTimes.Append(sTableName + "=")
which now that I point it out to you, I am sure you realise should be
sLastUpdateTimes.Append(sTableName).Append("=")

:)

Cheers
Daniel
 
Thanks for replying Daniel, and for pointing out my oversight (duh me :o))

What is this Stopwatch you speak of?

Daniel said:
Sorry I don't have time to measure your function and optimise it. Use the
Stopwatch to see which areas are slow. I am assuming you determined that
this function is the bottleneck for your application, right?

Anyway, reason for replying is that you have a classic oversight in your
piece of code:
sLastUpdateTimes.Append(sTableName + "=")
which now that I point it out to you, I am sure you realise should be
sLastUpdateTimes.Append(sTableName).Append("=")

:)

Cheers
Daniel
--
http://www.danielmoth.com/Blog/

Jon Brunson said:
Morning all,

I just want some advice really. I've got the following VB.NET code, and it
seems rather slow, and I was wondering if anyone could see a/some way/s of
speeding it up a little/lot.

Thanks in advance


[VB.NET]
' Me.barCurrent is a ProgressBar

Dim sqlLocal As New SqlCeConnection("Data Source='\Program
Files\TestApp\Data.sdf'")
sqlLocal.Open()

Dim sLastUpdateTimes As New Text.StringBuilder
Dim cmdLocalTableCount As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLocalTables As New SqlCeCommand("SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLastUpdateExists As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME =
'LastUpdate'", sqlLocal)
Dim cmdLastUpdatedTime As New SqlCeCommand("", sqlLocal)
cmdLastUpdateExists.Parameters.Add("@TableName", SqlDbType.NVarChar)

Me.barCurrent.Value = 0
Dim iTable As Integer = 0, iTableCount As Integer =
CInt(cmdLocalTableCount.ExecuteScalar())
Dim drLocalTables As SqlCeDataReader = cmdLocalTables.ExecuteReader()
While drLocalTables.Read()
Dim sTableName As String = drLocalTables.GetString(0)
cmdLastUpdateExists.Parameters(0).Value = sTableName

If CInt(cmdLastUpdateExists.ExecuteScalar()) = 1 Then
cmdLastUpdatedTime.CommandText = "SELECT MAX(LastUpdate) FROM """ +
sTableName + """"
Dim LastUpdate As Object = cmdLastUpdatedTime.ExecuteScalar()

If Not (LastUpdate Is DBNull.Value) Then
If sLastUpdateTimes.Length > 0 Then _
sLastUpdateTimes.Append(",")
sLastUpdateTimes.Append(sTableName + "=")
sLastUpdateTimes.Append(CDate(LastUpdate).ToString("yyyy-MM-dd
HH\:mm\:ss.fff"))
End If

End If

iTable += 1
Me.barCurrent.Value = (iTable / iTableCount) * 100

End While

drLocalTables.Close()
sqlLocal.Close()

[/VB.NET]
Click to expand...
Click to expand...
 
What is this Stopwatch you speak of?

It's a class. You can get the Stopwatch though the SDF from OpenNETCF.

It originated here:
http://www.danielmoth.com/Blog/2004/12/stopwatch.html

Cheers
Daniel
--
http://www.danielmoth.com/Blog/

Jon Brunson said:
Thanks for replying Daniel, and for pointing out my oversight (duh me :o))

What is this Stopwatch you speak of?

Daniel said:
Sorry I don't have time to measure your function and optimise it. Use the
Stopwatch to see which areas are slow. I am assuming you determined that
this function is the bottleneck for your application, right?

Anyway, reason for replying is that you have a classic oversight in your
piece of code:
sLastUpdateTimes.Append(sTableName + "=")
which now that I point it out to you, I am sure you realise should be
sLastUpdateTimes.Append(sTableName).Append("=")

:)

Cheers
Daniel
--
http://www.danielmoth.com/Blog/

"Jon Brunson" <jon.brunson@innovation-NOSPAM-software-DOT-co-DOT-uk>
wrote
in message news:[email protected]...
Morning all,

I just want some advice really. I've got the following VB.NET code, and
it
seems rather slow, and I was wondering if anyone could see a/some way/s
of
speeding it up a little/lot.

Thanks in advance


[VB.NET]
' Me.barCurrent is a ProgressBar

Dim sqlLocal As New SqlCeConnection("Data Source='\Program
Files\TestApp\Data.sdf'")
sqlLocal.Open()

Dim sLastUpdateTimes As New Text.StringBuilder
Dim cmdLocalTableCount As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLocalTables As New SqlCeCommand("SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLastUpdateExists As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME =
'LastUpdate'", sqlLocal)
Dim cmdLastUpdatedTime As New SqlCeCommand("", sqlLocal)
cmdLastUpdateExists.Parameters.Add("@TableName", SqlDbType.NVarChar)

Me.barCurrent.Value = 0
Dim iTable As Integer = 0, iTableCount As Integer =
CInt(cmdLocalTableCount.ExecuteScalar())
Dim drLocalTables As SqlCeDataReader = cmdLocalTables.ExecuteReader()
While drLocalTables.Read()
Dim sTableName As String = drLocalTables.GetString(0)
cmdLastUpdateExists.Parameters(0).Value = sTableName

If CInt(cmdLastUpdateExists.ExecuteScalar()) = 1 Then
cmdLastUpdatedTime.CommandText = "SELECT MAX(LastUpdate) FROM """ +
sTableName + """"
Dim LastUpdate As Object = cmdLastUpdatedTime.ExecuteScalar()

If Not (LastUpdate Is DBNull.Value) Then
If sLastUpdateTimes.Length > 0 Then _
sLastUpdateTimes.Append(",")
sLastUpdateTimes.Append(sTableName + "=")
sLastUpdateTimes.Append(CDate(LastUpdate).ToString("yyyy-MM-dd
HH\:mm\:ss.fff"))
End If

End If

iTable += 1
Me.barCurrent.Value = (iTable / iTableCount) * 100

End While

drLocalTables.Close()
sqlLocal.Close()

[/VB.NET]
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Daniel, I will use this to investigate the choke-points of
my code!

Daniel said:
What is this Stopwatch you speak of?
Click to expand...

It's a class. You can get the Stopwatch though the SDF from OpenNETCF.

It originated here:
http://www.danielmoth.com/Blog/2004/12/stopwatch.html

Cheers
Daniel
--
http://www.danielmoth.com/Blog/

Jon Brunson said:
Thanks for replying Daniel, and for pointing out my oversight (duh me :o))

What is this Stopwatch you speak of?

Daniel said:
Sorry I don't have time to measure your function and optimise it. Use the
Stopwatch to see which areas are slow. I am assuming you determined that
this function is the bottleneck for your application, right?

Anyway, reason for replying is that you have a classic oversight in your
piece of code:
sLastUpdateTimes.Append(sTableName + "=")
which now that I point it out to you, I am sure you realise should be
sLastUpdateTimes.Append(sTableName).Append("=")

:)

Cheers
Daniel
--
http://www.danielmoth.com/Blog/

"Jon Brunson" <jon.brunson@innovation-NOSPAM-software-DOT-co-DOT-uk>
wrote
in message Morning all,

I just want some advice really. I've got the following VB.NET code, and
it
seems rather slow, and I was wondering if anyone could see a/some way/s
of
speeding it up a little/lot.

Thanks in advance


[VB.NET]
' Me.barCurrent is a ProgressBar

Dim sqlLocal As New SqlCeConnection("Data Source='\Program
Files\TestApp\Data.sdf'")
sqlLocal.Open()

Dim sLastUpdateTimes As New Text.StringBuilder
Dim cmdLocalTableCount As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLocalTables As New SqlCeCommand("SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", sqlLocal)
Dim cmdLastUpdateExists As New SqlCeCommand("SELECT COUNT(*) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME =
'LastUpdate'", sqlLocal)
Dim cmdLastUpdatedTime As New SqlCeCommand("", sqlLocal)
cmdLastUpdateExists.Parameters.Add("@TableName", SqlDbType.NVarChar)

Me.barCurrent.Value = 0
Dim iTable As Integer = 0, iTableCount As Integer =
CInt(cmdLocalTableCount.ExecuteScalar())
Dim drLocalTables As SqlCeDataReader = cmdLocalTables.ExecuteReader()
While drLocalTables.Read()
Dim sTableName As String = drLocalTables.GetString(0)
cmdLastUpdateExists.Parameters(0).Value = sTableName

If CInt(cmdLastUpdateExists.ExecuteScalar()) = 1 Then
cmdLastUpdatedTime.CommandText = "SELECT MAX(LastUpdate) FROM """ +
sTableName + """"
Dim LastUpdate As Object = cmdLastUpdatedTime.ExecuteScalar()

If Not (LastUpdate Is DBNull.Value) Then
If sLastUpdateTimes.Length > 0 Then _
sLastUpdateTimes.Append(",")
sLastUpdateTimes.Append(sTableName + "=")
sLastUpdateTimes.Append(CDate(LastUpdate).ToString("yyyy-MM-dd
HH\:mm\:ss.fff"))
End If

End If

iTable += 1
Me.barCurrent.Value = (iTable / iTableCount) * 100

End While

drLocalTables.Close()
sqlLocal.Close()

[/VB.NET]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top