SubSonic AddMany() vs foreach loop Add()

Question

I'm trying to figure out whether or not SubSonics AddMany() method is faster than a simple foreach loop. I poked around a bit on the SubSonic site but didn't see much on performance stats.

What I currently have. (.ForEach() just has some validation it it, other than that it works just like forEach(.....){ do stuff})

records.ForEach(record =>
            {
                newRepository.Add(record);
                recordsProcessed++;
                if (cleanUp) oldRepository.Delete<T>(record);
            });

Which would change too

newRepository.AddMany(records);
if (cleanUp) oldRepository.DeleteMany<T>(records);    

If you notice with this method I lose the count of how many records I've processed which isn't critical... But it would be nice to be able to display to the user how many records were moved with this tool.

So my questions boil down to: Would AddMany() be noticeably faster to use? And is there any way to get a count of the number of records actually copied over? If it succeeds can I assume all the records were processed? If one record fails, does the whole process fail?

Thanks in advance.

Answer 1

Just to clarify, AddMany() generates individual queries per row and submits them via a batch; DeleteMany() generates a single query. Please consult the source code and the generated SQL when you want to know what happens to your queries.

Your first approach is slow: 2*N queries. However, if you submit the queries using a batch it would be faster.

Your second approach is faster: N+1 queries. You can find how many will be added simply by enumerating 'records'.

If there is a risk of exceeding capacity limits on the size of a batch, then submit 50 or 100 at a time with little penalty.

Your final question depends on transactions. If the whole operation is one transaction, it will commit of abort as one. Otherwise, each query will stand alone. Your choice.