July 4, 2018 at 2:09 pm
We currently have a payment tracking system which uses MS SQL Server Enterprise. When a client requests a service, he would have to do the payment within 24 hours, otherwise we would send him an SMS Reminder. Our current implementation simply records the date and time of the purchase, and keep on polling constantly the records in order to find "expired" purchases.
This is generating so much load on the database that we have to implement some form of replication in order to offload these operations to another server.
I was thinking: is there a way to combine CLR triggers with some kind of a scheduler that would be triggered only once, that is, 24 hours after the purchase is created?
Please keep in mind that we have tens of thousands of transactions per hour.
July 5, 2018 at 6:46 am
Why would you constantly be polling? Why not setup a job to run once an hour or 2 to look for these conditions? Then modify the payment process to update what ever needs to be updated so that record doesn't show in the job polling.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2018 at 8:47 am
Mike01 - Thursday, July 5, 2018 6:46 AMWhy would you constantly be polling? Why not setup a job to run once an hour or 2 to look for these conditions? Then modify the payment process to update what ever needs to be updated so that record doesn't show in the job polling.
Which is fair. Another possibility is the creation of a separate and well-indexed 'SMS queue' table.
A service request generates a row in the table (including the datetime of the request).
A payment received removes the corresponding row in the queue table.
A process runs periodically, finds rows where service date < getdate() - 1, sends the SMS reminders for the found rows and removes those rows
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 5, 2018 at 9:31 am
Phil Parkin - Thursday, July 5, 2018 8:47 AMWhich is fair. Another possibility is the creation of a separate and well-indexed 'SMS queue' table.A service request generates a row in the table (including the datetime of the request).
A payment received removes the corresponding row in the queue table.
A process runs periodically, finds rows where service date < getdate() - 1, sends the SMS reminders for the found rows and removes those rows
This is what I might do. Gives me a separate tracking area, and potentially a place where I can analyze if there is any way to improve the system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply