Checksum & SProc
-
Hello,
I have an SP with 10 different SELECT statements in it so returning 10 different resultsets. These SP is run every four hours to get the data out and send it to an external application for further processing. The question is that SP needs to return full set of data once a week and only incremental when run every 4 hours. So there is a variable IsIncremental in this SP, which would be supplied the value of 1 for incremental runs and 0 for full runs. We have a CreateDt and/or UpdateDt fields in most tables this SP queries, but the value coming from these tables are not reliable so we need a different approach for sending incremental data only every 4 hours. I heard of Checksum, but not sure how to implement it against 10 resultset of this SP?!
For example, the SP was run this morning at 9AM and now it will be run again at 1PM. I want to know what data the SP (in 10 resultsets) sent at 9AM and what has changed about the same data between 9AM run and up coming 1PM run so that at 1PM only that data is returned by the SP and sent again then repeat this at next run, which would be 5PM.
If you could show how to accomplish the above using Checksum or if there is a better approach than using Checksum, please suggest.
Appreciate your help and suggestions.
Thanks.
JN
-
I think the fields CreateDT and UpdateDt you've created are datetime, no?
Why are them not reliable? Where do you update those fields?
You will need just a bit field on this tables, setted up to 1 when the SP reads each record. Obviously, the SP only reads records with this field in 0.
Other approach is to track down evry record readed into a readedRecords table, something like
TABLE ReadedRecords( TableName sysname, RecordID)
and the SP would check against this table... but it is a highly expensive way... and runs only if you have same type of ID's on ev'ry table...
Pease, send more info about it, a brief descripction of the tables and recordsets returned and the main code of the SP (a brief of the code...)
Luck!
Nicolas Donadio
-
Thanks for your response.
Let's see...
UpdateDt is NOT realiable, because I don't care about each and every field changed in this table. If the fields are NOT used in my SP then I don't care that they changed. Like table agents have 20+ fields, but I only want a few fields and their values out of table agents - agent name is what I care about, but the table might have fields like agent home phone, agent taxid, agentcommision and so on. I only care if agent name changes, because that's all my SP is looking for. If the other fields changed then the UpdateDt is updated with update date stamp, but I don't care about that change so using UpdateDt field to look for changes is not reliable for my situation. If I am not bringing the changes across in my SP then I don't care that the field has changed.
This is a production database so users change values as needed using a .NET frontend application.
Following is how my SP looks:
CREATE PROCEDURE dbo.sp_Photo_SEL_AgentProfile
@AgentID INT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Begin
SELECT dbo.PhotoASite.AgentID, dbo.PhotoDSite.PhysicalPathDestination,
dbo.PhotoASite.AutoAssignedSeqNbr
FROM dbo.PhotoASite INNER JOIN dbo.PhotoDSite
ON dbo.PhotoASite.PhotoTypeID = dbo.PhotoDSite.PhotoTypeID
WHERE (dbo.PhotoASite.DisplaySeqNbr = 0) AND
(dbo.PhotoASite.IsDisplayedOnWeb = 1) AND
(dbo.PhotoASite.AGENTID = @AgentID) AND
(dbo.PhotoDSite.IsEnabled = 1) AND
(dbo.PhotoDSite.PhotoDTypeID = 206) AND
(dbo.PhotoASite.PhotoTypeID = 201)
SELECT DISTINCT AgentName = LEFT(P.AgentDisplayName, 60
FROM dbo.AgentsAgent P
WHERE P.AgentID = @AgentID
SELECT O.OfficeADDR1, O.OfficeADDR2, O.OfficeCityNM, O.OfficeStateCD, O.OfficePostalCD, AgentLicenseState = O.OfficeStateCD
FROM dbo.AgentsOffice O
INNER JOIN dbo.AgentOfficeMap POM
ON O.OfficeID = POM.OfficeID
WHERE (POM.AgentID = @AgentID)
SELECT Franchise = RTRIM(C.Brand
FROM dbo.MyCStore C
INNER JOIN stgTL_Agent stgTLP
ON C.Metro_Number = stgTLP.Metro_Number
WHERE (stgTLP.AgentID = @AgentID)
SELECT TOP 3 PC.ContactID, PhoneType = LCT.ContactHeadline,
PhoneNum = PC.ContactText
FROM dbo.LookupContact LCT
INNER JOIN AgentContact PC ON LCT.ContactID = PC.ContactID
INNER JOIN AgentOfficeMap POM
ON PC.AgentOfficeMapID = POM.AgentOfficeMapID
WHERE (POM.AgentID = @AgentID) AND (PC.ContactID IN (0, 1, 23))
SELECT DISTINCT TOP 25 ASR.Community
FROM dbo.AreaServedBy ASR
INNER JOIN AreaServedToOffice ASO
ON ASR.CommunityID = ASO.CommunityID
INNER JOIN AgentOfficeMAP POM
ON ASO.OfficeID = POM.OfficeID
WHERE (POM.AgentID = @AgentID)
SELECT DISTINCT TOP 25 ZCUSPS.Zip
FROM dbo.ZipToCityUSPS ZCUSPS
INNER JOIN CityToOffice CTO
ON ZCUSPS.CityID = CTO.CityID
INNER JOIN AgentOfficeMAP POM
ON CTO.OfficeID = POM.OfficeID
WHERE (POM.AgentID = @AgentID)
SELECT Slogan = LEFT(PP.ProfileText, 60
FROM dbo.AgentProfile PP
WHERE (PP.AgentID = @AgentID)
SELECT stgTLP.Email_Address
FROM dbo.stgTL_Agent stgTLP
WHERE (stgTLP.AgentID = @AgentID)
-- Get Agent URL from LookupLink table
SELECT LL.LinkURL
FROM dbo.LookupLink LL
INNER JOIN AgentLink PL
ON LL.LinkID = PL.LinkID
WHERE (PL.AgentID = @AgentID)
End
GO
-
Hi again...
As i can see you recover just a few fields from each table in your stored proc... so... where are this values going?? I mean, what are you doing with the retrieved data? Do you store it in some table? If so, then you can check against that table to see what have been changed... if you don't have such table, you could create one... I have used something very similar, but since we need to maintain an historic, here was no problem because the historic table already existed...
If you don't need the historic tables, so you only need to maintain the last changes just to be able to check for changes... well, the first time you run the process with an historic, it would be expensive because it would need to populate the historic with all the data... but subsequent diferential retrieves would be low cost... just need to check if actual fields are different from the last retrieved... if so, then retrieve again and update the historic wuth new value, if historic value is same than actual, skip. (If you decide to maintain ALL historic values, you'll need to add version info, like a version number and/or updateDateTime)
Hope it help you!Nicolas
-
This is very obvious I think, but you'll need to maintain an historic for each result set retrieved...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply