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.

Any reliable tool free?

guys,
I am looking for any Sql monitoring tool more or less reliable and free for
evaluation.
Does anyone knows...
Regards,> I am looking for any Sql monitoring tool more or less reliable and free
> for
> evaluation.
> Does anyone knows...
Profiler comes with SQL Server, so it is for free.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hey
Assuming you have windows 2k + you can also use the for monitoring IO and
other SQL items with administrative tools.
HTH
"Dejan Sarka" wrote:

> Profiler comes with SQL Server, so it is for free.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Any load testing tool to test queries

Guys,

Is there any tool that can test the scalability of Analysis Services?

Basically we want to fire multiple queries to the Analysis Services and check if there is any performance degradation.

Is there any utility or tool that I can use?

Rgds

Hari

SQL Performance monitor works with SSAS too.|||

The Community samples site has an ASLoadSim tool listed which might be worth a look http://www.codeplex.com/SQLSrvAnalysisSrvcs

ANSWER: Re: SQLEXPRESS Database Issue - Cannot open database ASPNETDB.MDF requested by the login

Hello Guys

This is my connection string

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>

I tried to research on the internet and i got a solution on changing the permissions for this database to enable user SystemName/ASPNET, but iam not able to access this ASPNETDB.MDF from SqlServer and if i go to server explorer in vs2005, i dint know where to chage the permissions.

Can anyone help me on this.

Thanks a lot

The ASPNET account needs to have access both to the server and to the ASPNETDB database. It seems you have granted server access to the account, but not database access.

To grant database access, connect to SQL Server using Management Studio and execute the following SQL statements:

use ASPNETDB
go
create user [SystemName\ASPNET]
go

Thanks
Laurentiu

|||

hi,

Thanks for your info abt the adding of aspnet account in the ASPNETDB Db.

I have created a website using VS 2005, and my SQL Server 2005 -STD Edition(NOT SQLEXPRESS). When ever i try to connect to the database i get this following error : Cannot open database ASPNETDB.MDF requested by the login, login failed. Login failed for user DOMAIN|USERNAME.

FYI, I have added my DOMAIN\username in the Security - Logins of the Database. I have also done the user mappings. That screen reads as follows :
1. Master Database -

Still I get this error. Please help me understand that whether any other permissions is required in the database for me.

Thanks

krans001

|||

hi,

I get this error when i try to execute a website just having a DEFAULT.aspx in which i have added a webpartsmanager. the personalization property of the webpartmanager is set to TRUE and USER. The following steps I did:

1. Installed the Microsoft SQL Server Management Studio 9.00.1399.00 ( STANDERD EDITION).

2. from the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 installed the aspnet_regsql and followed the steps in installing the aspnetdb database.

In the usermappings i have provided permissions to me "DOMAIN\USERNAME" to the following db: 1. master 2. msdb 3. tempdb 4. aspnetdb

3. in the edit global configuration removed the USER INSTANCE=TRUE from the following
Data Source=.\MSSQLSERVER2005;Initial Catalog=aspnetdb.mdf;Integrated Security=True

4. edited the machine.config file under the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
as
<add name="LocalSqlServer" connectionString="Data Source=.\MSSQLSERVER2005;Initial Catalog=aspnetdb.mdf;Integrated Security=True" providerName="System.Data.SqlClient" />

After doing this i tried to execute the website that i have created which holds a default.aspx page in which i have the webpartmanager. I just wanted to view this file in the browser. As soon as i do this i get this error.

Cannot open database "aspnetdb.mdf" requested by the login. The login failed. Login failed for user 'DOMAIN\USERNAME'.

Thanks in advance for the solution. Please help.. if anyone has a solution please send it immediately to my email id : sriranga.kr@.gmail.com

|||

hi

all i did was removed the .mdf in the connection string and run the same. It works like a charm..

great...

|||

hi sreeksdave

all you need to do is to change the following :

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>

AS

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; Initial Catalog= ASPNETDB" providerName ="System.Data.SqlClient"/>

and see how it works..

Do let me know..

Regards

scotty

|||I think this should be rather aspnetdb than aspnetdb.mdf, if you use a user instance you have to specify the filename but with attached database you can just type in the name of the database.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Remember that both files ASPNETDB.MDF and aspnetdb_log.LDF are in a folder call App_Data in you project solution, just make sure that you give the right access privileges to your local MACHINE\ASPNET account

Hope this will help

|||

Hi all,

Thanks for all your contribution. It has worked for me and i have also helped few people.

Thanks & Regards

Raaj

|||

Hello Ranga,

Thanks a lot yar.

Hats off