SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET
NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET
@Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

169 thoughts on “SQL SERVER – Stored Procedure Optimization Tips – Best Practices

  1. A very useful collection of tips.

    Though I would expect a few people to come up with the ‘it depends’ and ‘not always’ arguments, this is certainly a helpful set of tips/best-practices that every TSQL developer should add to his/her check list.

    • ugh..I have a complex stor proc that I could so use all of your help on. It runs fine but takes over 8 hours to run! Any suggestions would be so greatly appreciated.
      BEGIN
      — SET NOCOUNT ON added to prevent extra result sets from
      — interfering with SELECT statements.
      SET NOCOUNT ON;

      /*
      IF OBJECT_ID(‘tempdb..#tempAgingRecords’) IS NOT NULL
      drop table #tempAgingRecords
      */

      declare @itemid varchar(40),
      @dataareaid varchar(10)

      –Master records
      select distinct t4.product_number itemid,
      t3.company_id dataareaid,
      1 processed
      into #tempAgingRecords
      from edw.dbo.f_inventory_transaction t1
      left outer join edw.dbo.dim_inventory_trans_type t2 on
      t1.transaction_type_key = t2.transaction_type_key
      left outer join edw.dbo.dim_company t3 on
      t1.company_key = t3.company_key
      left outer join edw.dbo.dim_product t4 on
      t1.product_key = t4.product_key
      left outer join edw.dbo.dim_date t5 on
      t1.date_key = t5.date_key
      where –t2.transaction_type not in (‘Profit/Loss’, ‘Transfer’, ‘Quarantine Order’, ‘Transfer Order Shipment’, ‘Transfer Order Receive’)
      t2.issues_status in (‘None’, ‘Sold’, ‘Deducted’, ‘Picked’)
      and t2.receipt_status in (‘None’, ‘Purchased’, ‘Received’, ‘Registered’)
      and t1.transaction_quantity < 0
      –and t4.product_number = 'Meiban CM'
      and t4.product_key is not null
      and t5.[date] ‘1/1/1900′
      order by t4.product_number

      –Variables for aging process
      declare @negTable table (
      negDatestatus datetime,
      negQty decimal(28,12),
      negProcessed int,
      negRecid bigint,
      negInventoryAmount decimal(28,12)
      )

      declare @posTable table (
      posDatestatus datetime,
      posQty decimal(28,12),
      posProcessed int,
      posRecid bigint,
      posInventoryAmount decimal(28,12)
      )

      declare @negDatestatus datetime,
      @negQty decimal(28,12),
      @posDatestatus datetime,
      @posQty decimal(28,12),
      @negRecid bigint,
      @posRecid bigint,
      @negInventoryAmount decimal(28,12),
      @posInventoryAmount decimal(28,12)

      truncate table work_invent_trans_aging

      while (select sum(processed) from #tempAgingRecords) 0
      begin
      select @itemid = itemid, @dataareaid = dataareaid from #tempAgingRecords where processed 0

      insert into @negTable
      select t5.[date] datestatus,
      sum(t1.transaction_quantity) qty,
      1 processed,
      rec_id,
      sum(case when t3.company_id = ‘rel’
      then isnull(case when issues_status in (‘None’, ‘Sold’, ‘Deducted’,’Picked’)
      and receipt_status in (‘None’, ‘Purchased’, ‘Received’, ‘Registered’)
      then t1.transaction_quantity * t4.unit_cost end, 0)
      else isnull(case when issues_status in (‘None’, ‘Sold’, ‘Deducted’,’Picked’)
      and receipt_status in (‘None’, ‘Purchased’, ‘Received’, ‘Registered’)
      then t1.posted_cost_amount end, 0) +
      isnull(case when issues_status in (‘None’, ‘Sold’, ‘Deducted’,’Picked’)
      and receipt_status in (‘None’, ‘Purchased’, ‘Received’, ‘Registered’)
      then t1.adjustment_cost_amount end, 0) +
      isnull(case when issues_status in (‘Deducted’,’Picked’)
      or receipt_status in (‘Received’, ‘Registered’)
      then t1.physical_cost_amount end, 0) end) as InventoryAmount
      from edw.dbo.f_inventory_transaction t1
      left outer join edw.dbo.dim_inventory_trans_type t2 on
      t1.transaction_type_key = t2.transaction_type_key
      left outer join edw.dbo.dim_company t3 on
      t1.company_key = t3.company_key
      left outer join edw.dbo.dim_product t4 on
      t1.product_key = t4.product_key
      left outer join edw.dbo.dim_date t5 on
      t1.date_key = t5.date_key
      where t4.product_number = @itemid
      and t3.company_id = @dataareaid
      –and t2.transaction_type not in (‘Profit/Loss’, ‘Transfer’, ‘Quarantine Order’, ‘Transfer Order Shipment’, ‘Transfer Order Receive’)
      and t2.issues_status in (‘None’, ‘Sold’, ‘Deducted’, ‘Picked’)
      and t2.receipt_status in (‘None’, ‘Purchased’, ‘Received’, ‘Registered’)
      and t1.transaction_quantity < 0
      –and t4.product_number is not null
      and t5.[date] ‘1/1/1900′
      group by t5.[date],
      rec_id
      having sum(t1.transaction_quantity) 0
      –and t4.product_number is not null
      and t5.[date] ‘1/1/1900′
      group by t5.[date],
      rec_id
      having sum(t1.transaction_quantity) > 0

      –Get the first positive quantity
      select @posRecid = posRecid, @posDatestatus = posDatestatus, @posQty = posQty, @posInventoryAmount = posInventoryAmount from @posTable p1 where posProcessed 0 order by posDatestatus desc, posRecid desc

      while (select isnull(sum(negProcessed), 0) from @negTable n1) 0
      begin
      –Get negative quantity
      select @negRecid = negRecid, @negDatestatus = negDatestatus, @negQty = negQty, @negInventoryAmount = negInventoryAmount from @negTable n2 where negProcessed 0 order by negDatestatus desc, negRecid desc

      if (select isnull(sum(posProcessed), 0) from @posTable) = 0
      begin
      –Insert negative value if there are no positive quantities
      insert into work_invent_trans_aging
      values (@negRecid, @posRecid, @itemid, @dataareaid, @negDatestatus, ‘1/1/1900′, @negQty, ‘New1′, @negInventoryAmount)

      set @negQty = 0
      set @negInventoryAmount = 0
      end
      else
      begin
      if @posQty > (@negQty * -1)
      begin
      –Write into result table the consumed negative amount
      insert into work_invent_trans_aging
      values (@negRecid, @posRecid, @itemid, @dataareaid, @negDatestatus, @posDatestatus, @negQty, ‘Original’, @negInventoryAmount)

      set @posQty = @posQty + @negQty
      set @posInventoryAmount = @posInventoryAmount + @negInventoryAmount
      end
      else
      –If the negative quantity has left to consume, get another positive record
      while @negQty < 0
      begin
      –If there are no more positive record (still negatives left), then insert and end
      if (select isnull(sum(posProcessed), 0) from @posTable) = 0
      begin
      insert into work_invent_trans_aging
      values (@negRecid, @posRecid, @itemid, @dataareaid, @negDatestatus, '1/1/1900', @negQty, 'New2', @negInventoryAmount)

      set @negQty = 0
      set @negInventoryAmount = 0
      end
      else
      begin
      if (@negQty * -1) < @posQty
      begin
      insert into work_invent_trans_aging
      values (@negRecid, @posRecid, @itemid, @dataareaid, @negDatestatus, @posDatestatus, @negQty, 'New3', @negInventoryAmount)

      set @posQty = @posQty + @negQty
      set @posInventoryAmount = @posInventoryAmount + @negInventoryAmount

      set @negQty = 0
      set @negInventoryAmount = 0
      end
      else
      begin
      –Write into result table the consumed positive amount
      insert into work_invent_trans_aging
      values (@negRecid, @posRecid, @itemid, @dataareaid, @negDatestatus, @posDatestatus, (@posQty * -1), 'New4', (@posInventoryAmount * -1))

      set @negQty = @negQty + @posQty
      set @negInventoryAmount = @negInventoryAmount + @posInventoryAmount

      update @posTable
      set posProcessed = 0
      where posDatestatus = @posDatestatus
      and posRecid = @posRecid

      select @posRecid = posRecid, @posDatestatus = posDatestatus, @posQty = posQty, @posInventoryAmount = posInventoryAmount from @posTable p1 where posProcessed 0 order by posDatestatus desc, posRecid desc
      end
      end
      end
      end

      update @negTable
      set negProcessed = 0
      where negDatestatus = @negDatestatus
      and negRecid = @negRecid

      set @negDatestatus = NULL
      set @negQty = NULL
      set @negRecid = NULL
      end

      –select * from @posTable t1 order by t1.posDatestatus, posRecid
      –select * from @negTable t1 order by t1.negDatestatus, negRecid

      –Clear aging variables
      set @negDatestatus = NULL
      set @negQty = NULL
      set @negRecid = NULL
      set @negInventoryAmount = NULL

      set @posDatestatus = NULL
      set @posQty = NULL
      set @posRecid = NULL
      set @posInventoryAmount = NULL

      delete @negTable
      delete @posTable

      update #tempAgingRecords
      set processed = 0
      where itemid = @itemid
      and dataareaid = @dataareaid
      end
      END

      GO

  2. Please, when you post something that you are trying to explain it to the community, provide samples like in your other posts, for me is very abstract sample about Try and Catch

    BEGIN TRY
    –Your t-sql code goes here (give a example here)
    END TRY
    BEGIN CATCH
    –Your error handling code goes here (give a example here)
    END CATCH

    Anyway thank you for the tips…

    • Knowing that it is there to use, you could always take a look in Books Online or search elsewhere for in depth examples. There are lots of examples and trying them out will help you understand how it works more clearly. This set of tips doesnt go in to depth on any of the items as they each could have an article on their own.

  3. If i am using temporary tables, local and global in some of my store procedures, should i use indexes (clustered & non-clustered) on these tables to get maximum performance, specially when i know that these temporary tables will contain lot of rows ???

    • Hello Aasim,

      Creating an index to be used for once would not provide any improvement because processing consumed to create an index itself would overcome the benifit of its uses. We should create an index on any table (permanent or temporary table) if that is usable in more than one statements.

      Regards,
      Pinal Dave

      • Hello Aasim,

        Like SQL Genius (Pinal) said, if we use temp tables more than once in stored procedure, then definitely it is suggested to create indexes on temporary tables especially when you are dealing with huge amounts of data.

        I have seen significant differences in my procedure performance after I created Indexes on huge temp tables.

        ~ IM.

      • Hello Pinal,

        Sorry, but I disagree with your statement about there is no sense for creating an index on # table if this index will be used just once. Many times in my practice “creation index on the fly” saved tens of minutes, sometimes hours on data processing. It is all about data amount. Sometimes you can’t avoid of using a # table and that table becomes huge in some cases. When we talking about million records temporary table and we have to join this table with another multimillion table we have to create indexes otherwise we can wait forever till procedure is completed.
        When we have deal with VLDB, the creation of index(es) on # table on the fly is definitely solution.

        BTW, very nice site, I like it, thank you!

        Thanks,
        IgorA

      • HI Pinal i have a question i have a stored procedure which takes 1 hour for execution and how i can check whether it is progressing or not ?

    • Aasim,

      I would profile the procedure with/without indexes, and then decide what is the actual best practice for your case.

  4. Hello!

    Interesting that you wrote this article today. Along with my other responsibilities, I am also part of the DBA team and was working on a code review today and recommended the usage of schema qualifiers (dbo.MyStoredProcedure) for the stored procedures. I had logically thought it through and was going to do a little experiment tomorrow to see the performance difference that qualifers provide. I will run the test tomorrow and update if time permits.

    Great compilation, by the way. I agree with Jacob – this should be part of every database developer’s/administrator’s checklist.

    Have a Great Day!

    • Use schema name with object name:
      I executed the both queries with schema name and without schema name and checked the execution plan of both the queries.I didn’t find any differrence.

      Does anyone have solution how to prove this tip?

      • whenever we execute any SELECT query first database engine checks in master database. if it’s not there in master database it checks in the current session database depends on schema.

  5. Regarding using point on

    Use IF EXISTS (SELECT 1) instead of (SELECT *):

    There is no guarantee SELECT 1 will outperform SELECT *
    Also the result is a boolean value and it doesn’t matter how many columns are used inside EXISTS

  6. Regarding using point on

    Use IF EXISTS (SELECT 1) instead of (SELECT *):

    There is no guarantee SELECT 1 will outperform SELECT *
    Also the result is a boolean value and it doesn’t matter how many columns are used inside EXISTS

    • Well, I would think that in the case of EXISTS the subquery is terminated when first matching row is found and only boolean value is returned. This would seem logical.

      But since MSDN doesn’t say anything about this I think the safest way would be to use:

      EXISTS (SELECT TOP(1) 1 FROM foo WHERE bar = ..)

      This is because if subquery isn’t terminated it would do the matching against all the rows in ‘foo’ and return 0..n rows. This means that there would be 0..n unneccessary matching operations and 0..n * column_count data readings.

      With TOP(1) you tell that you are only insterested in the first matching row and no subsequent rows are matched. And because you are returning constant value 1 there will be no unnecessary read operation to the table.

      • Okay. I went ahead and tested this. It seems that there’s no diffence between any of these:

        SELECT 1 FROM MyTable WHERE EXISTS (SELECT * FROM MyTable)
        SELECT 1 FROM MyTable WHERE EXISTS (SELECT 1 FROM MyTable)
        SELECT 1 FROM MyTable WHERE EXISTS (SELECT TOP(1) 1 FROM MyTable)

          • @rudesyle

            INDEX usage would not be based on the *, but on the WHERE clause. Think about it, EXISTS causes a lookup which we are hoping to be an INDEX scan of sorts. The match is based on the WHERE clause where the correlation is defined.

      • Brian,

        From link1

        However, at runtime the two forms of the query will be identical and will have identical runtimes.

        So SELECT 1 is not faster

        Aslo from link2

        Sure, it will take a couple of extra femtoseconds to compile (compared to the boring SELECT 1),

        See the definition of femtoseconds
        It is actually very negligible in real time

        http://en.wikipedia.org/wiki/Femtosecond

        • @Madhivanan

          Yes, but the expansion itself takes time! :)

          When Brad said femtoseconds, he was being facetious.

          The idea here is, it does take longer, just not much. As is a very, very, very, very, very, very, tiny bit longer.

          • Pinal, this is great stuff!

            Guys – regarding the “select 1″ versus “select *”, I feel you are missing something here.

            I have seen notable peformance improvements by using SELECT 1 instead of SELECT *. Looking at the Execution Plan to address to performance, I could see that SELECT * was having to bring back all fields using the clustered index. When SELECT 1 was used instead, a covering index based on the WHERE clause was used by the engine and performance was notably better. As in a query taking 4 secs now takes 0.15 secs.

            Also, from a developers point of view, SELECT 1 is preferred because it is symantically stating that the fields are not important to the query.

  7. As Madhivanan points out, there is no benefit in the ‘select 1′ syntax over ‘select *’ if you’re using ‘if exists’ at the same time.

    There is no data processed from the query inside the ‘exists’ clause and so it makes no difference what you ‘select’. Books Online’s examples for the ‘exists’ keyword uses the ‘select *’ syntax.

    A much more important point to make is not to perform a count and compare it to zero (a lot of people seem to do this) – so:

    if (select count(*) from …) > 0

    is especially bad, and should be replaced with

    if exists (select * from …)

    instead.

  8. Hi,
    On the part where you’ve suggested not to use the ‘SP_’ prefix. Wouldn’t that be a problem only if you are creating stored procedures using the dbo schema.

    Please elaborate the issue.

    Regards.

    • Hello Hamza,

      A stored procedure with prefix “sp_” whether in dbo schema or user defined schema, whether fully qualified name or non-qualified name, always first checked in master database. And if your stored procedure is in another database then an extra search would be an overhead every time you call that stored procedure.

      Regards,
      Pinal Dave

  9. I have seen in one of the blogs to use EXISTS like

    IF EXISTS(Select null from table)

    Will it optimize the perfomance better than

    IF EXISTS(Select 1 from table)
    ?

    • @Divya

      It makes no difference what is put there.

      Brad Schulz has an interest article on it: http://bradsruminations.blogspot.com/2009/09/age-old-select-vs-select-1-debate.html

      Even 1/0 is allowed! Obviously, it is not evaluated.

      Technically, however, * does get expanded, adding some minuscule amount of time: http://www.sqlskills.com/BLOGS/CONOR/2008/02/default.aspx?page=2

      So, anything other than * takes the same amount of time. * takes an iota longer. Personally, i use *, to show that i do not care what the results are.

    • Hello Divya,

      I not think so because there is no significant difference in transferring of 1 byte and a null value.
      Other than that there is no difference between these two queries.

      Regards,
      Pinal Dave

      • “I not think so because there is no significant difference in transferring of 1 byte and a null value.”

        While that is true in this exact case I like to be pedantic about this and elaborate this a bit and probably confuse everybody up :)

        There is no concept NULL value if you think about C code or even the CPU. Pointers in C can have NULL values but that just means that the pointer is pointing to memory in the address 0x00000000 (in 32-bit machine). The actual pointer is still taking sizeof(VOID*) amount of memory even when it points to NULL.

        Now if you think about functions in C or any other language they always reserve space in the function stack for the return value. You can say, again in C, that you don’t care about the return value and declare the function to return “void” but still it takes 4 bytes of memory in 32-bit machine for return value.

        And actually, of you think about the CPU, there is 32 bit register reserved exactly for this.

        And now considering that it makes no difference to return one byte or four bytes since there is space for four bytes anyway. And in fact, many times (all the times?) when you deal with C data types CHAR (1 byte) or SHORT (2 bytes) you end up taking 4 bytes because of the padding to keep memory aligned.

        Uh! I almost felt like going back in time some ten years when I was writing kernel drivers for Windows :)

    • after testing several queries using both IF EXISTS(SELECT * AND SELECT 1, in none of my tests did the SELECT 1 statement outperform the SELECT * statement. In all tests the IF EXISTS statement always only returned 1 row, the I/O Cost and CPU Cost were identical whichever statement was used, however my tables were relatively small with less than 10000 records in each.

  10. Sir, This is very useful tips for my site. But also have one problem.

    I am searching for a stored procedure which will eliminate all the STOP words like “in”, “the” in my query and accordingly search the result.

    Is it correct :-
    //This is my stored procedure
    CREATE PROCEDURE sp_GetInventory
    @location varchar(50) AS
    select column1, column2 from table1
    where column1 like ‘%SearchString%’ or column2 like ‘%SearchString%’

    EXECUTE sp_GetInventory ‘SearchString’

  11. Dear pinal,
    very useful tips for Optimization for stored procedures.

    Yes it is really good performance not using SP_ProcName.
    We are using instead as dbo.USP_ProcName means UserStoredProcedure

  12. Pingback: SQL SERVE R- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table) Journey to SQL Authority with Pinal Dave

  13. It is very helpful article, but I have one comment about the point

    Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

    I created two stored procedures with the same name in “Master” and “Another new DataBase”, and I executed it from the new one.
    The result returned from the new one.
    This mean that, it didn’t execute the one in the Master, although there’s a SP in the Mater with the same name.
    Can you please elaborate this.

  14. This is a killer post since optimizing database code or SQL statements is a killer job.
    However the hints provided here are very useful. Simple optimization hints, that anyone can use, and more advanced but still easy to understand.
    About the SELECT 1 versus the SELECT * i think that the first should be preferred over the last.
    A few years ago i came across a few optimization tips for Oracle (my previous professional background) and this tip was already mentioned. But later i read that Oracle internally optimizes EXIST (SELECT * FROM …). Perhaps the same approach is used inside SQL Server and that’s why the difference isn’t noticed.
    The reason stated for using SELECT ‘ANY_CONSTANT’ over SELECT * is that * would read every column, so it would be more efficient to return a constant. The gains would be obtained in the reduced reading of data.
    Regards.

  15. Use schema name with object name:
    I executed the both queries with schema name and without schema name and checked the execution plan of both the queries.I didn’t find any differrence.

    Does anyone have solution how to prove this tip?

  16. Hello Swati,

    The difference will be considerable when the queries involves too many tables,views and processes large amount of data.

    Regards
    Sheju

  17. Pinal,
    If we execute the Stored procedure starting with “sp_”
    by this way

    Exec DBname.dbo.SPname

    SQL engine directly go on specified database instead of master DB to search that sp.
    Is it right? Let me know your comments.

    Thanks
    Darshan Shah

  18. i have confused about store procedure becuase query is good to procedure plz expain full how to store procedure and what is use of bussiness inudstires

  19. Hi Pinal,

    Could you please tell some what in detail about performance tuning, may be with an example like – If a query takes more time what are all the causes for that or if a stored procedure takes more time what are all causes for that so that many DBA’s as like me who are all in intermediate stage would gain a lot . Thanks!

  20. good afternoon all the query is used to table but what is used of trigges and stored procedure because query is profitable in table but what is used of procedure in table

    i dont know what is used of procedure,triggers

    plz define with example

    what is used of table and database

    thankyou

  21. Pinal:

    How would one discover / prove via a trace “Do not use the prefix “sp_” in the stored procedure name….first searches in the master database and then in the current session database”

  22. Hi Pinal and all the readers

    Nice article..

    Btw one more tips of using DDL and DML query..
    Always use the DDL queries like create table or drop table first and then use DML query like Select, Update or Delete

    As DDL queries always need a recompilation of procedures in order to make the changes in our DML Queries

  23. hi all,

    regd. sql sp, i face the prob. regd. passing date value…

    it gives error ‘Invalid syntax at date-part..’

    here i give u the code 4 sp …

    CREATE Procedure BimstInsert
    (@Date Datetime,@Pc Numeric(6,0),@Ic Numeric(6,0),@Qt Numeric(6,0))
    As
    Begin

    declare @ra Numeric(6,2)
    Declare @ch Numeric(6,0)

    Select @ra=I.Rate from ItemMst As I
    Where @Ic=I.Code
    Select @ch=IsNull(Max(B.ChNo),0)+1 from BiMst As B

    Insert into BiMst Values
    (@ch,@Date,@Pc,@Ic,@Qt,@ra,(@Qt*@ra))
    End

    ———————————————————–

    exec BimstInsert (’10/jan/2010′,1,1,10)

    i also tried for diff. date style…

    pls. help me

    thanks in advance…

    bye…

  24. where are the values which you passing in
    Insert into BiMst Values
    (@ch,@Date,@Pc,@Ic,@Qt,@ra,(@Qt*@ra))

    the value must me in datetype format which you will be passing in @date

  25. Hi sir,
    I am fresher and i am working in welcomenetwork company
    i have string like ‘0012340056789’
    but i want only the string like ‘12340056789’
    that is starting 0 should be removed so how can i
    do in sql server function
    Reply
    Thanks & Regards,
    Dipak B. Kansara

  26. Some of my most impressive performance improvements have come from replacing cursor-based code with carefully constructed set-based processing. That tip alone can pay the bills…

  27. Is it better to have multiple small stored procedure or one big stored procedure (Combining multiple sp into one and having some conditional logic in it to choose which sql should be used) ?

    I love to have multiple stored procedure specific to the user request in place of having one which can serve all kinds of request but i need some better reasons for it.

    Thanks for the all your help over the years…

    • I think it is better to have seperate procedures so that it is easy to maintain. If the result set contains different number of columns, it is not possible to design a report based on it

  28. Solution for optimizing SQL stored Procedures are WITH (ROWLOCK) for insert, update and select, and if you dont do any calculation on your syntax you can use also WITH (NOLOCK).
    Nice site..

  29. Hi, Pinal.

    Could the number of parameters being sent to a stored procedure from a VB .Net application affect the performance of an If Exists (…) Update… statement in the procedure?

    Specifically: The procedure takes 144 parameters some of which are varchar(200), and others are varchar(50). The procedure is called from a live data broadcast handler sub-routine written in VB .Net.

    The updates seem to take excessively long time, specially when the broadcasts are quicker. So much so that the time lag grows into several hours over a day.

    I have not maintained an index on the table, because it causes another procedure (with insert commands) to slow down.

    Any suggestions would be appreciated.

  30. Sir,
    Thanks for the information or tips you provide for optimazation in SQL. I have been working on an application which uses XML file and database as well. We read XML files and extract required information and update that information in database. We have approx 18 stored procedure which updates 7-8 tables for a single XML file.
    And we have about 22500 Xml files. It takes about 3 hours on local computer and if we update remote server using this application it takes about 10 hours to run completely. Please tell me more about optimization in SQL stored procedures.

    Thanks

  31. > The reuse of the existing complied plan will result in >improved performance.
    It is true for sp_executesql if we have uniform distribution of data. In other case we will have degradation of performance. You can save a bit time on reuse existing plan but you can lose a lot time when you reuse bad plan. So in my opinion if you have big table with non-uniform distribution of data reusing of plan is bad idea

  32. i want to auto insert records in a muster entry table with imp fields in time & out time as a data type date time

    should i prefere trigger to auto insert my in time daily
    at 9:00 am except weekends????

  33. Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
    SELECT * FROM dbo.MyTable — Preferred method
    — Instead of
    SELECT * FROM MyTable — Avoid this method
    –And finally call the stored procedure with qualified name like:
    EXEC dbo.MyProc — Preferred method
    –Instead of
    EXEC MyProc — Avoid this method

  34. Use schema name with object name

    I’m not able to find the difference between
    SELECT * FROM dbo.MyTable — Preferred method
    – Instead of
    SELECT * FROM MyTable — Avoid this method

    Tell me the difference . I dont undestand this concept

  35. Hi Dave,
    As you said
    “Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.”

    – so i have created the stored procedure sp_test in my own database and same in master database.

    i have excuted the stored procedure from my local database and i found that local stored procedure is getting executed all time.

    please clarify me..

  36. Fortunately most applications have stored procedures that follow the same basic operations and design.

    So I always prefer some stored procedure generator so that sp becomes consistent.

    Tools4Sql.net has gr8 sp generator and it really generates optimized and efficient stored procedures for MS SQL Server

  37. Hi Dave,

    I have a typical situation. I have two tables, 1. user 2.address. each user contains more than 2 or 3 address. I have to update user table with all address ids and count concatenated string in a field.

    ex: user.address = 4;addid1;sddid2;addid3.

    I have used cursor, while loop and temp table to update. in all cases it is taking more than 24 hours. for production we don’t have that much time. I have to complete in 4 to 6 hours.

    can you please guide me?

    your help much appreciate.

    Regards,
    Seshadri

  38. I did a comparison executing and showing actual plan and cost and between the 2, they were both at 50% so could you provide an example of where there is a definite impact?

    The point I tried to verify was
    Use IF EXISTS (SELECT 1) instead of (SELECT *)

  39. I have used Tools4Sql.net and I found it wonderful but I think they are missing features of Encryption which is really necessary.

  40. hi sir,

    i am using cursors for looping
    any alternative.

    problem statement is i want pass cursor value in where condition in select statement

    example:

    select count(*) from table

    — inner joins

    where id= cursor value

  41. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31 Journey to SQLAuthority

  42. I think this point is bit misleading: Use IF EXISTS (SELECT 1) instead of (SELECT *):

    The column list with SELECT in IF EXISTS () is ignored, so you provide column list or * it just ignores them, here is an example:

    if exists (select 1/0)
    select ‘yes’
    else
    select ‘no’
    GO

    select 1/0
    GO

  43. plz sombody resolve my problem
    i have a query in sql server and i have to change the results into xml form through stored procedure
    plz sombody tell
    thanks

  44. Hi Pinal,

    Nice artical.

    But as you mentioned there in your article-
    “always refer the objects with qualified name in the stored procedure” like:
    —————————————————————————————————
    SELECT * FROM dbo.MyTable — Preferred method
    — Instead of
    SELECT * FROM MyTable — Avoid this method
    –And finally call the stored procedure with qualified name like:
    EXEC dbo.MyProc — Preferred method
    –Instead of
    EXEC MyProc — Avoid this method
    —————————————————————————————————
    we could face problem when we have more than one database position like a test database server and a live database and database admin are different or database admin can be changed time to time, in that case to maintain the synchronization we need to change the query regarding database admin, which is quite impossible for a developer.

    • Hello, Amit!

      User-schema separation introduced in SQL Server 2005 was meant to avoid this situation that you are talking about. Users are no longer directly linked to an object.

      In the qualified object name that Pinal demonstrates, the “dbo” is not the user name, but the default schema name.

  45. Use schema name with object name:

    Hi sir i have some doubt regarding the above explanation

    I try select * from dbo.Categories in NorthWInd db
    execution plan showing Clustred index scan Estimated cpu cost 0.003125
    and select cached plan size 9B

    after that i try select * from Categories
    execution plan showing Clustred index scan Estimated cpu cost 0.003125
    and select cached plan size 9B

    both the execution plans are same.

    so why we need to put schema name with object name

    regards,
    swapnil K

  46. Re: Swapnil,

    For this situation to work, as Pinal is suggesting, you will have to take note of the time taken before the cached plan is found. Having the qualified schema name reduces the time taken to search for the correct plan.

    Obviously we’re splitting hairs here, but in the world of DBA’s that’s often what we do if there’s a requirement to attain the fastest possible response.
    Dallas

  47. Why do u make things complex by adding odd things like “SET NOCOUNT ON;”
    try keep things simple.

    “CEATE PROCEDURE proc_name
    as
    your query”

    thats it

    • Contrary to your comment, SET NOCOUNT ON is quite important. What it ensures is that the number of rows affected is not returned to the caller.

      You may be surprised to know, but it is true that not having SET NOCOUNT ON may result in the application not behaving as it should.

      In my opinion, removing “SET NOCOUNT ON” is not making things simple – it’s making things suicidal!

  48. Would you suggest using the schema name for all object references in a sproc including joins and conditional clauses?

    For example:
    SELECT b.BatchID, b.BatchName, bt.BatchTransactionID, bt.BatchTransactionName
    FROM dbo.Batch
    INNER JOIN dbo.BatchTrx WITH(NOLOCK) ON dbo.BatchTrx.BatchID = dbo.Batch.BatchID
    WHERE dbo.Batch.StatusID = 1
    AND dbo.BatchTrx.StatusID = 1

  49. Hello Pinal Dave, thanks you for your blog, is a very good source for me.

    My cuestion is when I execute the select directly in the SP does it reuse the
    execution plan ?, like this

    CREATE PROC dbo.ProcName @p1 int
    AS
    SET NOCOUNT ON;
    –Procedure code here
    SELECT column1 FROM dbo.TblTable1 where ProcID = @p1
    — Reset SET NOCOUNT to OFF
    SET NOCOUNT OFF;
    GO

  50. These Tips are very much useful in optimizing the stored procedures.
    I would like to add one more.
    Minimize the usage of temporary tables,instead use variables to improve the performance

  51. Dear Pinal,

    I have written a stored procedure but, It takes on an average 1 minute to execute. I tried all tips you suggested, but still that haven’t optimized the query.

    I am a newbie, please guide me to optimize below Stored Procedure

    CREATE PROCEDURE [dbo].[usp_GetMessagesCountBySearchFilter]
    (
    @SearchText NVARCHAR(1000),
    @FromDate DATETIME,
    @ToDate DATETIME,
    @IsPending BIT,
    @Sender NVARCHAR(1000),
    @Receiver NVARCHAR(1000)
    )
    AS
    BEGIN
    –SET NOCOUNT ON;

    DECLARE
    @local_SearchText NVARCHAR(1000),
    @local_FromDate DATETIME,
    @local_ToDate DATETIME,
    @local_IsPending BIT,
    @local_Sender NVARCHAR(1000),
    @local_Receiver NVARCHAR(1000)

    SET @SearchText= @local_SearchText
    SET @FromDate =@local_FromDate
    SET @ToDate =@local_ToDate
    SET @IsPending=@local_IsPending
    SET @Sender =@local_Sender
    SET @Receiver =@local_Receiver

    DECLARE @MessageCount INT
    SELECT @MessageCount=COUNT(MessageID) FROM
    (
    SELECT
    DISTINCT T1.MessageID,
    MessageSubject,
    MessageBody,
    PostedDateTime,
    (
    SELECT
    CAST(T4.UserID AS NVARCHAR(120))
    +’,’
    FOR XML PATH(”)
    ) AS PostedForIds,
    (
    SELECT
    CASE T4.IsOrganization
    WHEN 1 THEN ISNULL(T4.CompanyName,ISNULL(T4.FirstName+’ ‘+T4.LastName,T4.UserName))
    ELSE ISNULL(T4.FirstName+’ ‘+T4.LastName,T4.UserName)
    END
    +’,’
    FOR XML PATH(”)
    ) AS PostedForNames,
    T2.FromUserId AS PostedById,
    CASE T3.IsOrganization
    WHEN 1 THEN ISNULL(T3.CompanyName,ISNULL(T3.FirstName+’ ‘+T3.LastName,T3.UserName))
    ELSE ISNULL(T3.FirstName+’ ‘+T3.LastName,T3.UserName)
    END AS PostedByName,
    (SELECT COUNT(UserId) FROM dbo.tblMessageInbox T4 WHERE T4.MessageId=T2.MessageId) AS SentToCount,
    (
    SELECT CASE
    WHEN COUNT(UserId)>0 THEN 1
    ELSE 0
    END
    FROM dbo.tblMessageInbox T5
    WHERE T5.MessageId=T2.MessageId
    AND
    T5.IsPending=1
    ) AS IsPending
    FROM dbo.tblMessageDetails T1 LEFT OUTER JOIN dbo.tblMessageInbox T2
    ON T1.MessageID=T2.MessageID
    LEFT OUTER JOIN uvw_UserCompleteInformation T3
    ON T2.FromUserID=T3.UserID
    LEFT OUTER JOIN uvw_UserCompleteInformation T4
    ON T2.UserID=T4.UserID
    WHERE MessageType=’M’

    UNION ALL

    SELECT
    DISTINCT T2.MessageID,
    T1.MessageSubject,
    T1.MessageBody,
    T1.PostedDateTime,
    T2.ToUserIDs AS PostedForIds,
    T2.ToUserNames AS PostedForNames,
    T2.UserID AS PostedById,
    CASE T3.IsOrganization
    WHEN 1 THEN ISNULL(T3.CompanyName,ISNULL(T3.FirstName + ‘ ‘ + T3.LastName,T3.UserName))
    ELSE ISNULL(T3.FirstName + ‘ ‘ + T3.LastName,T3.UserName)
    END AS PostedByName,
    (SELECT COUNT(*) FROM dbo.fnSplit(T2.ToUserIDs,’,’)) AS SentToCount,
    0 AS IsPending
    FROM dbo.tblMessageDetails T1 LEFT OUTER JOIN dbo.tblMessageSentbox T2
    ON T1.MessageID = T2.MessageID
    LEFT OUTER JOIN uvw_UserCompleteInformation T3
    ON T2.UserID = T3.UserID
    WHERE
    T2.MessageID NOT IN
    (
    SELECT DISTINCT(MessageID) FROM dbo.tblMessageInbox
    )
    AND T1.MessageType=’M’
    ) AS T
    WHERE
    T.PostedForIds IS NOT NULL
    AND
    T.PostedDateTime BETWEEN @local_FromDate AND @local_ToDate
    AND
    (
    T.MessageSubject LIKE ‘%’+
    CASE
    WHEN @local_SearchText =”
    THEN T.MessageSubject
    ELSE @local_SearchText
    END + ‘%’
    OR
    T.MessageBody LIKE ‘%’+
    CASE
    WHEN @local_SearchText =”
    THEN T.MessageBody
    ELSE @local_SearchText
    END + ‘%’
    )
    AND
    T.PostedByName LIKE ‘%’+
    CASE
    WHEN @local_Sender =”
    THEN T.PostedByName
    ELSE @local_Sender
    END + ‘%’
    AND
    T.PostedForNames LIKE ‘%’+
    CASE
    WHEN @local_Receiver =”
    THEN T.PostedForNames
    ELSE @local_Receiver
    END + ‘%’
    AND
    IsPending=@local_IsPending

    SELECT @MessageCount
    END


    Thank You,
    Amol

  52. Hi Pinal,

    Could you please tell me the “script ” for long running Queries/Stored procedures from last 24 hrs .It will help us to optimize Stored procedures.

  53. Nice topic
    I face same problem, Please check my Procedure

    ALTER PROCEDURE [dbo].[SP_ExtendedPTU]
    (
    @SQLForPTU as varchar(MAX)
    )
    AS
    BEGIN TRAN
    –DECLARE
    –@SQLForPTU as varchar(MAX)
    –SET @SQLForPTU= ‘WHERE OrderID IN(4233)And OrderType =3′

    BEGIN–1
    DECLARE
    @ProductionTracingUnitID as int,
    @OrderID as int,
    @OrderType as smallint,
    @ProductID as int,
    @LabLabDipID as int,
    @ColorName as varchar(255),
    @PantonNo as varchar(127),
    @Shade as varchar(4),
    @DyeingOrderQty as decimal(30, 17),
    @JobOrderQty as decimal(30, 17),
    @ProductionPipeLineQty as decimal(30, 17),
    @ProductionFinishedQty as decimal(30, 17),
    @DeliveryQty as decimal(30, 17),
    @BuyerID as int,
    @FactoryID as int,
    @ProductionGraceQty as decimal(30, 17),
    @WeightLossGainQty as decimal(30, 17),
    @RateInLBS as decimal(30, 17),
    @State as smallint,
    @ProductionLossQty as decimal(30, 17),
    @ReturnQty as decimal(30, 17),
    @ActualDeliveryQty as decimal(30,17),
    @ReadyStockInhand as decimal(30, 17),
    @JobOrderQtyApproved as decimal(30, 17),
    @OrderNumber as varchar(50),
    @FactoryName as varchar(200),
    @BuyerName as varchar(200),
    @ProductName as varchar(200),
    @YetToDelivery as decimal(30,17),
    @StockInHand as decimal(30,17),
    @YetToProduction as decimal(30,17),
    @LCID as int,
    @LCNo as varchar(300),
    @PIQty as decimal(30,17),
    @ChangingQty as decimal(30,17),
    @SampleAdjQty as decimal(30,17),
    @SampleAdjValue as decimal(30,17),
    @MKTPersonID as int,
    @MKTPersonName as varchar(500),
    @MerchandiserID as int,
    @MerchandiserName as varchar(500),
    @AmendmentStatus as smallint,
    @AcceptanceValue as decimal(30,17),
    @MaturityValue as decimal(30,17),
    @BillAcceptanceValue as decimal(30,17),
    @BillMaturityValue as decimal(30,17),
    @BillAcceptancePercentage as decimal(30,17),
    @BillMaturityPercentage as decimal(30,17),
    @ExportLCValue as decimal(30,17),
    @Acceptance as varchar(100),
    @Maturity as varchar(100),
    @YarnCount as varchar(50),
    @PTUDQty as decimal(30,17),
    @ShadeFromOrder as smallint,
    @EWYDLRelabNo as varchar(100),
    @EWYDLColorNo as varchar(100),
    @DeliveryTo as int,
    @FactoryPersonnelID as int,
    @BuyerPersonnelID as int,
    @OrderRcvBy as int,
    @OrderState as smallint

    CREATE TABLE #TempTableOne(
    ProductionTracingUnitID int,
    OrderID int,
    OrderType smallint,
    ProductID int,
    DyeingOrderQty decimal(30,17),
    JobOrderQty decimal(30,17),
    ProductionPipeLineQty decimal(30,17),
    ProductionFinishedQty decimal(30,17),
    DeliveryQty decimal(30,17),
    BuyerID int,
    FactoryID int,
    ProductionGraceQty decimal(30,17),
    WeightLossGainQty decimal(30,17),
    RateInLBS decimal(30,17),
    ProductionLossQty decimal(30,17),
    ActualDeliveryQty decimal(30,17),
    ReadyStockInhand decimal(30,17),
    OrderNumber varchar(50),
    FactoryName varchar(200),
    BuyerName varchar(200),
    ProductName varchar(200),
    ColorName varchar(200),
    LabLabDipID int,
    ReturnQty decimal(30,17),
    YetToDelivery decimal(30,17),
    StockInHand decimal(30,17),
    YetToProduction decimal(30,17),
    LCID int,
    LCNo varchar(300),
    PIQty decimal(30,17),
    ChangingQty decimal(30,17),
    SampleAdjQty decimal(30,17),
    SampleAdjValue decimal(30,17),
    MKTPersonID int,
    MKTPersonName varchar(500),
    MerchandiserID int,
    MerchandiserName varchar(500),
    AmendmentStatus smallint,
    Acceptance varchar(100),
    Maturity varchar(100),
    YarnCount varchar(50),
    EWYDLRelabNo varchar(50),
    EWYDLColorNo varchar(50),
    ShadeFromOrder smallint
    )

    –ProductionTracingUnitID,OrderID,OrderType,ProductID,LabLabDipID,ColorName,PantonNo,EWYDLColorNo,Shade,EWYDLRelabNo,DyeingOrderQty,JobOrderQty,ProductionPipeLineQty,ProductionFinishedQty,DeliveryQty,BuyerID,FactoryID,ProductionGraceQty,WeightLossGainQty,RateInLBS,State,ProductionLossQty,ShadeFromOrder,ReturnQty,ActualDeliveryQty,ReadyStockInhand,JobOrderQtyApproved
    DECLARE
    @SQL as varchar(MAX)
    SET @SQL=

    DECLARE Cur_AB1 CURSOR GLOBAL FORWARD_ONLY KEYSET FOR
    SELECT ProductionTracingUnitID,OrderID,OrderType,ProductID,DyeingOrderQty,JobOrderQty,ProductionPipeLineQty,ProductionFinishedQty,BuyerID,FactoryID,ProductionGraceQty,WeightLossGainQty,RateInLBS,ProductionLossQty,ActualDeliveryQty,ReadyStockInhand,ColorName,LabLabDipID,ReturnQty,EWYDLRelabNo,EWYDLColorNo,ShadeFromOrder FROM ProductionTracingUnit ‘+@SQLForPTU+’

    EXEC (@SQL)

    OPEN Cur_AB1
    FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
    WHILE(@@Fetch_Status -1)
    BEGIN–2
    SET @LCID=0
    SET @LCNo=”
    SET @PIQty=0
    SET @AcceptanceValue =0
    SET @MaturityValue= 0
    SET @Acceptance= ”
    SET @Maturity =”

    SET @DeliveryQty=@ActualDeliveryQty-@ReturnQty
    SET @YetToDelivery=@JobOrderQty-@ActualDeliveryQty+@ReturnQty
    set @PTUDQty=(select sum(Qty) from PTUDistribution where ProductionTracingUnitID=@ProductionTracingUnitID )
    IF(@PTUDQty>@YetToDelivery)
    BEGIN–sih
    SET @StockInHand =@YetToDelivery
    END –sih
    ELSE
    BEGIN–sih2
    SET @StockInHand =@PTUDQty
    END –sih2
    SET @YetToProduction=@JobOrderQty-@ReadyStockInhand-@ActualDeliveryQty+@ReturnQty
    IF (@YetToProduction0 and @ExportLCValue is not null)
    BEGIN
    SET @BillAcceptancePercentage =(@BillAcceptanceValue*100)/@ExportLCValue — bill Percentage
    SET @BillMaturityPercentage =(@BillMaturityValue*100)/@ExportLCValue

    SET @AcceptanceValue=(@ChangingQty*@RateInLBS)*(@BillAcceptancePercentage/100)–Percentage Wise PI Valu
    SET @MaturityValue=(@ChangingQty*@RateInLBS)*(@BillMaturityPercentage/100)

    IF((@ChangingQty*@RateInLBS)>0 and (@ChangingQty*@RateInLBS) is not null)
    BEGIN
    SET @AcceptanceValue=(@AcceptanceValue*100)/(@ChangingQty*@RateInLBS)– PI ValuePercentage
    SET @MaturityValue=(@MaturityValue*100)/(@ChangingQty*@RateInLBS)
    END

    SET @Acceptance=Convert(varchar(20),(CONVERT(float,round((@AcceptanceValue+@MaturityValue),0)))) +’%’
    SET @Maturity =Convert(varchar(20),(CONVERT(float,round(@MaturityValue,0)))) +’%’
    END

    SET @FactoryName=”
    IF(@FactoryID>0)
    BEGIN–jam3
    SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
    END–jam3

    SET @BuyerName=”
    IF(@BuyerID>0)
    BEGIN–jam4
    SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
    END–jam4

    END–jam1
    ELSE
    BEGIN –jam2 IF Sample
    SET @LCID=0
    SET @LCNo=”
    SET @OrderNumber=”
    SET @PIQty=0
    SET @DeliveryTo=0
    SET @FactoryPersonnelID=0
    SET @BuyerPersonnelID=0
    SET @OrderRcvBy=0
    SET @ChangingQty=0
    SET @MerchandiserName=”
    SET @MKTPersonName=”
    SET @OrderState=0
    SET @AmendmentStatus=0
    SET @PIQty= (SELECT ISNULL(SUM(Qty),0) FROM SampleOrderDetail WHERE PTUID=@ProductionTracingUnitID)
    SET @ChangingQty=@PIQty
    SELECT @OrderNumber=ISNULL(SampleOrderNo,”), @DeliveryTo=ISNULL(DeliveryTo,0),@FactoryPersonnelID=ISNULL(FactoryPersonnelID,0),@BuyerPersonnelID=ISNULL(BuyerPersonnelID,0),@OrderRcvBy=ISNULL(OrderRcvBy,0),@OrderState=ISNULL(OrderState,0) FROM SampleOrder WHERE SampleOrderID=@OrderID
    SET @AmendmentStatus=@OrderState
    IF(@DeliveryTo=3)
    BEGIN
    SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@FactoryPersonnelID)
    END

    IF(@DeliveryTo=2)
    BEGIN
    SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@BuyerPersonnelID)
    END
    SET @MKTPersonName =(SELECT [Name] FROM Employee WHERE EmployeeID=@OrderRcvBy)

    IF(@DeliveryTo=3)
    BEGIN
    SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
    END

    IF(@DeliveryTo=2)
    BEGIN
    SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
    END

    SET @BuyerName=”
    IF(@BuyerID>0)
    BEGIN–jam4
    SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
    END–jam4
    END–jam2

    SET @ProductName=”
    SET @YarnCount =”
    SELECT @ProductName=(‘[‘+ Code+ ‘] ‘+ [Name]), @YarnCount =[Count] FROM Yarncategory WHERE YarncategoryID=@ProductID

    INSERT INTO #TempTableOne Values(ISNULL(@ProductionTracingUnitID,0),ISNULL(@OrderID,0),ISNULL(@OrderType,0),ISNULL(@ProductID,0),ISNULL(@DyeingOrderQty,0),ISNULL(@JobOrderQty,0),ISNULL(@ProductionPipeLineQty,0),ISNULL(@ProductionFinishedQty,0),ISNULL(@DeliveryQty,0),ISNULL(@BuyerID,0),ISNULL(@FactoryID,0),ISNULL(@ProductionGraceQty,0),ISNULL(@WeightLossGainQty,0),ISNULL(CONVERT (decimal(18,2),@RateInLBS),0),ISNULL(@ProductionLossQty,0),ISNULL(@ActualDeliveryQty,0),ISNULL(@ReadyStockInhand,0),ISNULL(@OrderNumber,”),ISNULL(@FactoryName,”),ISNULL(@BuyerName,”),ISNULL(@ProductName,”),ISNULL(@ColorName,”),ISNULL(@LabLabDipID,0),ISNULL(@ReturnQty,0),ISNULL(@YetToDelivery,0),ISNULL(@StockInHand,0),ISNULL(@YetToProduction,0),ISNULL(@LCID,0),ISNULL(@LCNo,”),ISNULL(@PIQty,0),ISNULL(@ChangingQty,0),ISNULL(@SampleAdjQty,0),ISNULL(@SampleAdjValue,0),ISNULL(@MKTPersonID,0),ISNULL(@MKTPersonName,”),ISNULL(@MerchandiserID,0),ISNULL(@MerchandiserName,”),ISNULL(@AmendmentStatus,0),ISNULL(@Acceptance,”),ISNULL(@Maturity,”),ISNULL(@YarnCount,”),ISNULL(@EWYDLRelabNo,”),ISNULL(@EWYDLColorNo,”),ISNULL(@ShadeFromOrder,0))
    FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
    END–2
    CLOSE Cur_AB1
    DEALLOCATE Cur_AB1
    SELECT * FROM #TempTableOne Order By OrderID
    –Group By Product
    SELECT ProductID,ProductName, YarnCount, SUM(PIQty) as PIQty, SUM(ChangingQty) AS ChangingQty, SUM(SampleAdjQty) AS SampleAdjQty, SUM(SampleAdjValue) as SampleAdjValue, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY ProductID,ProductName,YarnCount Order By ProductID
    –Group By Factory
    SELECT FactoryID,FactoryName,SUM(PIQty) as PIQty, SUM(ChangingQty) AS ChangingQty, SUM(SampleAdjQty) AS SampleAdjQty, SUM(SampleAdjValue) as SampleAdjValue, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY FactoryID,FactoryName
    –Group By Order
    SELECT OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,SUM(PIQty) AS PIQty,SUM(ChangingQty) AS ChangingQty,SUM(SampleAdjQty) AS SampleAdjQty,SUM(SampleAdjValue) AS SampleAdjValue,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity,SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY OrderID,OrderNumber,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity
    –Default View
    SELECT OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,ProductID,ProductName,YarnCount,PIQty,ChangingQty,SampleAdjQty,SampleAdjValue,RateInLBS,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,ProductID,ProductName,YarnCount,PIQty,ChangingQty,SampleAdjQty,SampleAdjValue,RateInLBS,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity
    DROP TABLE #TempTableOne
    END–1
    COMMIT TRAN

    Note : At a time i want to get more than 500000 record from this store procedure .

  54. Hi Pinal,
    I am required to write stored procedure in sql server to get:
    -the number of sessions on daily and hourly basis
    -number of unique users
    -Number of first time users.
    i have a table with 100’s of rows & columns :
    RequestID,ApplicationID,SessionID,UserId,UserName,RequestURL,RequestTimeStamp,InsertTimestampGMT,UpdateTimestampGMT,Idx
    Please help!

  55. Hi,

    is there any difference between
    select top 1 * from table where condition

    and

    select 1 from table where condition

  56. Any problem if I use the prefix “sp” in the stored procedure name: If a stored procedure name begins with “SP,” will the SQL server first searches in the master database??? For example : spFact.StoredProcNameA and spRpt.StoredProcNameB

  57. Hello Pinal,
    I have a question regarding stored procedure.

    I have a stored proc which is about 2000 lines of code. It has several set of logic embedded into it.
    My question is is it better to break this stored procs into different stored procs and run them as part of a stored proc.

    Can i break this proc into 3 different stored procs of 500 lines of code each and run it the same proc. Hope my question is clear

  58. just an example… I have a stored proc NewUser which lets say takes a new user. In the next steps of the same proc it adds the user to login table,notify’s the admin that there is a new user,and several other steps.

    Can I break this adding user to login table… and notifying the admin.. into different stored procs but run them as different steps inside the NewUser stored proc.

    Is there any advantage doing this?

  59. Hi, I have this procedure which is used to display a report. its taking an acronym parameter. the procedure is as below:

    CREATE PROCEDURE [dbo].[RISCAppRISCRatingHistory]
    (
    @Acronym varchar(500)
    )
    as
    begin
    SET ARITHABORT ON

    select DISTINCT Acronym,Rating as RISCRating,
    ProductionAccess as [Production access restrictions],
    NonProductionAccess as [Non-production access restrictions],
    Status,
    CUID as [User ID],[TimeStamp], CONVERT(datetime, [timestamp]) as [Timestamp_Sort]
    From dbo.RISC_Long_Rating_History
    where Acronym=@Acronym AND Status IN(‘Accepted’,’Rejected’) and Rating”
    ORDER BY [Timestamp_Sort] desc

    end

    now i want to add an condtion to check for a particular rating in d table and display only particular columns if the acronym has tht rating, else display al the columns of the table.

    so i modified the above procedure like this:

    ALTER PROCEDURE [dbo].[RISCAppRISCRatingHistory]
    (
    @Acronym varchar(500)
    )
    as
    begin
    SET ARITHABORT ON

    if(Rating IN (‘Critical Infrastructure Information Theft’,’Network Sabotage’))
    BEGIN
    select DISTINCT Acronym,Rating as RISCRating,
    ProductionAccess as [Production access restrictions],
    NonProductionAccess as [Non-production access restrictions],
    Status,
    CUID as [User ID],[TimeStamp], CONVERT(datetime, [timestamp]) as [Timestamp_Sort]
    From dbo.RISC_Long_Rating_History
    where Acronym=@Acronym AND Status IN(‘Accepted’,’Rejected’) and Rating”
    ORDER BY [Timestamp_Sort] desc
    END

    else
    BEGIN
    select DISTINCT Acronym,Rating as RISCRating,
    ProductionAccess as [Production access restrictions],
    NonProductionAccess as [Non-production access restrictions],
    Status,
    CUID as [User ID],[TimeStamp], CONVERT(datetime, [timestamp]) as [Timestamp_Sort]
    From dbo.RISC_Long_Rating_History
    where Acronym=@Acronym AND Status IN(‘Accepted’,’Rejected’) and Rating”
    ORDER BY [Timestamp_Sort] desc
    END
    end

    but im getting an error tht rating is not a valid column name in the if condition.

    Can anyone help?

  60. Hi Pinal Dave sir, Its very pleasure in reading topics in your blog. I always got doubts how to check which query executes fast. I am biggest fan of the stored procedure. But when it comes to write complex logic, I usually have more than one way to get the result. So how can i check the performance. Could you suggest easy way. I don’t want use extra code for checking performance.

  61. Hi,

    Pinal can we use select * statement in stored procedure?
    and if we want all the field from the table by having only one inout parameter, then at the time of execution it is require to declear all the output attributes
    or to declear only the input value inthe case if we use the select * query.
    Please reply .
    I am new for stored procedure.

    • Hi Sandesh, since I don’t see any reply to your question I’ll answer you.

      You dont have to declare all the output attributes because you are selecting from a table, so the return attributes come from the result of the select.

      But it’s not a good practice to use “SELECT *”, for performance reasons and unexpected behaviors in case you table change.

  62. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  63. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  64. Hi
    I’m currently working on the data migration project.

    I’m using cursor for looping so that I can massage the data record by record and also migrate the child data together.
    From your article, you mention try to avoid using cursors, should replace with SET-based approach.
    Can you please tell you should I use SET-based in below sample

    Sample

    Account_tbl
    Account_ID
    Account_Name
    Tax_Exempt_IND
    Duty_Exempt_IND
    Status
    Status_Change_Date
    Reference_Text

    Account_Tel_Number_tbl
    Tel_Number_ID
    Account_ID
    Tel_Number
    Status

    My stored procedure

    SET NOCOUNT ON
    SELECT Account_ID, Account_Name, Tax_Exempt_IND , Duty_Exempt_IND , Status, Status_Change_Date, Reference_Text
    INTO #Account_Temp
    FROM Account_tbl
    WHERE ((Status IN (‘A’, ‘F’, ‘U’) OR
    (Status IN (‘C’, ‘E’, ‘R’) AND DATEDIFF(MONTH, Status_Change_Date, GETDATE()) <= 6))

    UPDATE #Account_Temp
    SET Tax_Exempt_IND = ‘N’,
    Duty_Exempt_IND = ‘N’,
    Reference_Text = ‘’

    DECLARE Account_Cursor FOR
    SELECT Account_ID, Account_Name, Tax_Exempt_IND , Duty_Exempt_IND , Status, Status_Change_Date, Reference_Text
    FROM #Account_Temp

    OPEN Account_Cursor

    FETCH NEXT FROM Account_Cursor
    INTO @Account_ID, @Account_Name, @Tax_Exempt_IND, @Duty_Exempt_IND , @Status, @Status_Change_Date, @Reference_Text

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC [dbo].[Transform_Account_Tel_Number] @Account_ID

    INSERT INTO [Converted_Database].[dbo].[Account_tbl] (Account_ID, Account_Name, Tax_Exempt_IND , Duty_Exempt_IND , Status, Status_Change_Date, Reference_Text)
    VALUES (@Account_ID, @Account_Name, @Tax_Exempt_IND, @Duty_Exempt_IND , @Status, @Status_Change_Date, @Reference_Text)

    FETCH NEXT FROM Account_Cursor
    INTO @Account_ID, @Account_Name, @Tax_Exempt_IND, @Duty_Exempt_IND , @Status, @Status_Change_Date, @Reference_Text

    END

    CLOSE Account_Cursor;
    DEALLOCATE Account_Cursor;

    DROP #Account_Temp

    SET NOCOUNT OFF

    This [dbo].[Transform_Account] will loop the data in Account_tbl table and called [dbo].[Transform_Account_Tel_Number] stored procedure.
    In [dbo].[Transform_Account_Tel_Number], it will do another loop to migrate the records in [dbo].[Account_Tel_Number_tbl] records by records.

    Can you please tell me how to use SET-based approach for this??

    My current problem is, I have huge database to loop.
    I have more than 2mils account in [dbo].[Account_tbl], each Account has about 6 child table to loop. Each of them also has more than 2mils to loop. It takes more than 10 hours to finish.
    I’m look at how to make it fast.

    Thanks for help.

  65. Hi,

    I have a procedure which runs successfully 95% of the time. But times out some times. Getting the error ‘System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding’. I have set sql connection timeout to 2 mins and max pool size as 250 in web.config.

    Below is the procedure:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROC [dbo].[spFunChat_SearchUsers]
    (
    @UserID int,
    @Gender char(1),
    @MinAge int,
    @MaxAge int,
    @CurUserGender char(1),
    @CurUserAge int,
    @CurUserLocation varchar(100)
    )
    AS
    BEGIN
    DECLARE @ModifiedMinAge int
    DECLARE @ModifiedMaxAge int
    DECLARE @IsAgeWeightage bit

    SET NOCOUNT ON;
    If (@MinAge = 16 and @MaxAge =99)
    BEGIN
    Select @IsAgeWeightage = 1, @ModifiedMinAge = @CurUserAge – 2, @ModifiedMaxAge = @CurUserAge + 5
    END

    SELECT ChatUserID, Age,Location,Gender,UserID,Status,GenderWeightage,AgeWeightage,LocationWeightage,ID, GenderWeightage + AgeWeightage + LocationWeightage as TotalWeightage
    FROM
    (
    SELECT
    fcu.ChatUserID,fcu.Age,fcu.Location,fcu.Gender,fcu.UserID,fcfl.Status, fcu.ID,
    GenderWeightage =
    CASE
    WHEN @Gender = ‘A’ and @CurUserGender = ‘M’ and fcu.Gender = ‘F’ THEN 50
    WHEN @Gender = ‘A’ and @CurUserGender = ‘F’ and fcu.Gender = ‘M’ THEN 50
    ELSE 0
    END,
    AgeWeightage =
    CASE
    WHEN @IsAgeWeightage = 1 and fcu.Age between @ModifiedMinAge and @ModifiedMaxAge THEN 25
    ELSE 0
    END,
    LocationWeightage =
    CASE
    WHEN @IsAgeWeightage = 1 and @Gender = ‘A’ and fcu.Location = @CurUserLocation THEN 25
    ELSE 0
    END
    From dbo.FunChatUsers fcu
    LEFT OUTER JOIN dbo.FunChatFriendsList fcfl
    On fcu.UserID = fcfl.FriendID and fcfl.UserID = @UserID
    Where fcu.IsLoggedIn = 1
    and fcu.UserID@UserID
    and (fcu.age>=@MinAge and fcu.Age<=@MaxAge) and (@gender = fcu.gender OR @Gender='A')
    and fcu.UserID NOT IN (Select FriendID from dbo.FunChatFriendsList where UserID = @UserID and Status = 'Blocked')
    ) Search
    order by TotalWeightage desc, ID

    END

    Any help would be much appreciated.

    Thanks

  66. very informative…I am trying to optimize a query written by my team member that is taking 28 hours to run and I am not sure whether I should change the logic or just use the optimizatin techniques :(

  67. Hi, how can we avoid cursor from the following procedure?

    CREATE PROCEDURE [dbo].[TestLeave] AS
    SET NOCOUNT ON
    DECLARE @MemberID INT, @TypeID INT, @StartDate DATETIME, @ENDDATE DATETIME
    DECLARE CUR_EMPLOYEELEAVE CURSOR FOR
    SELECT MemberId, TypeID, StartDate, EndDate
    FROM Leave
    OPEN CUR_EMPLOYEELEAVE
    FETCH NEXT FROM CUR_EMPLOYEELEAVE
    INTO @MemberId, @TypeID, @StartDate, @EndDate
    WHILE @@FETCH_STATUS = 0
    BEGIN

    WHILE CONVERT(VARCHAR, @StartDate, 101) CONVERT(VARCHAR, @EndDate, 101)
    BEGIN

    INSERT INTO #TempLeave (MemberId, TypeID, StartDate,EndDate )
    VALUES (@MemberId, @TypeID, @StartDate, DATEADD(hour, 10, @StartDate))
    SET @StartDate = DATEADD(day, 1, @StartDate)
    END
    IF CONVERT(VARCHAR, @StartDate, 101) = CONVERT(VARCHAR, @EndDate, 101)

    BEGIN
    INSERT INTO #TempLeave (MemberId, TypeID, StartDate, EndDate )
    VALUES (@MemberId, @TypeID, @StartDate, @EndDate)

    END
    FETCH NEXT FROM CUR_EMPLOYEELEAVE
    INTO @MemberId, @TypeID, @StartDate, @EndDate
    END
    CLOSE CUR_EMPLOYEELEAVE
    DEALLOCATE CUR_EMPLOYEELEAVE
    SET NOCOUNT OFF

  68. Hello Brother,

    Store procedure taking 5 minutes to execute but if query of which i written procedure then it will take on 4 sec. I did google and find parameters sniffing for improving procedures performance so implemented that and now it still taking 3 minutes.
    What should i do?

    Thanks
    Atul yadav

  69. Hello Pinal Sir,
    I have 2 Simple Query that i wants to optimize and made into Single.
    I want to reduce the time( 2 Time Selection + 2 time Index Scanning ) that taken by both Update Query on Single Table.

    I wants Query 1 + Query 2 = SQLAuthority_Query.

    Always my final and Last hope is always you and your blog…..

    Query 1 : Deduct Amount from Source Customer Account
    =================================================
    Update dbo.T_BANK
    SET Balance= Balance-@TAmount
    WHERE Bank_Customer_ID=@Source_Customer_ID

    Query 2 : Amount Added to the Destination Customer Account
    =================================================
    Update dbo.T_BANK
    SET Balance= Balance+@TAmount
    WHERE Bank_Customer_ID=@Dest_Customer_ID

  70. Very helpful article, especially about not starting a stored proc’s name with ‘sp_’. Working at this for 12 years I hadn’t realized that.
    By the way, as other’s noted, I found a tremendous performance boost after adding temp table indexing even though the proc used the table only once, due to it being joined to other tables.
    Didn’t always prove worth it, I supposed if the join wasn’t a big one, but here and there it made a big difference.

  71. hi,
    while SET NOCOUNT ON , @@Rowcount has been updated. so It may reduce the performance. I dont know exactly. Please suggest me.

    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s