search

mtanneryd / ef-bulk-operations

Bulk operations for Entity Framework 6
Apache License 2.0
80 stars 30 forks source link

SELECT COUNT(*) FROM {tableName.Fullname} performance #4

Closed badgerparade closed 6 years ago

badgerparade commented 6 years ago

If a table has an int primary key and has a large number of rows (e.g. > 100 million) then the SELECT COUNT(*) has a negative impact on performance because of the scan on the table. For me it's adding in 4-5 seconds per bulk insert.

Looking at the code it looks like the count is only used to see if there's any records in the table for setting the nextId. You could change from a "SELECT COUNT(*) FROM ...." to a "SELECT TOP 1 1 FROM ..." and then handle the null as a zero if the table doesn't have any records.

badgerparade commented 6 years ago

Or you could change the CommandText to be "SELECT CASE WHEN EXISTS (SELECT TOP 1 * FROM {tableName.Fullname}) THEN 1 ELSE 0 END"

mtanneryd commented 6 years ago

That's a good idea. Thanks!

mtanneryd commented 6 years ago

I just made the suggested change and started a rather long running db population program trying it out.

badgerparade commented 6 years ago

Cool, hopefully you'll see the speed improvement. If you ever want a big database for playing around with I love using the stack overflow public database dump. You can get the SQL Server version from here: https://www.brentozar.com/archive/2018/06/new-stack-overflow-public-database-available-2018-06/