![]() ![]() Returning duplicates (because of page splits and allocation order scan) is not a problem because I stage the data, use row_number partition by timestamp field to keep the latest record and then do a SQL MERGE with target table. ![]() NOLOCK causes non-repeatable reads and phantom reads, which is also fine because my SELECT query is not running inside a transaction. But there are no transactions in my database/application so this is not an issue. INSERT INTO HISTORY TABLE THE VALUE FROM results in dirty reads. MERGE DATA FROM STAGING TABLE INTO TARGET TABLE INSERT THE ABOVE RECORDS INTO STAGING TABLE WHERE AND is no index on timestamp column but an index may be added in the future JOIN TBL2 T2 WITH(NOLOCK) ON T1.ID=T2.T1_ID The two tables that are used in the SELECT query may be concurrently modified when the SELECT query is running, but the join criteria's column values won't change.Įxample pseudocode: DECLARE MAX(LOADDATE) FROM HISTORYTABLE Every 10 minutes, the scheduler runs a SELECT query (with NOLOCK) with WHERE clause based on a timestamp to fetch the data, dump it into a staging table, remove duplicates if any (keep latest) and merge it into the target table. The target DB has a history table that holds the LoadDate. I understand that there are better ways like log shipping, replication, mirroring, AG, clustering to have a replica database, but those are not the point of this question. I understand that there are problems with using NOLOCK but I am thinking of ways to counter them using the strategy explained below. I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |