SQL Select with a join takes too long, any alternative

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)
                         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 (
  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?


hope these links helps :slight_smile: :slight_smile:

So the issue happens when you implement the notes field query?

I made a Scaler Valued function , and called it from the SP, the results took 11 seconds.

Yes, 2 million notes to go through. I done it!

1 Like

So painful..

Have to take so so so many notes

If there was some very simple easy way
To take notes ...I mean get to the point
Directly from very very few notes..

I face this many many many notes situation
A lot. ....