Geeks With Blogs
ArchitectNow Kevin Grossnicklaus' Blog

The project I'm currently wrapping up is a large .NET Windows "Smart Client" application (which I'm sure you'll hear more about in upcoming posts).  It actually has the capability of working in "offline" mode and we have a pretty extensive infrastructure to cache data locally and store it for offline use in a SQL Server 2K5 CE database (which we auto-deploy with the rest of the app via ClickOnce).

Unfortunately, we've recently hit somewhat of a snag when our offline cache grows significantly.  We have a test case where (after loading a large amount of data into the application via a saved document) we end up with 90,000+ lines of data in a cache table that we want to write out to the SQL CE database when the application closes.  Basically, it requires 90K of simple insert statements in a loop.  This is where it's readily apparent that SQL CE is not a full SQL 2K5.  These 90,000 inserts take over 10 minutes to complete. 

We're currently evaluating any ways to increase the speed of this process (or provide the users the option to bypass).  We can show a progress bar but who likes to watch a progress bar for 10 minutes when all you want is to exit an application? :)

Other than performance in situations like this (and lacking the ability to use stored procs) we have been pleasantly suprised with the SQL CE option for offline data.  We've gotten pretty good at using it as well.

If you need this type of functionality (or want to know what else it is capable of) check it out here:

http://www.microsoft.com/sql/editions/compact/default.mspx

-Kev

Posted on Friday, June 22, 2007 1:23 PM | Back to top


Comments on this post: SQL Server 2005 Compact Edition performance

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Are you calling ExecuteNonQuery() 90,000 times? If so then try out concatenating 100 INSERT statements to a string, then issue 100 at a time. Something like:

int x=0;
while(notDone)
{ // Wicked loop doing 90,000 INSERTs

... Code that builds out insertStatement, perhaps adding uniquely named parameters based on x

lotsaInserts += " " + insertStatement;
if(++x % 100 == 0)
{
cmd.CommandText=lotsaInserts;
}
}

This should speed it up _significantly_.
Left by Lorin Thwaits on Jun 22, 2007 1:52 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Oh, add these statements after "cmd.CommandText=lotsaInserts":

cmd.ExecuteNonQuery();
lotsaInserts=String.Empty;

Hope all that makes sense.
Left by Lorin Thwaits on Jun 22, 2007 1:55 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
It does actually :) We'd optimized the loop as best we could and were still looking at other possibilities but I hadn't thought of batching the calls. I'll give this a try and see if it helps.

Thanks a million for the suggestion.

