Support Fail / Resume in Extraction pipelines

[tznind]

See Teams discussion:

[10:06] Christopher Hall (Staff)
    the problem is it's not about whole extractions, it's about 1 or 2 datasets that we know to execute separately. 
Appreciating that batching is difficult / not a solution, when 1 single extraction working on it's own doesn't complete after 14 hours, 5 nights in a row, even having it run at 50% speed, but doing something in those 14 hours would be quicker..
​[10:06] Thomas Nind (Staff)
    Just dont set it so high you run out of memory on your VM smile
​[10:07] Thomas Nind (Staff)
    Are you able to see where the bottleneck is Chris? is it the query execution time on the server? 
​[10:08] Thomas Nind (Staff)
    Are there any changes you can make to the query manually that cause it to complete faster, if so we can look at those and see if it's possible to integrate those optimisations into the RDMP pipeline source that builds the query to execute
​[10:11] Thomas Nind (Staff)
    
​[10:12] Thomas Nind (Staff)
    You can also try turning on Hash Joins and/or switching to the OrderBy approach to distincting, that should prevent the SqlServer having to prepare the entire result set before releasing records to the consuming source
​[10:12] Magalie Guignard-Duff (Staff)
    it's still running (was blocked all night by CHI control pop up) - if if fails, I will try that
​[10:13] Thomas Nind (Staff)
    Interactive popups in extraction pipeline components are bad sad
​[10:14] Thomas Nind (Staff)
    I wonder if that chi checking plugin should instead stash a report about the CHIs found and present that as a warning/error at the release pipeline stage?
​[10:15] Christopher Hall (Staff)
    we have a pipeline with no CHI checker and Hash joins set up for this reason
[10:16] Magalie Guignard-Duff (Staff)
    I quite like the idea of a report but we would need to go back to the file and change manually any necessary - CHI check is here for a reason
​[10:16] Zsolt Szarka (Staff)
    what was the column name? We have exceptions for CHI checker
​[10:16] Magalie Guignard-Duff (Staff)
    even though on big extractions it's pretty painful to deal with as it stands
Edited​[10:16] Christopher Hall (Staff)
    realistically, it's most probably down to the server performance than anything else, but that doesn't mean we shouldn't think about how RDMP could potentially help, and batching an extraction is always the automatic first thought as you end up with something at the end when it crashes for whatever reason
​[10:18] Christopher Hall (Staff)
    (of course, killng the chi checker isn't really what we want to do, equally, running the extraction completely manually, not via RDMP, which is generally more successful, also misses out on many of the features in RDMP that are valuable)
​[10:18] Thomas Nind (Staff)
    
So I think the only way to do batches on the sql server would be using offset
ORDER BY dataset.CHI
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY


Edited​[10:24] Zsolt Szarka (Staff)
    Can we store the @Skip variable, so we can resume from there if things fail?
​[10:25] Thomas Nind (Staff)
    Yes, but downstream components in the pipeline would run into trouble though.  For example the flat file destination component currently outputs the contents of the pipe to the CSV file replacing whatever was there (if anything) - it doesn't append the data.
​[10:26] Zsolt Szarka (Staff)
    is there a reason for not to append?
​[10:26] Thomas Nind (Staff)
    Also any middle components that assume they are seeing all the data that is going to be extracted in one go are going to be disapointed (e.g. something that generates a report about the data extracted).
[10:26] Thomas Nind (Staff)
    Yes, if your extraction failed then you want to replace the current file when you re-run the extraction.  
​[10:27] Zsolt Szarka (Staff)
    that is not always the case I think. sometimes we would like to resume, rather than replace

​[10:29] Thomas Nind (Staff)
    Yes that's what we are discussing.  Currently there is only atomic pipeline execution.  All pipeline components see all the data.  Once we say that a pipeline can fail and then run it again 2 or 3 times to get the rest of the data out it means all pipeline components must be aware that that's a possibility and be programmed to handle it
​[10:29] Zsolt Szarka (Staff)
    Could the middle components be executed at the end. E.g: the report would be generated by examining the output file
​[10:31] Thomas Nind (Staff)
    We have a discussion already on making the DLE resume from STAGING (https://github.com/HicServices/RDMP/discussions/558).  I will create a discussion for this too since it is too big a task for an issue at the moment.  Its more like a project 
(1 liked)Include 'RunAfterStaging' option in RDMP · Discussion #558 · HicServices/RDMPIs your feature request related to a problem? Please describe. When a data load fails after loading RAW (a long time e.g. 100 million records) Describe the solution you&#39;d like The ability t...github.com<https://teams.microsoft.com/l/message/19:[email protected]/1635499601525?tenantId=ae323139-093a-4d2a-81a6-5d334bcd9019&amp;groupId=573173a6-9283-4ddc-a901-c9dbe2a2f274&amp;parentMessageId=1635437866785&amp;teamName=HIC&amp;channelName=Data&amp;createdTime=1635499601525>