>> if not exists(Select 1 From [dbo].[Transactions] Where PNR = @PNR AND Timestamp = @Timestamp)
>>
>
>Hi, Frank,
>
>There are (at least) 2 possibilities.
>
>One is that the EXISTS check might be causing an issue - either the check isn't optimized, or there's a locking issue, etc.
>

Thanks Kevin, is there a better way to not allow duplicate records to be added?


>Second (and perhaps more likely)..it looks (at least it appears from your code) that you have a primary key on a uniqueidentifier. Maybe there's a clustered index on that uniqueidentifier as well. If that's the case...AND if you aren't reindexing regularly, that creates the potential for database fragmentation and huge page splits (since uniqueidentifiers populated with the newid() function are going to generate random ID values, which creates huge work for the database engine to maintain an index)
>
>As a practice - unless there is a compelling reason for doing so (distributed database system as one example), I caution people about using uniqueidentifiers as indexes/primary keys.
>
>Let me know if those 2 items make sense. Here is a link that shows how you can check for database fragmentation. If you have fragmentation levels above 30%, that can affect the performance of inserts. I've seen database neglect (i.e. no reindex jobs) that led to fragmentation levels as high as 90%. Fragmentation can occur pretty quickly when you use uniqueidentifiers as indexes, when they are populated using newid random values.

Yes, the uniqueidentifier is a primary key and there is a clustered index on it. I inherited the database so am not sure of the repercussions of changing to an auto incrementing integer. I will check the fragmentation and reindexing to see if that helps.