speed-up question

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]
 
D

Daniel Moth

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
 
J

Jon Brunson

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

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...
 
D

Daniel Moth

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 :blush:))

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...
 
J

Jon Brunson

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 :blush:))

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help: Please check my code for dynamic table names with respect toschema 2
Please help me fix my code to check for existence of table 1
SQL Server Mobile Edition does not support calls to HasRows propertyif the underlying cursor is not 4
Speed up Bulk Insert Dataset records! 1

Top