Happy new year!

I explain the subject a little bit further:

I have created a webservice. One of the methods excepts an EntityType from which the data is fetched (considering filter and sort expressions). The (result) EntityCollection is converted to a compressed delimited flat file which is delivered as a byte array over the wire. On the other side (PPC) the byte array will be decompressed and temporary stored in a text file. Then I create an EntityCollection (from the EntityFactorý known by the EntityType), and a IEntity2 object for each row (from the textfile) and insert the IEntity2 to the EntityCollection. After that I call the SaveEntityCollection()-Method ...

The transfer of the compressed byte array and the creation of the EntityCollection is very fast, but if I call the SaveEntityCollection - puuh (30 min. for 8000 records if the PPC don´t die - out of memory or something else)!

If I call the SaveEntity()-Method (for each entity), it is nor slower!

        /// <summary>
        /// Insert/Update records from file (Cache)
        /// </summary>
        /// <param name="entityType">EntityType</param>
        private void InsertFromFile(EntityType entityType)
        {
            string entityName = String.Format("{0}", entityType).Replace("Entity", "");
            string fileName = String.Format("{0}\\{1}\\{2}\\{3}.tmp", Core.ApplicationPath, "Data", "Cache", entityName);

            if (File.Exists(fileName))
            {
                StreamReader sr = new StreamReader(fileName, Encoding.Default);

                // read the first row (header information)
                string row = sr.ReadLine();
                string[] columns = row.Split('|');

                int affectedRows = 0;
                IEntityFactory2 factory = (IEntityFactory2)DMOfficeBL.Helper.GetFactoryFromEntityType(entityType);
                EntityCollection collection = new EntityCollection(factory);

                while ((row = sr.ReadLine()) != null)
                {
                    affectedRows++;
                    IEntity2 entity = (IEntity2)factory.Create();
                    entity.IsDirty = true;

                    string[] values = row.Split('|');
                    // count values
                    for (int column = 0; column < values.Length; column++)
                    {
                        Type dataType = (Type)DMOfficeBL.Helper.GetType(entity.Fields[columns[column]].DataType.FullName);
                        switch (dataType.FullName)
                        {
                            .
                            . 
                            .
                        }
                    }
                    //collection.Add((EntityBase2)entity);
                    this._BusinessLayer.SaveEntity(entity, this._BusinessLayer);
                }
                //this._BusinessLayer.SaveEntityCollection(collection, this._BusinessLayer);

                sr.Close();

                // Delete file from cache
                File.Delete(fileName);
            }
        }

Any suggestions? What is SSCEDirect? Did someone work(ed) with that classes?

Regards, Carlo

SqlCE isn't a big performer when you go to an O/R layer and don't use the direct SqlCE interface. Also, if you save the entitycollection, a transaction is started ,and every entity saved is kept alive till the transaction completes. This can be tiresome for the handheld. If you save the entities NONrecursively (!) and one by one, without a transaction, it should be faster.

Hi Otis,

I have tried to save the entities one by one, none recursivly and without a transaction, but that doesn´t help. The performance is bad like before. How can I get the database scheme from the generated code?

The method which fetches the data from the webservice knows the EntityType. Now I have to get the structure for the given EntityType. From which object/class can I get the related column (eg. Customer.CustName > Table: Customer, Column: Cust_Name)?

My idea is to create the insert/update statement by myself. Or can I get the generated statement from the DQE? Or is the DQE in this case the performance killer?

Something like that:

// INSERT
StringBuilder insert = new StringBuilder();
insert.Append("INSERT INTO ");
insert.Append(entityName); // Database tablename
insert.Append("(");

// PARAMETERS
StringBuilder params = new StringBuilder();
params.Append("(");

bool comma = false;
foreach (IEntityField2 field in entity.Fields)
{
if (comma)
{
insert.Append("," + field.???);  // real table column
params.Append(",@" + field.Name);
}
else
{
insert.Append(field.???);  // real table column
params.Append("@" + field.Name);
comma = true;
}

}

The result should be something like that:

// INSERT INTO Customer (Cust_Id, Cust_Name) VALUES (@CustId, @CustName) string insertCommand = insert.ToString() + ") VALUES " + params.ToString() + ")";

I need a solution asap, my deadlines are overriden

Regards, Carlo

Rushmore wrote:

Hi Otis,

