How to OPTIMIZE the performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to OPTIMIZE the performance

Author

Topic

GautamRy
Starting Member

2 Posts

Posted - 2010-10-16 : 06:17:11

Hi
I need your precious commements on the following issue.

We need to display customer information in 3 indipendent tables in the same page of UI(User Interface). The input parameter to the Stored procedure are @policyNumber or @callerName or @contactNumber.

Ideally, we need to write three Stored Procedures to handle the above situation.
But after analyzing, I found that we are calling the same procedure 3 times with three different input parameter.
Now, I want to call a single procedure instead of three where all the three parameters are handled.
I thought to use temporary table to handle so. My Query is below

For @callerName

select csext_id1 as CALLERNAME,csext_id2 as CONTACTNUMBER,csext_id12 as POLICYNUMBER,FROM tblCases cs,tblCaseCustomAttributes ca,tblProcess pr WITH (NOLOCK) , tblCaseHistory cshy WITH (NOLOCK),
dbo.tblteam tm WITH (NOLOCK), tblProcess subpr (NOLOCK)
WHERE cs.cs_cid = ca.csext_cs_cid AND cs.cs_cid *= cshy.cshy_cs_cid AND cs.cs_pr_pid = pr.pr_pid
AND tm.tm_tid = pr.pr_tm_tid AND cs.cs_sub_pr_pid *= subpr.pr_pid AND csext_id1 IN (''' + REPLACE (@callerName , ',' , CHAR(39)+','+CHAR(39) ) + ''')

For @policyNumber
Same Query but csext_id1 IN (''' + REPLACE (@callerName , ',' , CHAR(39)+','+CHAR(39) ) + ''')

replaced by
csext_id2 IN (''' + REPLACE @policyNumber, ',' , CHAR(39)+','+CHAR(39) ) + ''')

And Same approach for
@contactNumber
Please, let me know How I can optimize.

Regards
Gautam

Sachin.Nand

2937 Posts

Posted - 2010-10-16 : 08:00:43

But will the values for the parameters be always passed?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 02:41:28

actually you can use something like


select csext_id1 as CALLERNAME,csext_id2 as CONTACTNUMBER,csext_id12 as POLICYNUMBER,FROM tblCases cs,tblCaseCustomAttributes ca,tblProcess pr WITH (NOLOCK) , tblCaseHistory cshy WITH (NOLOCK),
dbo.tblteam tm WITH (NOLOCK), tblProcess subpr (NOLOCK) 
WHERE cs.cs_cid = ca.csext_cs_cid AND cs.cs_cid *= cshy.cshy_cs_cid AND cs.cs_pr_pid = pr.pr_pid 
AND tm.tm_tid = pr.pr_tm_tid AND cs.cs_sub_pr_pid *= subpr.pr_pid 
AND ( ',' + @callerName + ',' LIKE  '%,' + CAST(csext_id1 AS varchar(20)) + ',%' OR @callerName IS NULL)
AND ( ',' + @policyNumber + ',' LIKE  '%,' + CAST(csext_id2 AS varchar(20)) + ',%' OR @policyNumber IS NULL)
AND ..

so that you dont need separate procedures nor do you need to use dynamic sql
Only thing is you need to set default values of parameters as NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources