Hi,
I have a stored procedue that gets case details from a SQL DB, with multpile select conditions , this works OK and powers a SSRS report.
The report is required now to join a table of Notes related to the cases, with a foriegn Key.
We need to search the Notes table for the presence of a string in a text field called "Note", and add to the final report Yes or No , if the string is present.
I have tried a Join and its still running after 10 minutes.
The select to get the Notes takes 34 seconds to run through and get 5k rows from around 2million rows.
I can get all 2 mil Notes rows in 16 seconds.
I made them both views and joined them, but still running after 10 minutes again.
I tried a stored proceudre with a nested select, it doesn't bring the whole dataset, just those cases with a yes, but takes 2.5 minutes.
SELECT value1, value2, value3, value4, value5, value6
FROM tblCases AS cd
INNER JOIN tblPro AS ref ON cd.SourceID = ref.ProspectID AND (cd.DateInstructed > '2015/08/31') AND (cd.ActualStartDate IS NOT NULL) AND ( (cd.ActualFinishDate IS NULL) AND (cd.BookingType = 2 OR cd.BookingType =12 or cd.BookingType=17)) OR
(cd.DateInstructed > '2015/08/31') AND (cd.ActualStartDate IS NOT NULL) AND (cd.ActualFinishDate IS NOT NULL) AND (cd.BookingType = 2 OR cd.BookingType =12 or cd.BookingType=17) AND (cd.DatePaymentRequested IS NULL)
LEFT OUTER JOIN
tblHiS AS hp WITH (nolock) ON cd.FK_SuID = hp.PK_SubID LEFT OUTER JOIN
VW_GroupCode AS gc ON cd.FK_ClipID = gc.PK_RowID LEFT OUTER JOIN
tblTVH AS hv WITH (nolock) ON cd.FK_HCID = hv.PK_TCID
LEFT OUTER JOIN tblBookingType aS bt WITH (nolock) ON cd.BookingType = bt.PK_BookingTypeID
where cd.PK_RefNo IN (
SELECT
[FK_CaseID]
FROM [dbo].[tblCaseNotes]
where Note LIKE '%String to search for%'
)
group by value1, value2, value3, value4, value5, value6
Is there another way of doing this please?
