Is this stored procedure optimised ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Is this stored procedure optimised ?

Author

Topic

sudhan
Starting Member

6 Posts

Posted - 2007-02-24 : 07:23:27

Hi i am using following stored procedure on MS SQL 2000 server & also on MS SQL 2005
Its working fine.
Now for better performance (related to execution speed , memory , recompilation problems)
Is this stored procedure optimized or needs to be optimize?
if it require to write in more better manner how can i modify?
is any suggestions?
thanks in advance...


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmdb_device_Paging]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cmdb_device_Paging]
GO
CREATE PROCEDURE cmdb_device_Paging
 (
    @criteria varchar(15),
	@custID varchar(20),
	@pageSize varchar(5),	
	@topValue varchar(5)
		
 )
 AS 
SET NOCOUNT ON
if @criteria = 'device'
begin
exec  (
	 'SELECT top '+@pageSize+ ' dev.DeviceId,dev.Address,dev.DeviceType,dev.MI,dev.DeviceName,dev.Hardware,
	dev.Software,dev.Community,dev.operation  FROM Accounts ac INNER JOIN Device_PlaceHolder dev 
	ON ac.SiteID = dev.SiteID AND ac.CustID = '''+@custID+'''' +' where dev.DeviceID not in( SELECT top '+@topValue+ 
    ' dev.DeviceId FROM Accounts ac INNER JOIN Device_PlaceHolder dev ON ac.SiteID = dev.SiteID 
      AND ac.CustID = '''+@custID+''''+'  )'
      )     
end

if @criteria = 'managedDev'
begin
exec  (	  
	  'SELECT top '+@pageSize+ ' dev.DeviceID ,dev.Address ,dev.DeviceType ,dev.MI,dev.DeviceName ,dev.Hardware ,dev.Software
	 ,dev.Community ,dev.Operation FROM Accounts ac INNER JOIN Device_PlaceHolder dev ON ac.SiteID = dev.SiteID AND 
	  ac.CustID = '''+@custID+'''' +' AND dev.operation IN('+'''MAD'''+','+'''MS'''+')'+' where dev.DeviceID not in ( SELECT  top '+@topValue+
	 ' dev.DeviceID FROM  Accounts ac INNER JOIN Device_PlaceHolder dev ON ac.SiteID = dev.SiteID 
	  AND ac.CustID = '''+@custID+''''+' AND dev.operation IN('+'''MAD'''+','+'''MS'''+')'+ ')'	
      )  			
             
end

if @criteria = 'unManagedDev'
begin
exec  (	  
	  'SELECT top '+@pageSize+ ' dev.DeviceID ,dev.Address ,dev.DeviceType ,dev.MI,dev.DeviceName ,dev.Hardware ,dev.Software 
      ,dev.Community ,dev.Operation FROM Accounts ac INNER JOIN Device_PlaceHolder dev ON ac.SiteID = dev.SiteID  AND 
      ac.CustID = '''+@custID+'''' +' AND dev.Operation = '+ '''U'''+'  where dev.DeviceID not in ( SELECT  top '+@topValue+
	  ' dev.DeviceID FROM  Accounts ac INNER JOIN Device_PlaceHolder dev  ON ac.SiteID = dev.SiteID  
       AND ac.CustID = '''+@custID+'''' +' AND dev.Operation = '+ '''U'''+' )'
     )
end

if @criteria = 'IgnoreDev'
begin
exec  (
	  'SELECT top '+@pageSize+ ' dev.DeviceID ,dev.Address ,dev.DeviceType ,dev.MI,dev.DeviceName ,dev.Hardware ,dev.Software 
      ,dev.Community ,dev.Operation FROM Accounts ac INNER JOIN Device_PlaceHolder dev ON ac.SiteID = dev.SiteID  AND 
      ac.CustID = '''+@custID+'''' +' AND dev.Operation = '+ '''I'''+'  where dev.DeviceID not in ( SELECT  top '+@topValue+
	  ' dev.DeviceID FROM  Accounts ac INNER JOIN Device_PlaceHolder dev  ON ac.SiteID = dev.SiteID  
      AND ac.CustID = '''+@custID+'''' +' AND dev.Operation = '+ '''I'''+' )'

      )
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-24 : 07:28:58

duplicate post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79622

KH

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-27 : 06:14:47

does this proc is taking more time than expected, to execute? or apart from this do u want to apply something else, even ur proc is executing properly?

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources