SQL CE is somewhat slower than SQLite, but I have not observed the 120x slower performance you are seeing. I am not sure what can explain that. You can always try a compact and repair on the DB (Site Settings - General page), but I doubt that will be a cure-all.

For the best performance, switch to SQL Server. Even the free SQL Server Express has very fast performance. The Admin Guide has instructions for doing a conversion.

However, it is possible the bottleneck is caused by something other than the DB, in which case switching to SQL Server may not have a noticeable effect. In my experience, the vast majority of server resources during a sync are involved in loading images into memory and creating the thumbnail and low-res versions. If you are syncing images that are larger than before, you will notice a slowdown.

I suspect most of the slowness comes from ASP.NET loading each image into memory and building the thumbnail and low-res versions. If your image files are large (say, more than 2 MB each), that will be a pretty slow process.

If you don't care about preserving the originals, make smaller versions with an image editor and then sync those.

A client program won't be any faster and will still require an open window. Plus, it has all the disadvantages associated with fat clients.

Remember, you can use auto-sync to sync things up without any user intervention or waiting for the UI.

Indeed. I tried the compact and repair. It didn't improve performance.
It came back with message that the database was healthy and no inconsistencies were found.
The size went from 52884Kb to 50196Kb. Is this size within normal parameters for a SQL CE database referencing 16.152 files with a total size of 40,5Gb?



The average size of the images I'm sync'ing hasn't changed since the upgrade, neither has the amount of files. So - although my images are on average 3-4 Mb - this can't be the trigger for it running slower after the upgrade.
The platform the Gallery is running on hasn't changed either: a VM running Windows Server 2008R2 on a physical box - also running the same OS. I haven't changed any of the VM parameters (vCPU, memory, disk).
I was thinking it might be related to the video-conversion running in the background and consuming resources but I removed all the conversion entries and checked that it remained "idle" during sync'ing. It didn't improve the performance a single bit.

Was the sync'ing logic updated/changed as part of the upgrade?
The IIS Works Service is maxing out to 97-99% during the sync, but I suppose that is to be expected.
Another thing I noticed was that - although Galleryserverpro is running under .Net 4.0.30319 - the overall .Net Framework version is still referring to 2.0. Could this have something to do with performance? I'm not big a IIS specialist... :-)

I'd like to hold of installing and running SQLServer for the time being because - as you already indicated - I'm not sure this will actually solve the problem.

Regards,

Peter

I seem to be having the same problem. Synchronising is taking about 100 seconds per image.

I downloaded the source code and added logging around all of the synchronisation code to try to figure out what was taking so long. I traced it back to:

Factory.GetDataProvider().MediaObject_Save(this._galleryObject)
->
GalleryServerPro.Data.SqlCe.SqlCeGalleryServerProProvider.MediaObject_Save and was logged at taking 90267ms

Interesting. 3 seconds for each insert to the MediaObjectMetadata table? Not cool. I wonder if it correlates to the number of records in the table. Can you tell me about how many you have in there?

Thanks. When I get a chance I'll try to repro the situation. One of the first things I'll look into is whether the indexes are causing INSERT slowdowns.

Those are all good points, jambalaya. I intend to dig into this issue as soon as time permits, and there will probably be one of two outcomes: 1) SQL CE performance is significantly improved 2) The Admin Guide will prominently mention the performance difference between the two databases.

Hopefully it's the first one...