April 2, 2009 at 7:52 am
Hello,
I apologize in advance, but I am running into a performance issue on a PPC app that uses CE 2.0. The SDF exists on a card, and the app accesses the DB. On one particular lookup, well, not a look up really, I have an ID of a record, and I now want to pull that record from the table. The table is not what I would call well formed, nor can I make wholesale changes. (I have inherited this app and it's quirks) Back to the question. Using one clients SDF the retrieval is fast, less than a second. Same sdf structure, same code, same machine, but a different clients SDF (approx. same # of records, within 10%) the retreival can take upwards of 10-15 seconds. The only "data" difference is a blob field in the table. An image field, stored in record (not my choice) that the slower client populates, while the faster client does not. Tried removing that field from the table, and no appreciable difference. The indexes on this table are a little quirky I think, and it currently had no primary key. Indexes as follows:
8 non unique, non clustered indexes using the following fields:
dob_lastname_fname_mname dob is datetime, other 3 are varchar 50s, null allowed
dob datetime, null allowed
fname varchar50 null
iaddresseid int null allowed
ivoterid (do not let the name fool you) it is a varchar 50 field, not null
lastname, fname, mname, varchar 50s null
lastname fname dob
and one last varchar look up field.
My concern is the number and type of indexes, especially on a table with no primary key. I am nto that adept in CE, but I think these indexes might be hurting performance on the table.
Thank you
November 18, 2009 at 8:39 am
Hello all,
just posting back in case anyone looks into this thread again:
I added a primary key integer field, VoterId to the table in question, and formed an index on this identity field, and the lookups got a lot faster.
November 19, 2009 at 9:14 am
Hi,
That many indexes is not preferred, but it will significantly increase the size of the table. As far as performance due to the indexes, I don’t think the indexes are hurting that. Without looking at the query you are running it against it is hard to give advice. I will tell you that you will see a huge variation in performance if you are opening and closing the SQLCE connection to the database on every query.
Thanks,
Neal
November 19, 2009 at 9:21 am
Hi Neal
Are you for or against closing between each query?
Currently, it creates a SQL connection, opens it, and does not explicitly dispose of the connection, rather relies on the garbage collection to do so.
Thanks
Mike
November 19, 2009 at 9:31 am
Mike,
In a PDA type application I am against opening and closing for every query, but only because the performance difference is tremendous. I usually (in a VB.NET forms app) declare and open one SQL connection for the entire form to use and then I close it when the form closes and let the GC automatically dispose of it. This allows me to make multiple queries against the database under one connection.
Thanks,
Neal
November 19, 2009 at 9:35 am
Thanks again Neal, my first concern would be that all of the get/set methods exists in classes, and not the form, and some of the classes remain instatiated in the application. This is not my design, but it is what is there. Would holding a single global connection work?
November 19, 2009 at 9:38 am
Mike,
Yes, having a global connection would work, and some people actually suggest that. I personally don't like that, but it is a perfectly viable option.
Thanks,
Neal
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply