AnsweredAssumed Answered

Slow search

Question asked by cschmidt on Feb 12, 2019
Latest reply on Feb 13, 2019 by cschmidt

This is my search to populate a number on a status board, however, it is taking 15 seconds to load 207 records, can anyone suggest a more optimal way to do this? I'm using a Calculation fields...the user loads the status board and this will tell them how many of the tasks are in that particular bucket of "To do". We then use a separate script that when they click on that number, it will load the records. I'm working on optimizing the sql query, but if anyone has a better way than the executesql, I'm happy to try it.

 

ExecuteSQL (

"SELECT COUNT(T.UUID_Task)

FROM Orders_Tasks T

LEFT JOIN Orders_Tasks T2

ON T.UUID_Parent_Task = T2.UUID_Task

LEFT JOIN Orders_Tasks T3

ON T.Dependancy_Task = T3.Task AND T.UUID_Orders = T3.UUID_Orders

JOIN Orders O

ON T.UUID_Orders = O.UUID_Orders

WHERE (T2.QC_Status = ? OR T2.QC_Status = ? )

AND (T.Dependancy_Task = ? OR T3.QC_Status = ? OR T3.QC_Status = ? )

AND T.Archive_Type = ?

AND T.General_Library_Type = ?

AND T.QC_Status IS NULL

AND ( T.Sample_Subgroup = ? OR T.Sample_Subgroup = ? )

AND ( O.\"Test Status\" <> ? AND O.\"Test Status\" <> ? )

" ;

"" ;

"" ;

"Pass" ;

"Pass with Exception" ;

"N/A" ;

"Pass" ;

"Pass with Exception" ;

"NGS Library" ;

"Genome" ;

"Tumor" ;

"Constitutional" ;

"Review Required" ;

"Awaiting Specimen";

 

 

)

Outcomes