Crystal Reports - Very slow SQL command
Posted on 2013-01-29
I have a SQL query that I have created within a Crystal Report (2008) but it runs very slowly, I know this is because I have written it to do a search within a field.
Here is the query:
(select (min("TAUDIT"."ENTERED")) from "TAUDIT"
where SUBSTR("TAUDIT"."ACTION",1,22) = 'Arrived event (stop 1)'
and SUBSTR("TAUDIT"."RECORD_REF",7,6) = "TJOBS"."REFERENCE")
I have tested various methods of getting the correct value for "TAUDIT"."ACTION" = 'Arrived event (stop 1)', none of them really slow the system down.
The problem is when SUBSTR("TAUDIT"."RECORD_REF",7,6) = "TJOBS"."REFERENCE" is run, it takes a really long time to calculate.
What I really wanted to do was create a query that would search for complete string, not just part of the substring.
TAUDIT.RECORD_REF is a String(25) & the values I would be looking for would be a concatenation of "tJobs " & the TJOBS.REFERENCE (Number), so a value would look something like "tJobs 257407".
However, I am struggling to write a query that uses the CAST or CONVERT to join the 2 data types so that it can be compared with TAUDIT.RECORD_REF (String(25)).
Anyone have any ideas on this matter, or have had similar problems?