asked
duplicate records while loop
Hi All, I am facing a very strange issue while working with while loop and table variable. Please find below my code in short. A table variable - uniqueconsolidationkey, with following columns 1. Rowid int identity primary 2. DocumentId varchar value 3. Consolidationkey. First we inserting distinct consolidation value in our table variable. It is a simple insert. Selecting data from another table. Now, we apply a while loop through each row and update table variable with documentid. Please see the code below. Rowno=1 and rowcount is no of row in our table variable While(rowcount>rowno) Begin select @docid=some query to fetch milli seconds value plus some other varchar data If(@prevdocid<>@docid) Begin Update @uniqueconsolidationkey Set documentid=@docid Where rowid=rowno Set rowno=rowno+1 Set @prevdocid=@docid End End Now the issue sometimes like 1 out of 5 time. Same documentid is assigned to different rows. I am not sure how. And one more thing to note is it happens for 4 rows alternatively. For ex. If row 385 has docid as ABC123 and row 386 has docid as DEF345 then at 387 docid again came as ABC123 and for 388 DEF345. How this happened when we have all checks in place. This code run fine but sometime as I say once in five we see such issue. A little help will be highly appreciated
Can you give more details about the relationship between the data in the table variable and the docid? What is the significance of updating the docid? I am wondering if there is a way of accomplishing this in a single update query, but I am having trouble visualizing it without more information.
Ok... First table variable is loaded with consolidationkey from table say T1. T1 holds multiple duplicate consolidationkey. I want to have same documentid for each consolidation key. so first I load distinct consolidation key in table variable and they through while loop apply each consolidation key with docid. Then write back those docid with where clause of consolidationkey in table T1. I hope I made it clear enough. Also issue doesn't appear if I use delay of .02 millisecond right after select @documentid. I think I might be because of parallelism two rows are getting updated at one run
To investigate parallelism, try rerunning the query with "OPTION (MAXDOP 1)" at the end of each SELECT and UPDATE statement. That limits the query to a single thread. If that eliminates the duplication you are seeing, then yes, it was probably parallelism. However, in my test of this with a simplified version of your query, it was painfully slow. Perhaps the query can be restructured to work with set-based logic rather than the row-by-row model used here? Can you provide more information about how the document ID is generated? I think it might be possible to include that as part of a select statement against the table variable.
Hi everyone. Problem lies in the millisecond part.. I am not sure how but if I replace it with any other varchar it work fine. Please find below the exact code which we are using to get time difference in millisecond. Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate() as varchar(9))
Still I haven't find a Root cause of it. Does anyone know if this can be caused by Paging i.e how data stored in SQL. Might be when row goes to next page it again fill the last two row. Is it possible? Due to high fragmentation or something in DB.
> Please find below the exact code which > we are using to get time difference in > millisecond. > > Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate() > as varchar(9)) I don't know about anyone else but when I run that code... SELECT Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate() as varchar(9)) ... I get the following error... Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'as'. It's because the last GETDATE() is part of the DATEDIFF and it's missing a parenthesis right after that. The code should look like the following... SELECT Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate()) as varchar(9))
15 People are following this question.
