locked
Top ten things NOT to do in SPROCS and Functions RRS feed

  • Question

  • User-1918385786 posted

    In an effort to always improve my knowledge of performance tuning for SQL Server 2000/2005- I'm hoping others will be willing to share their top N lists of things you should not do in tsql sprocs and user defined functions.  I'll submit some of my top no-no's but I'm hoping others can share their knowledge as well that may be more advanced than mine.


    1.  Do not use build dynamic SQL statements that cannot be cached for optimal performance plans.  So always use parameters in your dynamic sql in your stored procedures.  If someone ran a query for the top N slowest queries on your box, none of them should be something like OriginDate >= '1/5/2009' but instead OriginDate >= @OriginDate. 


    2.  Do not use correlated subqueries on anything that needs to go fast.  I only count the rare lazy scheduled report as a candidate for a correllated subquery.  Otherwise the question to ask is how can I either put this in a join or restructure my database so that this query can run more efficiently. (ex. select h.empid, (select i.name from emp i where i.empid = h.empid) as 'name' from hrdata h )


    3.  Do not be comforted if things run fast on the test database.  If you can, always perform a SQL Profiler trace on your demo application before leaving in the lonely cycle of maintenance.  I like using the Profiler because it gives you test parameters to use for all of your stored procedures so you can then take that and get actual execution plans in sql server management studio.  Then you can scan for any conspicuous table scans or clustered index scans that don't belong.  You'll sleep better at night knowing your application isn't going to crash in some fatal unoptimized slow down one day.

    4.  Don't put every function and your mom's functions all in one big query.  You might find your tempdb or transaction log growing exponentially larger in any scheduled reports or extract load transform (ETL) tasks.


    Please share anything you can as well.  Thanks.

    Tuesday, February 9, 2010 2:57 PM

All replies

  • User-1528148979 posted

    Your explanation does not satisfy the title. Please change the title or insert more subsequent "things".

    Nonetheless, thanks for the tipsSmile, joe!

    Tuesday, February 9, 2010 3:59 PM
  • User-1918385786 posted

    Sorry, I was hoping someone else would provide numbers 4 - 10.  Smile  Always learning...

    Tuesday, February 16, 2010 3:26 PM
  • User-319574463 posted

    5) Do not have a stored procedure that just EXECUTES some TSQL. I have seen this and it is just wrong!

    6) Consider grouping your stored procedures either by domain (SQL2005 and later) or by a prefix that allows allows you to set permissions by wildcard.

    7) Assign EXECUTE permissions to a role which can then be assigned to one or more user accounts.

    Friday, February 19, 2010 7:17 AM
  • User-319574463 posted

    Here is an example of setting permissions by wildcard:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:  Clive Chinery
    -- Create date: 11Nov2007
    -- Description: Set Permissions
    -- =============================================
    -- Copyright (C) 2000 to 2009 Clive Chinery
    -- 
    -- This library is free software; you can redistribute it and/or
    -- modify it under the terms of the GNU Lesser General Public
    -- License as published by the Free Software Foundation; either
    -- version 2.1 of the License, or (at your option) any later version.
    -- 
    -- This library is distributed in the hope that it will be useful,
    -- but WITHOUT ANY WARRANTY; without even the implied warranty of
    -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    -- Lesser General Public License for more details.
    -- 
    -- You should have received a copy of the GNU Lesser General Public
    -- License along with this library; if not, write to the 
    -- Free Software Foundation, Inc., 59 Temple Place, Suite 330, 
    -- Boston, MA  02111-1307  USA
    ALTER PROCEDURE [dbo].[asp_dbPermissions]
    (  
      @Execute  BIT = 1, -- Default is Execute
      @Print    BIT = 0  -- Default is No print 
    ) AS
    SET NOCOUNT ON;
    DECLARE @ROLENAME VARCHAR(50)
    SET @ROLENAME = 'WebRole'   -- Change WebRole to your desired database RoleName
    DECLARE xArgs CURSOR FOR 
      SELECT  O.name 
      FROM sysobjects O
      WHERE O.xtype = 'P' AND O.name LIKE 'usp_%'
      ORDER BY O.name
    DECLARE @name VARCHAR(128)
    DECLARE @exec VARCHAR(160) 
    OPEN  xArgs
    FETCH xArgs INTO @name
    WHILE @@FETCH_STATUS=0
    BEGIN
      SET @exec = 'GRANT EXECUTE ON ' + @name + ' TO ' + @ROLENAME
      IF @Print = 1 PRINT @exec
      IF @Execute = 1 EXEC (@EXEC)
      FETCH xArgs INTO @name
    END
    CLOSE xArgs
    DEALLOCATE xArgs
    ---------------------- This is the end ---------------

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		  Clive Chinery
    -- Create date: 11Nov2007
    -- Description:	Set Permissions
    -- =============================================
    -- Copyright (C) 2000 to 2009 Clive Chinery
    -- 
    -- This library is free software; you can redistribute it and/or
    -- modify it under the terms of the GNU Lesser General Public
    -- License as published by the Free Software Foundation; either
    -- version 2.1 of the License, or (at your option) any later version.
    -- 
    -- This library is distributed in the hope that it will be useful,
    -- but WITHOUT ANY WARRANTY; without even the implied warranty of
    -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    -- Lesser General Public License for more details.
    -- 
    -- You should have received a copy of the GNU Lesser General Public
    -- License along with this library; if not, write to the 
    -- Free Software Foundation, Inc., 59 Temple Place, Suite 330, 
    -- Boston, MA  02111-1307  USA
    ALTER PROCEDURE [dbo].[asp_dbPermissions]
    (  
      @Execute  BIT = 1, -- Default is Execute
      @Print    BIT = 0  -- Default is No print 
    ) AS
    SET NOCOUNT ON;
    DECLARE @ROLENAME VARCHAR(50)
    SET @ROLENAME = 'WebRole'   -- Change WebRole to your desired database RoleName
    DECLARE xArgs CURSOR FOR 
      SELECT  O.name 
      FROM sysobjects O
      WHERE O.xtype = 'P' AND O.name LIKE 'usp_%'
      ORDER BY O.name
    DECLARE @name VARCHAR(128)
    DECLARE @exec VARCHAR(160) 
    OPEN  xArgs
    FETCH xArgs INTO @name
    WHILE @@FETCH_STATUS=0
    BEGIN
      SET @exec = 'GRANT EXECUTE ON ' + @name + ' TO ' + @ROLENAME
      IF @Print = 1 PRINT @exec
      IF @Execute = 1 EXEC (@EXEC)
      FETCH xArgs INTO @name
    END
    CLOSE xArgs
    DEALLOCATE xArgs
    ---------------------- This is the end ---------------



    Friday, February 19, 2010 7:19 AM
  • User-1918385786 posted

    Cool tips on the wildcard permissions.  Thanks 

    Tuesday, March 23, 2010 12:58 PM
  • User-1148431695 posted

    Hi,

    All the mentioned tips are really helpful. Thanks for sharing :) 

    Tuesday, August 31, 2010 2:52 AM