Any suggestions on how to optimize a query written in Dynamic SQL?

I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.

ddave
---------
SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) 'Be nice and post the results of:

PRINT @.StrQry1

...so we can focus on the SQL and not the dynamic concatenation...|||Sorry it took so long. I had to modify the procedure to print that string and run it again:-). These are the fields. It is confidential medical data so I can't put the actual data here. The procedure is below. Also if there are any obvious errors any tips would be greatly appreciated.

Counter
SubsidyLevel
MEMBID
PATID
SUBSSN
AIDSDATE
AIDCODE
OPFROMDT
OPTHRUDT
OPT
CURRENTCOUNTYID
LASTNM
FIRSTNM
BIRTH
HCP1NUM
SEX
HCC
HCCName
CaseMgrID
CaseMgrName
PCPFROMDT
PCP
PCPName
STREET
STREET2
CITY
STATE
ZIP
PHONE
CURRHIST
INTLZIP
TransferOut
CoPay
CoPayEffDate
PartsABD

-- The entire code is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter PROCEDURE DBO.STANDARD_MEMBERSHIP_QUERY_test_20071015

@.StartDate DateTime, @.EndDate DateTime,
@.HPlan varchar(20), @.HCC varchar(3),
@.Prvdr varchar(20), @.CaseMngr varchar(5),
@.Report smallint

AS

SET NOCOUNT ON

BEGIN

DECLARE @.SDate varchar(10), @.EDate varchar(10),
@.TDate varchar(10)

SET @.SDate = Convert(varchar(10),@.StartDate, 101)
SET @.EDate = Convert(varchar(10), @.EndDate, 101)
SET @.TDate = '06/06/2079'

DECLARE @.SDateP varchar(10), @.EDateP varchar(10)
DECLARE @.MySQL varchar(1600), @.StrQry1 varchar(800), @.StrQry2 varchar(800)

SET @.SDateP = Convert(varchar(10), DateAdd(Month, -1, @.StartDate), 101)
SET @.EDateP = Convert(varchar(10), DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Cast(@.SDateP AS datetime))+1, 0)), 101)

SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) '

SET @.MySQL = @.StrQry1

END

EXEC (@.MySQL)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||also and in the meantime I am studying how to index the view in question. I have rarely worked with views and I have never created an index before.

ddave|||One more time...
Be nice and post the results of:

PRINT @.StrQry1

...so we can focus on the SQL and not the dynamic concatenation...|||to do what blindman is asking, change your EXEC(@.MySQL) to PRINT @.MySQL and post the result.|||You should try to avoid sub queries.

SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT JOIN dbo.MEMB_LISHISTS Q1
on l.LISThruDate is null
AND l.Deleted = ''0'' and VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) '

I also suspect that massive index/table scans causes your problem.

Please post the execution plan for the query.
How to:
print @.StrQry1
copy the SQL to a new query windows.
Execute
set showplan_text on
go
your query here...

Copy and paste the text from the result window to this thread.|||Actually we resolved it by altering the view. We needed to add one field and we just added it to the view instead of going through all this. Thanks anyway.

ddave|||the word dynamic in the term dynamic sql almost makes it sound like a good thing. it deserves a name more akin to it's nature. maybe kludged sql? maybe poor perfroming insecure code?|||Perhaps "Injectable SQL"? Or maybe "Objectional SQL"?

It has its uses, but buy can it be abused.|||Why are you using dynamic sql ?, I usually only use dynamic sql if table names need to be assigned dynamically based on certain conditions. Where do you append the "%" for the likes, as part of the input variable ?|||That's when I use it also to dynamically name tables for example. I got it that way from my supervisor. Sigh, I guess I will just have to blame him. :)

ddave|||And when you have to use dynamic SQL (or injection-sql) do NOT use do it by concatenating the parameters into the sql string. Use sp_executesql with parameters.