SQLCE Optimization Help

  • Thread starter Thread starter Josue
  • Start date Start date
J

Josue

Background:

I am writing a word game in which you have a 4*4 grid of random letter,
and you find words within the grid using adjacent letters. I'm using
this project to help learn the .net cf as well as sqlce, but at the same
time, I'd like the game to start quickly.

Problem:

After I've created the game grid, I have to figure out all the available
words in the grid. I have two functions (see below), one to check if
the current string is the start of a word, and another function to check
if the current string is a word. On my test grid, IsWord is called 272,
StartWord is called 1435. The game takes 1 minute and 45 seconds to
find all of the possible words. I was hoping to tap into someone's
expertise in these forums, and see if I could tweak these functions to
be leaner and meaner. The database the wordlist is on is 26 tables (one
for each letter), with one column Primary Key column which is the word.

Other info:

I'm testing this application on an iPaq 3850 Pocket PC 2002 device.
..net cf 1.1 sp1. The database is over 15,000 words.

***IsWord Function***
Private Function IsWord(ByVal strWordToCheck As String) As Boolean
Try
Dim strSql As String = "SELECT word FROM word_" & _
Mid(strWordToCheck, 1, 1) & " WHERE word = '" & _
strWordToCheck & "'"

Dim strStuff As String
cmd.CommandText = strSql 'cmd is a module level object
strStuff = cmd.ExecuteScalar

If strStuff Is Nothing Then
Return False
Else
Return True
End If

Catch ex As SqlCeException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Function

***StartWord***
Private Function StartWord(ByVal strWordToCheck As String) As Boolean
If (strWordToCheck.Length > 1) Then
Try
Dim strStuff As String
Dim strSql As String = "SELECT word FROM word_" & _
Mid(strWordToCheck, 1, 1) & " WHERE word LIKE '" & _
strWordToCheck & "%'"

intStartWord = intStartWord + 1
cmd.CommandText = strSql
cmd.CommandType = CommandType.Text
strStuff = cmd.ExecuteScalar

If strStuff Is Nothing Then
Return False
Else
Return True
End If

Catch ex As SqlCeException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Else
Return True
End If
End Function

I know it's an involving question, but if anyone has any tips to share,
I would be all ears.
 
Hi Josue,

I'm not sure if I can give you a whole solution but here's a couple of
suggestions that might help. First, you shouldn't have 26 tables for each
letter - the purpose of having multiple tables is to express different
*types* of data (that might contain relationships to each other), not to
paginate that data. Second, you should probably query that table *once* at
the start of your application, loading it into your own data structure.
I'd expect in such a scenario a binary chop search into an array (a well
known algorithm) would be a more appropriate (and faster) approach than
thrashing SQLCE.

Ben Albahari
.NET Compact Framework
Microsoft

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


--------------------
| Subject: SQLCE Optimization Help
| From: Josue <josue85@hotmaildotcom>
| Message-ID: <[email protected]>
| User-Agent: Xnews/5.04.25
| Newsgroups: microsoft.public.dotnet.framework.compactframework
| Date: Tue, 07 Oct 2003 16:56:08 -0700
| NNTP-Posting-Host: 12-249-242-28.client.attbi.com 12.249.242.28
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:35382
| X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
|
| Background:
|
| I am writing a word game in which you have a 4*4 grid of random letter,
| and you find words within the grid using adjacent letters. I'm using
| this project to help learn the .net cf as well as sqlce, but at the same
| time, I'd like the game to start quickly.
|
| Problem:
|
| After I've created the game grid, I have to figure out all the available
| words in the grid. I have two functions (see below), one to check if
| the current string is the start of a word, and another function to check
| if the current string is a word. On my test grid, IsWord is called 272,
| StartWord is called 1435. The game takes 1 minute and 45 seconds to
| find all of the possible words. I was hoping to tap into someone's
| expertise in these forums, and see if I could tweak these functions to
| be leaner and meaner. The database the wordlist is on is 26 tables (one
| for each letter), with one column Primary Key column which is the word.
|
| Other info:
|
| I'm testing this application on an iPaq 3850 Pocket PC 2002 device.
| .net cf 1.1 sp1. The database is over 15,000 words.
|
| ***IsWord Function***
| Private Function IsWord(ByVal strWordToCheck As String) As Boolean
| Try
| Dim strSql As String = "SELECT word FROM word_" & _
| Mid(strWordToCheck, 1, 1) & " WHERE word = '" & _
| strWordToCheck & "'"
|
| Dim strStuff As String
| cmd.CommandText = strSql 'cmd is a module level object
| strStuff = cmd.ExecuteScalar
|
| If strStuff Is Nothing Then
| Return False
| Else
| Return True
| End If
|
| Catch ex As SqlCeException
| MessageBox.Show(ex.Message)
| Catch ex As Exception
| MessageBox.Show(ex.Message)
| End Try
| End Function
|
| ***StartWord***
| Private Function StartWord(ByVal strWordToCheck As String) As Boolean
| If (strWordToCheck.Length > 1) Then
| Try
| Dim strStuff As String
| Dim strSql As String = "SELECT word FROM word_" & _
| Mid(strWordToCheck, 1, 1) & " WHERE word LIKE '" & _
| strWordToCheck & "%'"
|
| intStartWord = intStartWord + 1
| cmd.CommandText = strSql
| cmd.CommandType = CommandType.Text
| strStuff = cmd.ExecuteScalar
|
| If strStuff Is Nothing Then
| Return False
| Else
| Return True
| End If
|
| Catch ex As SqlCeException
| MessageBox.Show(ex.Message)
| Catch ex As Exception
| MessageBox.Show(ex.Message)
| End Try
| Else
| Return True
| End If
| End Function
|
| I know it's an involving question, but if anyone has any tips to share,
| I would be all ears.
|
 
Back
Top