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_.

Oh, add these statements after "cmd.CommandText=lotsaInserts":

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

Hope all that makes sense.

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

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

we got the same issues. ever gotten anywere with your research ?

regards

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...

Use a result set object for inserts.

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

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

Hi,

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

thanks

Anand

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!!

}

}

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