-Kev
Left by Kevin on Jun 22, 2007 4:33 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Unfortunately, it doesn't appear SQL CE supports concatenating the statements together. We've tried a number of delimeters and what works on a regular SQL 2K5 install is not supported by the CE version :(

We're still looking at it though...

-Kev
Left by Kevin on Jun 26, 2007 12:08 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
we got the same issues. ever gotten anywere with your research ?

regards
Left by matthiasg on Nov 15, 2007 1:41 AM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Have you tried putting the INSERT's into a transaction? I'm not sure how the underlying mechanisms work but it might batch up the INSERT's and then apply the locks etc. in one go - rather than locking the table, inserting the row, freeing up the table 90k times it should only do it once.

I've just discovered CE so i'm doing some tests on it now...
Left by Alan on Apr 23, 2008 6:57 AM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Use a result set object for inserts.
Left by Ziad on Feb 11, 2009 10:50 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
I found a solution on this article:
http://www.pocketpcdn.com/forum/viewtopic.php?t=11003

I was inserting 1188 rows in 1 minute and 23 seconds, and now, it took 18 miliseconds!

Really awsome!

Best regards,

Henrique
Left by Henrique on Jul 12, 2009 5:02 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Can you please post how did you increase performance from 1:23 to 18 miliseconds, this article is no logner avaibale, and it would be really great to see that solution.
Thanks
Left by homer on Dec 03, 2009 7:44 AM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
Hi,

thanks a lot for the link. The link works and it is the exact info I need.

thanks

Anand
Left by Anand on Apr 29, 2010 6:16 AM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
You can get much better performance if you use parametric queries.

try:
public class RowsCopiedEventArg: EventArgs {
public long RowsCopied { get; set; }
public bool Abort { get; set; }
}

class BulkCopyCE : IDisposable {

public event EventHandler<RowsCopiedEventArg> RowsCopied;

string Connection;
Dictionary<string, string> ColumnMapping;

public string DestinationTable { get; set; }
public long NotifyAfter { get; set; }

public BulkCopyCE( string Connection ) {

this.Connection = Connection;
ColumnMapping = new Dictionary<string, string>( );

}

public void MapColumn( string SourceColumn, string DestinationColumn ) {
ColumnMapping.Add( SourceColumn, DestinationColumn );
}

public void Write( IDataReader reader ) {

reader.Read( );

using ( var ceConn = new SqlCeConnection( Connection ) ) {

var ceCmd = new SqlCeCommand( );
int[ ] ColumnMap = new int[ ColumnMapping.Count ];

var sFields = new StringBuilder( );
var sParam = new StringBuilder( );

int index = 0;
foreach ( string item in ColumnMapping.Keys ) {

int pos = reader.GetOrdinal( item );
ColumnMap[ index ] = pos;

if ( index > 0 ) {
sFields.Append( ", " );
sParam.Append( ", " );
}
sFields.Append( ColumnMapping[ item ] );
sParam.Append( "@p_" + item );

ceCmd.Parameters.Add( new SqlCeParameter( "@p_" + item, GetSqlDBTypeFromType( reader.GetValue( pos ).GetType( ) ) ) );

index++;

}

// prepare statement
ceConn.Open( );
ceCmd.Connection = ceConn;
ceCmd.CommandText = "insert into " + DestinationTable + " (" + sFields.ToString( ) + ") values (" + sParam.ToString( ) + ")";
ceCmd.CommandType = CommandType.Text;
ceCmd.Prepare( );

int TotalParameters = ceCmd.Parameters.Count;
long CurrentRow = 0;
long Remaining = 0;
var EventArgument = new RowsCopiedEventArg( );
EventArgument.Abort=false;

while ( true ) {

for ( int i = 0; i < TotalParameters; i++ ) {
ceCmd.Parameters[ i ].Value = reader[ ColumnMap[ i ] ];
}
ceCmd.ExecuteNonQuery( );

CurrentRow++;

Math.DivRem( CurrentRow, this.NotifyAfter, out Remaining );
if ( Remaining == 0 ) {
EventArgument.RowsCopied = CurrentRow;
if ( this.RowsCopied != null ) {
RowsCopied( this, EventArgument );
if ( EventArgument.Abort ) break;
}
}

if ( !reader.Read( ) ) break;

}

EventArgument.RowsCopied = CurrentRow;
if ( this.RowsCopied != null ) {
RowsCopied( this, EventArgument );
}

}

}

public void Dispose( ) {
RowsCopied = null;
ColumnMapping.Clear( );
ColumnMapping = null;
}

SqlDbType GetSqlDBTypeFromType( Type type ) {

System.ComponentModel.TypeConverter tc = System.ComponentModel.TypeDescriptor.GetConverter( typeof( DbType ) );

DbType dbType = ( DbType )tc.ConvertFrom( type.Name );
// A cheat, but the parameter class knows how to map between DbType and SqlDBType.
SqlCeParameter param = new SqlCeParameter( );
param.DbType = dbType;
return param.SqlDbType; // The parameter class did the conversion for us!!

}

}
Left by Jorge Varas on Jul 26, 2010 4:19 PM

# re: SQL Server 2005 Compact Edition performance
Requesting Gravatar...
I have thoroughly used Sqlite in the past, it is complete, but lacks speed and has bugs, I discovered that Sqlite can't be used by even 3 Users doing long database operations at the same time. It just locks up. Now only yesterday, I finally realized and was shocked that Sql Server Compact Edition is a hidden magic tool, does all the things i dreamt of. Sql Server Compact Edition 3.5 is the best Embedded Database that you will ever find in the entire world. It is free! No bugs came ever with my handling of Sql CE.

Tushar
Left by Tushar on Dec 01, 2010 2:27 PM

Your comment:
 (will show your gravatar)


Copyright © Kevin Grossnicklaus | Powered by: GeeksWithBlogs.net