Hello all -
So the last few days our localized "update ticket history" table procedure has taken over a day and half to update our history table. So... I know the proc is working at least! But a proc that runs for 1 day, 12 hours + doesn't do us any good with as many exchanges we have.
The localization we have is two fold and it's cosmetic only: We like our seat location to read "Sec: 1, Row A, Seats 101,102" rather than the Tessitura standard of (what I believe to be) '1 A 102,103'. to get that, all i did was copy the FT_HISTORY_LOCATION and add in the text for "Sec " and "Row ".
The second localization is simply the TP_UPDATE_TICKET_HISTORY proc modified to pull it's location data from the localized Function Table rather than the standard Function Table.
So maybe it's pulling too much data? We do have 6 seasons running with a 14 month date range (to catch the earliest of sales). So the questions here are:
- Do you break out your jobs to one for each season?
- How many days back do you go? We used to go 30 but I'm cutting it to 10 now that the proc is taking so long in hopes that helps
- How long does your proc usually take?
Any input is appreciated so i can see if I'm just way off the course or if I need to mod these jobs from here on out to go by season individually. Thanks!
RJ
How many rows are we talking about? I'm not super familiar with the proc (I think we use the default), but I've never seen performance like that unless there was a loop involved.
Richard, are you a RAMP client? We recently had a strange case of our nightly ticket history job taking 10 hours instead of 2-3 minutes, and don't currently have any customizations in place (it's just the standard TP_UPDATE_TICKET_HISTORY running for 4 different values of season_no). We brought it up in a TASK ticket we already had going about nightly jobs - they marked TP_UPDATE_TICKET_HISTORY, AP_MAINTAIN_CONSTITUENCY, and FT_HISTORY_LOCATION for recompilation and the issues seemed to clear up the next night. So there's a chance it may be some sort of bugbear that's unrelated to the customizations you've got.
We're not RAMP, but if the procs have not been updated since we customized ours, maybe I just need to take whatever the latest version of those procs are and "recustomize" them (i.e. copy and change a few text fields). I'll give it a shot on my Test database and see what happens. Thanks for that!
I'm not sure about the exact rows, but some simple math says: 8 performances a week in a 770 seat house is roughly 6160 rows and that shouldn't take 36 hours I wouldn't think.
What version of Tessitura are you using? 12.5.1, 14.0.x, 14.1.x, 15.0.x?
We're currently on 14.1.3
I've got no direct experience with 14.1.3. We are moving directly from 12.5.1 to 15.0.2 this weekend.
Have you tried running the update procedure manually in SSMS with the parameters that are usually being used? What could potentially be happening here is that a bad plan has gotten stuck in the plan cache for this procedure that is causing SQL server to use join types that are not optimal for typical runs of the procedure.
If you do see the code only taking long to run when part of the typical procedure, you may have a parameter sniffing or plan cache issue. "Slow in the Application, Fast in SSMS?" is a masterclass in how these issues work. You may also want to grab the Brent Ozar First Responder Kit and run sp_BlitzCache to find the execution plan being used by your query. BlitzCache can show you what command to run to purge just that cached plan from the database (instead of purging the ENTIRE plan cache, which will slow things down until the server has had a chance to recompile everything that was previously in the cache -- this is equivalent to what happens to the plan cache after a restart).