DPL-047 Use stored procedure to retrieve destination plate information

[pjvv]

User story
As a development team we would like to change how we query the DART database to retrieve the destination plate information. We are using currently using a view but the view is not operating efficiently and can lead to blocking issues. A stored procedure should operate most efficiently and provide the same, required output.

Who are the primary contacts for this story
@pjvv
@andrewsparkes

Acceptance criteria
To be considered successful the solution must allow:

• call a stored procedure instead of the view

Dependencies
This story is blocked by the following dependencies:

• #<issue_no.>
• sanger/#<issue_no.>

References
This story has a non-blocking relationship with:

• #<issue_no.>
• sanger/#<issue_no.>

Additional context
Yann will be assisting from the DBA team to convert the view to a stored procedure and help us optimise the interactions between LIMS and SQL Server.

[harrietc52]

Update: Not sure this story is any longer required

[TWJW-SANGER]

Eduardo and I have just had a meeting with Yann L. who re-configured the queries underlying the two views in the production DART database as a hot fix:
view_run_history - used by Sara Stott’s team through Tableau in 2 reports
view_heron_hitpicks – used by LIMS

With these changes the performance has increased from more than 3 mins from Tableau to less than 15 seconds, he has also taken steps to prevent the performance degrading with more data.

We do need to be careful though - so next steps will be

• Discuss with Sara S on how to gradually test use of Tableau reports during the day
• Monitoring the time the queries / reports take to run - to catch any further problems earlier

Hopefully a stored procedure won't be needed if performance remains acceptable to avoid more production hot-fixes.

[TWJW-SANGER]

Sara is currently testing the changes Yann made to the DART database - expecting feedback week starting 9th August

This issue should be considered On Hold

Longer term she is looking to get a combined report for Biosero & Beckman robots - this is captured in DPL-095.

[TWJW-SANGER]

Sara has tested the Tableau report and unfortunately it is still taking minutes to render, so the test has failed.
This is odd as the underlying view is much faster following Yann's improvements.

The next step is to work out with Scott what's required to do DPL-095 and then decide whether this story should go ahead as a temporary fix, or it's just better to design out the problem and go with DPL-095.