SqlCe Performance Issue with Datetime Datatype columns

Question

Hi, 

I need to insert around 40,000 records in a Table, the table consist of 4 column of which two are of nvarchar datatype and other two are datetime datatype, Am using SqlceResultset and Table Direct property to insert the records, to insert 40,000 records it is taking 44 seconds, for testing I have changed all the column datatypes to nvarchar and now for inserting 40,000 records it was taking only 16 seconds. Is there any way that i would get better performance with datetime column datatypes in sqlce. Am using sqlce 3.5

Thanks in advance

---

Julian B

Answer 1

I'm assuming your data is coming from text file or other text source? If so that's expected as parsing date string is very expensive operation.

That is especially true if you don't specify date format while parsing. In that case parser would try about couple dozen different formats. 

Anyways, to test out try inserting DateTime.UtcNow to avoid parsing and see if that makes a difference.

---

This posting is provided "AS IS" with no warranties, and confers no rights.