Improve SQL performance for populating List<T>

I have 200,000 records in a database with the PK as a varchar(50)

Every 5 minutes I do a SELECT COUNT(*) FROM TABLE

If that result is greater than the List.Count I then execute

"SELECT * FROM TABLE WHERE PRIMARYKEY NOT IN ( " + myList.ToCSVString() + ")"

The reason I do this is because records are being added to the table via another process.

This query takes a long time to run and I also believe 开发者_高级运维its throwing an OutOfMemoryException

Is there a better way to implement this?

Thanks

SQL Server has a solution for this, add a timestamp column, every time you touch any row in the table the timestamp will grow.

Add an index for the timestamp column.

Instead of just storing ids in memory, store ids and last timestamp.

To update:

• select max timestamp
• select all the rows between old max timestamp and current max timestamp
• merge that into the list

Handling deletions is a bit more tricky, but can be achieved if you tombstone as opposed to delete.

Can you change the table?
If so, you might want to add a new auto incremented column that will serve as the PK TableId.

On each SELECT save the max id and on the next select add where TableId > maxId.

Create an INT PK, and use something like this:

"SELECT * FROM TABLE WHERE MY_ID > " + myList.Last().Id;

If you can't change your PK, create another column with date as type , and with NOW() as the default value and use it to query for new items.

Create another table in the database with a single column for for the primary key. When your application starts, insert the PKs into this table. Then you can detect added keys directly with a select rather than checking the count:

select PrimaryKey from Table where PrimaryKey not in (select PrimaryKey from OtherTable) 

If this CSV list is large, I would recommend loading your file into a temp table, put an index on it and do a left join where null

select tbl.*
from table tbl
left join #tmpTable tmp on tbl.primarykey = tmp.primarykey
where tmp.primary key is null

edit: a Primary Key should not be a varchar. It should almost always be a incremented int/bigint. This would've been a lot easier. select * from table where primarykey > @lastknownkey

Smack the DB programmer who designed this.. :p

This design would also cause index fragmentation because rows won't be inserted in a linear fashion.