Is there anyway of generating a INSERT WHERE NOT EXISTS?

Posts   
 
    
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 15-Feb-2016 09:44:37   

We want to add some records to a table but only if there isn't already a record which matches some criteria. Currently I am running a LINQ Any query before the potential insert but I'm thinking a single INSERT WHERE NOT EXISTS would be more efficient. Something like the UpdateEntitiesDirectly method, but for new entities.

Context: Importing data from a flat file into a table, if a duplicate is found just ignore, no identity other than the combination of most of the values of each record. DB table has a Surrogate Primary Key.

V4.2. SQL server.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Feb-2016 10:52:39   

Not at the moment, no. A where not exists is indeed more efficient, although not by much. It also depends on whether the table you importing into is used by other users. insert where not exists has a lower risk of inserting a row while another user inserts a row with the same PK. The Any()->Insert method has a higher risk of a user inserting a row with the same pk after Any() but before Insert.

The Any and Insert where not exist will likely use the same index scan to decide whether there's a row already with the pk value, so for performance that doesn't mean much. If the import is done w/o users on the system, it should be OK.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 15-Feb-2016 15:31:38   

Otis wrote:

Not at the moment, no. A where not exists is indeed more efficient, although not by much. It also depends on whether the table you importing into is used by other users. insert where not exists has a lower risk of inserting a row while another user inserts a row with the same PK. The Any()->Insert method has a higher risk of a user inserting a row with the same pk after Any() but before Insert.

Concurrency isn't a concern as it's a system controlled process, so there will only ever be one import at a time. So what I've done is perfectly fine (half a second to do import 500 records), I just like the idea of combining the two steps and the existing UpdateEntitiesDirectly seems very close to achieving that. Any chance of moving this thread to the 'Feature requests' forum or otherwise record this as requested?

Cheers Jeremy

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Feb-2016 18:42:40   

Added to list of things to consider in the future (LLBLMAIN-1415). I thought it was perhaps covered by the expression support for inserts we added in v4.2, but that's not using a where clause like you need.

Not a high priority though.

Frans Bouma | Lead developer LLBLGen Pro