2

I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact Edition 4.0 does not support full-text-indexing

Is there any index that will mimic the way a full-text-index works?

Update and Insert Statement taking forever

This is the code I am using:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC


PredTable = PredTA.GetDataByPartialWord(combo)
        If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
        If PredTable.Count = 0 Then PredTA.Insert(combo, 1)
Improve this question
8
  • 1
    You know Compact Edition is deprecated, right? Have you considered using Express with Advanced Services, which includes full-text search?
    – Aaron Bertrand
    Commented Dec 24, 2014 at 14:41
  • you can cache queries in advance; for cache mechanism please refer to msdn.microsoft.com/en-us/library/cc293623.aspx
    – Ahmad Abuhasna
    Commented Dec 24, 2014 at 14:47
  • That post also says that full text predicates do not allow autoparameterization. It seems unlikely that cache will help with full text searches of any complexity.
    – RLF
    Commented Dec 24, 2014 at 14:54
  • @AaronBertrand if I transfer over to Sql-server Express would it be simple to transfer my .sdf database?
    – Dman
    Commented Dec 24, 2014 at 15:15
  • Sorry, no idea. What made you switch to Compact in the first place? How long ago did you do it? How much has your database really changed since then?
    – Aaron Bertrand
    Commented Dec 24, 2014 at 15:17

1 Answer 1

Reset to default
0

Because Compact Edition is deprecated and doesn't support full-text search (or anything like it), I think the best solution here is to stop using Compact Edition and instead use SQL Server Express with Advanced Services, which includes full-text search capabilities.

Stop moving database files around between developers. This is just dangerous, regardless of whether you are using Compact Edition or proper editions of SQL Server. You should install a single instance of SQL Server, and everybody manage a single copy of the database. Use source control so that rolling back is easier than "use the copy of the file from Jimmy's machine and then add this change and take this table away..."

In order to convert back from Compact Edition to proper SQL Server, see this question and this question.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.