A project i am working on processes a lot of gift cards, these gift cards are kept in sync with another system that also provides gift cards that the project imports every 15mins in a background job.
On the production environment payments are intermittently very slow and after profiling I can see that gift cards are attempted to be pulled from cache and if that cache doesn't exist it will get all from the DB.
Profile stacktrace:
I believe this is causing the issue whereby the gift card import invalidates the cache and then it pulls all gift cards from the DB(currently 73,000 rows in production).
I have looked into replacing the gift card service with my own implementation however when using:
It still uses the default service, so i assume this is not possible?
I'm currently looking into implementing a job to archive gift cards from the DB but im not sure if this will have any other effect?
Potentially archive any gift card that has zero remainingAmount or a deleted timestamp, however im not sure if GiftCardRepository.GetAll() already covers that?
Any suggestions or guidance would be very appreciated.
Cheers,
Greg
It does indeed look like we attempt to hold old Gift Cards in memory, which in hind-sight might have been best to implement this in a similar way as with orders which only holds active items which are then cleared after a period of inactivity.
I can defo look into changing this to work in this way, but it kind of depends which version of Vendr you are using and whether you can upgrade.
Another potential bottleneck is our use of Guids which in v1 of Vendr were just pure Guids, but in v2 we've now switched to COMBs in order to allow more effective DB indexes. Unfortunately though, if you are coming from a v1 install switching to COMBs now isn't really going to help that much as you will already have a whole bunch of records with normal Guids. That said, adding some DB indexes might still help, it just might mean you have to rebuild them more often as they get defragemented.
RE swapping out the Gift Card Service, yea, this won't work as these are fundamental services and so aren't intended to be swapped out.
You can probably add some indexes on the various ID columns to help with lookups. Like I say though, because these are Guids they may need rebuilding fairly regularly.
The main thing though really is the volume. If you don't need them hanging around, you could create a historic gift cards table and just copy them over to that table so that you real table only holds active gift cards.
Going through their prod environment they have about 73,000.
Just been looking into indexing the table, not sure if its the right path at the moment due to the negative impact it will have during importing additional vouchers and having to reindex whenever there is an import.
I am in talks now about reducing the number and coming up with some archiving rules, so I'll go down that route for now.
GiftCardService causing slow payments
A project i am working on processes a lot of gift cards, these gift cards are kept in sync with another system that also provides gift cards that the project imports every 15mins in a background job.
On the production environment payments are intermittently very slow and after profiling I can see that gift cards are attempted to be pulled from cache and if that cache doesn't exist it will get all from the DB. Profile stacktrace:
I believe this is causing the issue whereby the gift card import invalidates the cache and then it pulls all gift cards from the DB(currently 73,000 rows in production).
I have looked into replacing the gift card service with my own implementation however when using:
It still uses the default service, so i assume this is not possible?
I'm currently looking into implementing a job to archive gift cards from the DB but im not sure if this will have any other effect?
Potentially archive any gift card that has zero remainingAmount or a deleted timestamp, however im not sure if GiftCardRepository.GetAll() already covers that?
Any suggestions or guidance would be very appreciated. Cheers, Greg
Hey Greg,
What version of Umbraco / Vendr are you using?
It does indeed look like we attempt to hold old Gift Cards in memory, which in hind-sight might have been best to implement this in a similar way as with orders which only holds active items which are then cleared after a period of inactivity.
I can defo look into changing this to work in this way, but it kind of depends which version of Vendr you are using and whether you can upgrade.
Another potential bottleneck is our use of Guids which in v1 of Vendr were just pure Guids, but in v2 we've now switched to COMBs in order to allow more effective DB indexes. Unfortunately though, if you are coming from a v1 install switching to COMBs now isn't really going to help that much as you will already have a whole bunch of records with normal Guids. That said, adding some DB indexes might still help, it just might mean you have to rebuild them more often as they get defragemented.
RE swapping out the Gift Card Service, yea, this won't work as these are fundamental services and so aren't intended to be swapped out.
Matt
Hey Matt,
Thanks for the quick reply.
Umbraco version : 8.16.0
Vendr version : 1.8.6
As its not v2, would the best solution be to do something with the DB?
Greg
Hi Greg,
Yea, possibly.
You can probably add some indexes on the various ID columns to help with lookups. Like I say though, because these are Guids they may need rebuilding fairly regularly.
The main thing though really is the volume. If you don't need them hanging around, you could create a historic gift cards table and just copy them over to that table so that you real table only holds active gift cards.
How many active gift cards do you generally have?
Hey,
Going through their prod environment they have about 73,000.
Just been looking into indexing the table, not sure if its the right path at the moment due to the negative impact it will have during importing additional vouchers and having to reindex whenever there is an import.
I am in talks now about reducing the number and coming up with some archiving rules, so I'll go down that route for now.
Thanks for your help Matt, cheers.
is working on a reply...