Here, here, Kevin! Any process other than single record GUI support that uses RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") either has an incorrect database design or someone simply doesn't understand the methods used to make a process setbased. And, if you write things to handle more than one row at a time, you'll be amazed at how fast just one row will process and the kind of "concurrency" you can get out of your GUI procs, as well.
Actually, in 2005, the solution would probably work if a VARCHAR(MAX) data type was used, which supports up to 2GB of data.
I had speed example of using cursor and select:
In my previous project "data migration". using cursor would spend more then TWO days, with select, it just 30 min. There were about 200K total customer records
I would need to test this in order to verify (not sure if it would work).
Perhaps having a timestamp on the table? When a SELECT is initiated this would change. Then again I believe this is only when data is changed. With that in mind you could incorporate a change to a field (a meaningless field used only for this purpose) which would fire the trigger. You could also have the trigger fire only for a change to THIS field which means you could have a custom process fire for a query that would not be included in a record update / delete / add.
With this in mind I was going to post, after reading the article (and still will) the concept of TABLE VARIABLE. I use these often, when a set based operation is simply not possible.
Upon entering this forum I see the first post is regarding the very concept that I planned on writing about. I believe I would change the logic just a bit however as the poster only mentions the Identity column. I don't know that this would work as the complete record is not present until the trigger has completed (committed).
That said I would implement the TABLE VARIABLE as follows (keeping the logic in the trigger rather than a trigger calling an external procedure – Eliminates the dynamic portion as well as the parsing of XML, Comma string, ect.).
Lets say we are inserting simple customer data (multiple records / bulk insert). Another assumption is that we are not inserting more than something like 5,000 records at a time.
DECLARE @CustomerID as varchar (20)
,@CustFirstName as varchar (30)
,@CustLastName as varchar (40)
,@CustPhone1 as varchar (15)
,@RecordCount as int
DECLARE @ProcessTable as TABLE
(
CustomerID varchar (20)
,CustFirstName varchar (30)
,CustLastName varchar (40)
,CustPhone1 varchar (15)
,Processed bit 0
)
INSERT INTO @ProcessTable
(CustomerID, CustFirstName, CustLastName, CustPhone1, Processed)
SELECT CustomerID, CustFirstName, CustLastName, CustPhone1, Processed
FROM Inserted
SELECT @RecordCount = count(*)
FROM @ProcessTable
WHILE @Count > 0
BEGIN
SELECT TOP 1 @CustomerID = CustomerID, @CustFirstName = CustFirstName
,@CustLastName = CustLastName, @CustPhone1 = CustPhone1
WHERE Processed = 0
[
Perform your logic here such as duplicating the record somewhere, checking the data, ect
]
SET @Count = (@Count – 1)
UPDATE @ProcessTable
SET Processed = 1
WHERE CustomerID = @CustomerID
CONTINUE
END
This is a very basic example however I think you see the point. Now another consideration is that there could be more than one update to a given CustomerID within this bulk insert. With that in mind I would add field to my table variable, lets call it ( InsertedID int PK). I would then update my processed flag based upon this Identity column so that multiple records affecting the same CustomerID could be handled.
Any thoughts?
Jeff
