• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1247
  • Last Modified:

How do improve SQLCE 3.5 performance when using LIKE & wildcards in where statement?

Here is an example of a the SQL query that I run in a C#.Net mobile application running on a PPC2003 device:

SELECT top (100) ITEM_ID, DESCRIPTION, RETAIL, UPC, CUSTOMER_SKU FROM items
 where
(
ITEM_ID  like '%54%'
OR
DESCRIPTION  like '%54%'
OR
UPC  like '%54%'
OR
CUSTOMER_SKU  like '%54%'
)

The data is in a SQL CE 3.5 compact database file.  The .sdf file is 18 megs, with 54K records.  All four of the columns being used in the where statement have an index.  When I execute the above query, the results are populated to a grid instanteously.  But if the user enters a search value of "1554", the query will look like this:
SELECT top (100) ITEM_ID, DESCRIPTION, RETAIL, UPC, CUSTOMER_SKU FROM items
 where
(
ITEM_ID  like '%1554%'
OR
DESCRIPTION  like '%1554%'
OR
UPC  like '%1554%'
OR
CUSTOMER_SKU  like '%1554%'
)

But in the case of "1554" or another larger number, the query takes a LONG time - about 1-2 minutes to return a result.  All the fields are NVARCHAR.  DESCRIPTION is an 80 char field, the others are about 15 characters.

Background: The idea here is for the user to be able to enter a search string and search ALL of these data columns, anywhere within the column, to find occurrances of the string.  The returned dataset then populates a grid.

Here is a snip of the C#.Net code:
private void search(string sqlstatement)
        {
            //Fire tis when this form loads to search for text entered by user.
            try
            {
                {
                    if (App.DBConnected == true)
                    {
                        //Wait cursor
                        Cursor.Current = Cursors.WaitCursor;

                        //Create new command
                        SqlCeCommand cmd = App.Conn.CreateCommand();
                        //Set SQL statement                              

                        string sql = sqlstatement;
                        long reccount = 0;

                        cmd.CommandText = sql;

                        // Create a DataSet to put our results in
                        DataSet ds = new DataSet();

                        // Create the adapter
                        SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);

                        resizedRows.Clear();

                        // Fill it
                        da.Fill(ds, "Items");

                        reccount = ds.Tables["items"].Rows.Count;
                        lblRecCount.Text = "Found: " + reccount.ToString("#,##0");
                        //dtgItemList.DataSource = ds.Tables["items"];
                        dgLookup.DataSource = ds.Tables["items"];


Any idea why this behavior would occur?  Any ideas to improve the query, achieve the same results?
0
dhollow2
Asked:
dhollow2
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
field LIKE '%xxx%'
cannot be optimized or indexed.


with full sql server, a full-text index could be an alternative, but not with CE
0
 
dhollow2Author Commented:
Thanks for the reply.  That helps me understand the problem with the compact version of SQL.  I does work much better without the LIKE % stuff with it.  This definately lets me know that Full-Text Search isn't avail either.  

Just helping me understand fully here:
Is it true that the more characters I use to search with, the longer the result will take to return?  That seems to be the behavior to me.
0
 
dhollow2Author Commented:
I just had a follow-up question to clarify the issue, but am awarding points now.  Thanks for quick reply.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it true that the more characters I use to search with, the longer the result will take to return?  
it should be actually the other way round, because the longer the value you search, the less chances/tests there are to be done to check ...

however, I don't know if CE version works "differently" in that matter.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now