Shutdown (or next open) takes too long as it rebuilds file DB

Feb 14, 2011 at 3:08 AM
Edited Feb 14, 2011 at 4:59 AM

Hi,

In our OOB Silverlight 4 app we "checkout" a lot of data from our main SQL DB to effiproz. The user then works offline and can CheckIn later. We normally have around 10 000 records in our main table (our DB has 75 tables) + associated lookup data (some around 65k) which results in around a 50meg isolated storage file.. Here is our connection string (we are using a FILE DB):

connectionString = "connection type=FILE;auto commit=true;auto shutdown=false; initial catalog=AMTDataMobile; user=xyz; password=xyz;Pooling=true";

The user closes the app (using Application.Current.MainWindow.Close();), if we don't call ShutDown on our DB then the next time the user logins (ie. the first DB open call) it takes a few minutes to log on.

If we DO call Shutdown in our Exit method then the UI has to wait for the app to shutdown (a few minutes) and then the next log in is quicker.

I monitored the Isolated Storage and it seems that whenever ShutDown is called it makes a new file called DBName.Script.New. This starts at 0kb and goes up until it equals the size of the .Script file (around 50 megs) - this is what is taking a few minutes.

Obviously I can't expect the user to wait a few minutes to log in or exit the application, so I tried a few things:

  1. I tried ShutDown on a different thread on app exit but the next log in was still slow (I assume when the SLLauncher.exe process is killed then it kills all child threads?)
  2. I tried to use CACHED on all 75 table declarations and the on just the 5 largest tables with over 1000 records. Everything is MUCH slower now (It used to take 5 minutes to download 10 000 records and write them to Effiproze; now I stopped counting at 60 min = too long and unusable)
  3. I tried AutoShutdown=true on our connection string. This behaved a little differently, but the user still had to wait at some point the same amount of time.

I'm running out of ideas and thought this must be a very common scenario - can anyone offers some suggestions on how to get around this issue please?



Coordinator
Feb 14, 2011 at 4:40 AM

Can't you pre-build EffiProz database offline and do a minor sync with SQL DB on user access?


Feb 14, 2011 at 7:13 AM

Sorry, I am not sure what you mean by offline?

The nature of our app is that there are engineers in the field who have no connectivity. So they connect to the main server when they do (the main SQL server has millions of rows of data) and they "checkout" a subset of the data they'll be working on for a week. Then they go away for x amount of time and when they come back they check in what has changed.

The problem is not the WCF service retrieving the data - it is the writing of the data to Effiproz. It's fast enough for our means (about 1000 records per second) BUT this connection issue is a problem.

Coordinator
Feb 14, 2011 at 7:40 AM

 By offline I mean, periodically creating the EffiProz database at SQL DB site and just shipping database files to remote sites when they connect.

You can try following: doing a Checkpoint when OOB client is idle , creating database in My Documents folder instead of Isolated Storage , converting only a subset of tables to CACHED tables.

 

 

Feb 14, 2011 at 7:59 AM

Each user might have a different database depending on their UserID - besides, the WCF side of things works ok.

I did try using CACHED only on the 5 biggest tables (65k of records, 10k, 8k, 5k and 1k respectively). It increased the delay by 10 times when dumping into Effiproz (created a 500 meg file in the IS folder) so it was not an option.

Would the MyDocuments folder be faster than the IsolatedStorage folder?

Thanks for any more suggestions...

Coordinator
Feb 14, 2011 at 11:04 AM

Did you try with MyDocuments folder?

-thanks

Feb 14, 2011 at 10:37 PM

I can't find any documentation on how to do this? I tried:

public static string connectionString = "type=FILE; Data Source=c:\\TestDB;";

but it did not work.

This method does not work.

This manner just creates the subfolders under IS.

It's mentioned here but with no answer.

Please point me to any documentation on how to write your DB to any other folder other than under IS. Thanks

Coordinator
Feb 14, 2011 at 10:45 PM
Edited Feb 14, 2011 at 11:38 PM

Only supports MyDocuments, MyPictures, etc.

Here's a sample connection string,

"connection type=file; initial catalog=|MyDocuments|/efz/necs; auto commit=true; user=sa; password="

thanks

Feb 15, 2011 at 12:28 AM

Thanks, but that does not work. On the Open method it throws an exception: Illegal characters in path.

If you look at the message it still seems to be using IsolatedStorageFile.GetFullPath?

 at System.IO.Path.CheckInvalidPathChars(String path)   at System.IO.Path.Combine(String path1, String path2)   at System.IO.IsolatedStorage.IsolatedStorageFile.GetFullPath(String partialPath)   at System.IO.IsolatedStorage.IsolatedStorageFile.FileExists(String path)   at EffiProz.Core.Lib.FileUtilSlis.IsStreamElement(String elementName)   at EffiProz.Core.Persist.Logger.OpenPersistence()   at EffiProz.Core.Engine.Database.Reopen()   at EffiProz.Core.Engine.Database.Open()   at EffiProz.Core.Engine.DatabaseManager.GetDatabase(String type, String path, EffiProzProperties props)   at EffiProz.Core.Engine.DatabaseManager.NewSession(String type, String path, String user, String password, EffiProzProperties props, String zoneString, Int32 timeZoneSeconds)   at System.Data.EffiProz.EfzConnectionProxy.Open()   at System.Data.EffiProz.EfzConnection.Open()   at Amt.UI.Mobile.EntityDAL.EffiprozDB.getEfzConnection()

 

I tried to copy and paste your string exactly (and tried it without the || which causes it to make a directory under IS.

Coordinator
Feb 15, 2011 at 12:43 AM

Are on Silverlight 4 Out of Browser with Elevated Privileges?

Feb 15, 2011 at 3:39 AM

Apologies, I am running in Elevated Trust but I was debugging In Browser.... it is working OOB - I'll get back with some stats soon.

Feb 15, 2011 at 6:40 AM

Ok, writing to MyDocuments made a big difference. By way of comparison, checking our (ie. writing) 8000 records whilst under Isolated Storage took 3. 43 seconds whilst the same operation under MyDocuments took 2 minutes 10 seconds. The ShutDown delay dropped from around 30 seconds to 4 seconds, so a big improvement.

Is there any reason why this could be the case?

Thanks for the quick support.

Coordinator
Feb 15, 2011 at 7:23 AM

Good to know.