SaguiItay

My blog has moved!

You should be automatically redirected in 4 seconds. If not, visit:
http://itaysagui.wordpress.com
and update your bookmarks.

Monday, October 1, 2007

SqlCE doesn't support TRUNCATE TABLE

Not long ago, I modified a piece of code to use SqlCE as a data store. The older code was using OleDb to access an MS Access file. As part of the modifications, our team did a general overview of much of the data access code. We did many changes, more than I can even number. Here is a partial list: Review of table keys, indices and restrictions. Usage of IDbCommand instead of SQL string statements. Removed legacy object model. Replaced consecutive DELETE and INSERT statements with UPDATE. and so on. A few days ago, I noticed some delay in the data layer. This was nothing new - we process a huge amount of data - but since the many improvements, what used to be fast might now appear slow compared to the optimized code. After some search I've come up to a method that clears a many tables in the database. Something along the lines of:
using (IDbCommand cmd = DAL.GetClearTableCommand(tablename))
{
    // cmd.CommandText == "DELETE FROM " + tablename
    cmd.ExecuteNonQuery();
}
This of course, is nothing fancy or special. My thought was, instead of performing a DELETE statement, why not use the TRUNCATE TABLE statement, which is faster and more efficient. To my surprise, changing the GetClearTableCommand() method to return a TRUNCATE TABLE statement results in an parsing exception. After some research, I've found that the TRUNCATE TABLE statement is not supported/implemented in SqlCE. Much to my disappointment, I will have to leave the DELETE statement in place, until I find a faster solution.

Labels: , , , ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]



<< Home