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
 Transact-SQL (2000)
 Is this stored procedure optimised ?

Author  Topic 

sudhan
Starting Member

6 Posts
Posted - 2007-02-24 : 07:25:26
Hi i am using following stored procedure on MS SQL 2000 server .
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:31:31
Why are you using dynamic SQL ?

If it is only for the TOP <page size>, you can do this

declare @pagesize int
select  @pagesize = 10
set     rowcount pagesize
select  * from yourtable
set     rowcount 0


And the queries are all very similar. You should be able to combine all into one.

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-24 : 10:25:31
And using TOP keyword has no meaning without an ORDER BY.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2007-02-24 : 15:14:06
1) Use the ELSE IF construct to bypass extraneous branches
2) From a readability/maintenance stand point.. create another variable (@TSQL) that contains the dynamic sql string and have only the one single EXEC function as the last statement.

DavidM

Production is just another testing cycle
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts
Posted - 2007-02-26 : 08:03:46
IIRC the first time the SP runs the query optimiser determines the execution plan for the first Select thats run . . . If its running a different select the second time then it discards the stored query plan and recompiles it.

The rule I give my developers "One stored procedure, One SQL Statement" (any more than that and they get really confused

The other thing you can do is to use

SET ROWCOUNT = @PageSize

[Your Query]

SET ROWCOUNT = 0

Instead of the Select TOP @PageSize statement

--
Regards
Tony The DBA
Go to Top of Page

sudhan
Starting Member

6 Posts
Posted - 2007-02-26 : 08:40:28
hi
thanks for reply, whatever you suggest its fine & i will give it try
BUT
When i check in more detail then found that in each query i am using " NOT IN "
when topValue in inner query increased more than 1 lac then it will very very slow.
So now i want to remove NOT IN with IN or have to use CURSUR so how can i do that?
and if i rplace NOT IN with NOT EXIST then also it will doing same --like 1st it will execute inner query and then it will compare primary key whether it is present in ids obtained in inner query .
am i right?

thaks in advance...
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-02-26 : 08:51:33
I think if you convert NOT IN to NOT EXISTS operator, you don't need TOP clause at all and neither dynamic sql also.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sudhan
Starting Member

6 Posts
Posted - 2007-02-26 : 09:04:00
when i run sample query given below then it giving syntax error -
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXISTS'.

select top 50 * from device_placeholder where deviceid
NOT EXISTS(select top 20000 deviceid from device_placeholder)
Go to Top of Page

sudhan
Starting Member

6 Posts
Posted - 2007-02-26 : 09:06:27

missed to mentin i m using MS SQL server 2000 !!
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-02-26 : 09:08:29
I don't get your sample query? You are trying to select deviceids from device_placeholder table which are not in device_placeholder table itself ??? What does that mean?

Anyway your use of EXISTS operator is wrong syntactically:

Select * from t1
Where not Exists (select * from t2 where t2.col = t1.col)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sudhan
Starting Member

6 Posts
Posted - 2007-02-26 : 09:27:57
i am implementing this query for pagination and meaning of my qyery is
for each request it will fetch the next record set (of size equal to outer query top value) which was not present in previous record set of same query.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-26 : 09:38:10
Pagination ? Try using the "The SQL Search" at the top of this page.
http://www.sqlteam.com/search3.asp?cx=011171816663894899992%3Aaow51lf_dim&q=pagination&sa=Search&cof=FORID%3A9

Here are some of the result
http://weblogs.sqlteam.com/randyp/archive/2005/06/23/6335.aspx
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-26 : 10:04:26
[code]CREATE PROCEDURE cmdb_device_Paging
(
@Criteria VARCHAR(15),
@CustID VARCHAR(20),
@Page INT,
@PageSize INT
)
AS

SET NOCOUNT ON

DECLARE @Paging TABLE (DeviceID INT, RecID INT IDENTITY(0, 1))

INSERT @Paging
(
DeviceID
)
SELECT dev.DeviceId,
FROM Accounts AS ac
INNER JOIN Device_PlaceHolder AS dev ON dev.SiteID = ac.SiteID
WHERE ac.CustID = @CustID
AND 1 = CASE
WHEN @Criteria = 'device' THEN 1
WHEN @Criteria = 'managedDev' AND dev.operation IN ('MAD','MS') THEN 1
WHEN @Criteria = 'unManagedDev' AND dev.operation = 'U' THEN 1
WHEN @Criteria = 'IgnoreDev' AND dev.operation = 'I' THEN 1
ELSE 0
END
ORDER BY dev.DeviceId

DELETE
FROM @Paging
WHERE RecID / @PageSize <> @Page - 1

SELECT dev.DeviceId,
dev.Address,
dev.DeviceType,
dev.MI,
dev.DeviceName,
dev.Hardware,
dev.Software,
dev.Community,
dev.operation
FROM Device_PlaceHolder AS dev
INNER JOIN @Paging AS p ON p.DeviceId = dev.DeviceID
ORDER BY dev.DeviceId[/code]
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2007-02-28 : 00:23:06
The code the orginal requestor posted looks like GUI "paging" code... especially in light of the NOT IN. In the presence of correct indicies, it'll be lightning quick even though it's dynamic SQL.

It's been my experience that NOT IN's are a wee bit faster than outer joins with Null detects... haven't tried the NOT EXISTS because that involves a correlated subquery. Could be very fast if indexed correctly but I generally try to avoid correlated subqueries that work as RBAR.

--Jeff Moden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site