I have tried to save the entities one by one, none recursivly and without a transaction, but that doesn´t help. The performance is bad like before. How can I get the database scheme from the generated code?

to do what exactly?

Mind you: SqlCe is slow, unless you use the direct interface, with a live cursor on the table. ANY query executed in SQL format is slow as it's mobile. So don't expect blistering performance from inserting thousands of rows through .NET + SQL.

The method which fetches the data from the webservice knows the EntityType. Now I have to get the structure for the given EntityType. From which object/class can I get the related column (eg. Customer.CustName > Table: Customer, Column: Cust_Name)? My idea is to create the insert/update statement by myself. Or can I get the generated statement from the DQE? Or is the DQE in this case the performance killer?

No, the DQE is pretty quick in generating queries, it's the engine itself which is the bottleneck.

I need a solution asap, my deadlines are overriden

Unless you're going to use the direct interface, it will be slow. Though I admit, 30 minutes is pretty slow. However, inserting a truckload of rows might make the sql engine have a lot of problems with memory, which might make wince slow as well.

Hi Otis,

I don´t want to talk LLBLGen down - rather - LLBLGen is great! I am between a rock and a hard place, and I am looking for a quick solution.

Any suggestions to speed up inserts on SqlCE?

Regards, Carlo

Rushmore wrote:

Hi Otis,

I don´t want to talk LLBLGen down - rather - LLBLGen is great!

If I could do something about it, i.o.w. if it was my fault, you should NOT stop saying I should do a better job

I am between a rock and a hard place, and I am looking for a quick solution. Any suggestions to speed up inserts on SqlCE? Regards, Carlo

Yes, please check out the direct interface to SqlCE. With that, (the ce client) it allows you to get a table object directly and insert rows like you would in old ADO recordsets. That's about the fastest CE supports. As it's not query based, the o/r runtime doesn't use it, but it can give better performance.

If I could do something about it, i.o.w. if it was my fault, you should NOT stop saying I should do a better job

Next time

For the normal "work" with the ppc application the performance isn´t realy great but I (and the customers) can live with that

But the initialization of the database takes to much time (30-60 min. per device ), and in this case the network isn´t the bottleneck .

Please check out the direct interface to SqlCE. With that, (the ce client) it allows you to get a table object directly and insert rows like you would in old ADO recordsets. That's about the fastest CE supports.

I give it a try!

Thanks.

If others have the same problem, google about

SqlCeResultSet SqlUpdateableRecordSet

I make use of the FieldPersistenceInfoProvider to get the real column names. The next step is to determine the primarykey field to create the select statement for the SqlCeCommand-Object.

// Create select statement
bool comma = false;
StringBuilder selectCommand = new StringBuilder();
selectCommand.Append("select ");
for (int column = 0; column < fieldPersistence.Length; column++)
{
IFieldPersistenceInfo info = (IFieldPersistenceInfo)fieldPersistence[column];
IEntityField2 entityField = (IEntityField2)entity.Fields[column];

// Primarykey
if (entityField.IsPrimaryKey)
{
primaryKeyField = info.SourceColumnName;
primaryKeyOrdinal = entityField.FieldIndex;
}

if (comma)
selectCommand.Append("," + info.SourceColumnName);
else
{
selectCommand.Append(info.SourceColumnName);
comma = true;
}
}
selectCommand.Append(" from ");
selectCommand.Append(fieldPersistence[0].SourceObjectName);
// Read the table structure (and maybe the given record) from the database
selectCommand.Append(" where {0}='{1}'");

SqlCeConnection ceConnection = new SqlCeConnection(Core.SubscriberConnectionString);
ceConnection.Open();

// primaryKeyField = the name of the primarykey column
// key = the primarykey value
SqlCeCommand command = new SqlCeCommand(String.Format(selectCommand.ToString(), primaryKeyField, key), ceConnection);

SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord record = resultSet.CreateRecord();

// read the first record to determine if it exists
bool updateRecord = resultSet.ReadFirst();

if updateRecord is true, I am setting the values within the resultSet, if updateRecord is false, I am setting the values within the record (SqlCeUpdateableRecord).

if (updateRecord)
  resultSet.SetString(column, "Hello World!");
else
  record.SetString(column, "Hello World!");
                
if (updateRecord)
  resultSet.Update();
else
  resultSet.Insert(record);

resultSet.Close();

Regards, Carlo