IDn


Greetings,

I wrote a very simple C# console application which was designed to give us a rough idea on the performance and reliability of SQLCE (the source code without metrics has been posted to this group recently). What I've found is that there are major performance and reliability issues with SQLCE 3.0 as opposed to SQLCE 2.0. Whereas SQLCE 2.0 seems to be fairly consistent in terms of it's memory usage and the amount of time taken to insert rows into the table regardless of the size of the table (to a limit, after about 300,000 records it too has major performance issues), SQLCE 3.0 seems to slow down noticably and continually use more memory even after a few thousand records are inserted and actually crashes (consistently) after 25592 records are inserted!

What gives Based on this it would seem that SQLCE 2.0 is far preferable for an unattended device that needs an uptime of over 30 days whilst constantly (several times per minute) inserting into the database.

Thanks for your thoughts on this...




Re: Is it just me or are there BIG problems with SQLCE 3.0?

ErikEJ


I am able to reproduce in a desktop app, here the "magic number" is 44030.

I suggest you go ahead a file a bug at connect.microsoft.com/sqlserver.

WORKAROUND: (as seen suggested on the pulic sql ce news groupSmile

Code Block

SqlCeUpdatableRecord rec;

SqlCeResultSet rs;

sqlCommand.CommandText = strTable;

sqlCommand.CommandType = CommandType.TableDirect;

rs = sqlCommand.ExecuteResultSet(ResultSetOptions.Updatable);

rec = rs.CreateRecord();

for (int i = 1; i <= 500000; i++)

{

if (10000 % i == 10000)

{

sqlConnection.Close();

sqlConnection.Open();

sqlCommand.Connection = sqlConnection;

rs = sqlCommand.ExecuteResultSet(ResultSetOptions.Updatable);

}

Console.Write("\rInserted " + i.ToString() + " records ");

strText = "Row Number: " + i.ToString();

rec.SetString(2, strText);

rs.Insert(rec);

}

(I am using a SqlCeResultSet for speed)







Re: Is it just me or are there BIG problems with SQLCE 3.0?

Laxmi Narsimha Rao ORUGANTI MSFT

I just tried with SQL CE v3.5 RTM RC bits and I can happily make 1 Lakh inserts both with Command.ExecuteNonQuery and ResultSet.Insert. I will try to play more with v3.0 and v3.1 and shall come back. Here is the schema I am trying to play:

CREATE TABLE ResultSetInsertTest (id int IDENTITY(1,1) PRIMARY KEY, name nvarchar(128) UNIQUE NOT NULL)

It could be specific to a particular schema also.

Can you please provide the simplified repro with full VS Solution you have thru our blog: http://blogs.msdn.com/sqlservercompact/contact.aspx. First contact us and then we will exchange directly thru mail.

Thanks,

Laxmi







Re: Is it just me or are there BIG problems with SQLCE 3.0?

IDn

Thanks, we are not going to try and work around the bugs in SQLCE 3.0, because if we find a serious bug with a simple program, then we are not going to take the risk of finding more bugs with more complex programs. Our application (which needs to ship long before SQLCE 3.5 can be released and fully tested by us), needs to run for at least a month without restarting, and it needs to put in on average 2-3 records a minute into the database during this period. It is critical for this (unattended) device to run reliably.

So to rephrase the question: As there is at least one show-stopper (for us) bug in SQLCE 3.0 that doesn't appear to be in SQLCE 2.0, as well as SQLCE 2.0 appearing to have vastly superior performance. Is there any reason why we would not want to be using SQLCE 2.0




Re: Is it just me or are there BIG problems with SQLCE 3.0?

IDn

The source code for the test application can be found in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2223503&SiteID=1




Re: Is it just me or are there BIG problems with SQLCE 3.0?

IDn

Laxmi Narsimha Rao ORUGANTI MSFT wrote:

Can you please provide the simplified repro with full VS Solution you have thru our blog: http://blogs.msdn.com/sqlservercompact/contact.aspx. First contact us and then we will exchange directly thru mail.



Well, it appears that we're going to have to go down the SQLCE 3.0 C# route, so we may need to get this fixed. There are workarounds, which work, but you'll probably want to make sure that the bug isn't in SQLCE 3.5, and you may even want to fix it in SQLCE 3.0. I've sent a message through the blog to initiate the process.


Regards,


ID