2

EDIT - Putting my whole stored procedure:

I have the below stored procedure:

CREATE PROCEDURE [dbo].[data_Update] 
  @Owner NVARCHAR(255),
  @ID VARCHAR(255),
  @Division VARCHAR(255),
  @Manager VARCHAR(255),
  @TeamMembers VARCHAR(255)
  AS
  BEGIN 
    DECLARE @OwnerName VARCHAR(255),
    @ManagerName VARCHAR(255),
    @Country VARCHAR(255),
    @TEMP VARCHAR(255),
    @Vendor1 VARCHAR(100),
    @Vendor2 VARCHAR(100),
    @Vendor3 VARCHAR(100),
    @INDEX INT,
    @Division1 INT,
    @Division2 INT

    SELECT @Manager = NAME, @Country = Country FROM Users WHERE EMAILID = @Manager 
    SELECT @Owner = NAME FROM Users WHERE EMAILID = @Owner


    IF(@Division IS NOT NULL)
    BEGIN
        SELECT @INDEX = CHARINDEX('-', @Division)
        IF(@INDEX > 0)
        BEGIN
            SELECT @TEMP = SUBSTRING(@Division, 1, @INDEX - 1)
            SELECT @Division1 = ID FROM DivisionOne WHERE Value = @TEMP
        END
        IF(@INDEX = 0)
            SELECT @Division1 = ID FROM DivisionOne WHERE Value = @Division 
        IF(@INDEX > 0)
        BEGIN       
            SELECT @TEMP = SUBSTRING(@Division, @INDEX + 1, LEN(@Division))         

            SELECT @Division2 = ID FROM DivisionTwo
            WHERE Value = @TEMP AND Division1 = @Division1 
        END         
    END

    DECLARE @MemberEmails TABLE(col VARCHAR(255))
    DECLARE @MemberNames TABLE(col VARCHAR(255))
    DECLARE @Names VARCHAR(255)

    INSERT INTO @MemberEmails
    SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

    INSERT INTO @MemberNames
    SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

    SELECT @Names =
       STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
       FROM   @MemberNames b        
       FOR XML PATH('')),1,1,'') 

    DECLARE @Vendors TABLE(col varchar(50))
    DECLARE @itm1 int, @itm2 int, @itm3 int, 
            @Vids varchar(50)
    INSERT INTO @Vendors
      SELECT VendorID from VendorMapping where ID = @ID
    SELECT @Vids = STUFF((SELECT '; ' + col
                FROM   @Vendors        
                FOR XML PATH('')),1,1,'') 
    SELECT @itm1 = X.value('x[1]', 'int'),
           @itm2 = X.value('x[2]', 'int'),
           @itm3 = X.value('x[3]', 'int')
      from (select cast('<x>'+replace(@Vids, ';', '</x><x>')+'</x>' as xml)) as T(X)

    SELECT @Vendor1 = Name FROM Vendors WHERE Id = @itm1
    SELECT @Vendor2 = Name FROM Vendors WHERE Id = @itm2
    SELECT @Vendor3 = Name FROM Vendors WHERE Id = @itm3

    -- Populate tblDocuments
    DECLARE @Docs VARCHAR(MAX)
    DECLARE @pos INT
    DECLARE @len INT
    DECLARE @type VARCHAR(100)
    DECLARE @value VARCHAR(8000)
    DECLARE @name VARCHAR(1000)
    SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID 
    SET @Docs = @Docs + '$'
    SET @pos = 0
    SET @len = 0

    WHILE CHARINDEX('$', @Docs, @pos + 1)>0
    BEGIN
      SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
      SET @value = SUBSTRING(@Docs, @pos, @len)

      IF CHARINDEX('|', @value) >0
      BEGIN
        SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
                @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
        INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)
      END   

      SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1
    END

    --- update table
    UPDATE tblProjects SET OwnerName = @Owner, OwnerCountry = @Country, 
    ManagerName = @Manager, Division1ID = @Division1,
    Division2ID = @Division2, Team = @Names, Vendor1 = @Vendor1, 
    Vendor2 = @Vendor2, Vendor3 = @Vendor3 WHERE ID = @ID
  END

I am running this for every row in tblProjects, inside an SSIS job. Currently this step takes a lot of time to complete, for the whole set of records. Anything that can even slightly speed up the queries is appreciated.

I have added a SET NOCOUNT ON; in the procedure, and it seems to have given a slight performance boost.

  • Are these queries slow for you right now? If they are you should add an index on Id. – Mikael Eriksson Feb 17 '15 at 14:05
  • @MikaelEriksson, please see the edit. That Id column is not necessarily the slowest part of my sp. – rohinMVP Feb 17 '15 at 14:14
  • Just to clarify, do you mean that you run SELECT @Vendor3 = Name FROM Vendors WHERE Id = @param3; (or similar query) for every row of the table? Or for "hundreds of sets of the parameters"? – ypercubeᵀᴹ Feb 17 '15 at 15:30
  • If yes, it will be probably much more efficient to rewrite on a set-based logic. I suggest you make a new question where you explain what "my goal is to take data from different tables to update a row in a certain table" exactly means. With tables' definitions, sample data and wanted output (what columns need change, etc.) – ypercubeᵀᴹ Feb 17 '15 at 15:32
3

It's quite difficult to give any suggestions based on that information, but here's the approach I normally use for performance tuning. The order of different steps can vary, but these usually give quite a good idea where the improvements should be done.

1) Check for blocking

if someone else is blocking the query, you should focus on the blocking query first

2) Set statistics io on.

This will show you the I/O count per table per statement when you run the procedure. If your sql is simple statements, I/O count and time spent in that table should be quite linear. Amount of data in cache makes a big difference for first vs. second run. If you're using user defined functions, I/O generated by those is not shown with statistics io.

3) Query plan, preferably actual plan.

Look at think arrows (=a lot of rows). There might be something to improve here, indexing, statistics, something to adjust in the query itself. This should be give similar picture as statistics io.

Key lookups, especially ones with big percentages or big actual execution count. If the number of output columns is small you could add include columns to the index. Remember, percentages are just estimates.

Look out for index / table scans, especially in cases where you're not reading most of the table.

4) Plan cache.

You can find statement level statistics from sys.dm_exec_query_stats. I usually look into total_worker_time and total_logical_reads, other columns can also give valuable information about where the problem could be.

2

You might speed up the SP by applying index and converting multiple select statements to single as all written for same purpose but as per your below statement you need to do more.

'It is not just this set of queries. I have multiple select and insert statements in my stored procedure; my goal is to take data from different tables to update a row in a certain table.'

Table Creation and Dummy Data insert:

create table Vendors
(
id int,
name varchar(200)
)
GO

insert into Vendors
select 1, 'Aml'
union all 
select 2,'Mark'
union all 
select 3, 'Ken'
union all 
select 4, 'Edward'
union all 
select 5,'Richard'
union all 
select 6, 'Alex'

GO

Rewriting\ conversion of 3 queries to single

DECLARE @Vendor1 varchar(10)
      , @Vendor2 varchar(10)
      , @Vendor3 varchar(10)
      , @param1 int
      , @param2 int
      , @param3 int

select @param1=6 ,@param2=2,@param3=1

SELECT @Vendor1 = (Case Id when @param1 then  Name else @Vendor1 end )
      ,@Vendor2 = (Case Id when @param2 then  Name else @Vendor2 end )
      ,@Vendor3 = (Case Id when @param3 then  Name else @Vendor3 end )
FROM Vendors 
WHERE Id = @param1 or Id = @param2 or Id = @param3


select @Vendor1 
      ,@Vendor2 
      ,@Vendor3

Index Required for Table Vendors to avoid 'Table Scan':

CREATE NONCLUSTERED INDEX [IX_ID_Name] ON [dbo].[Vendors] 
(
    [id] ASC
)
INCLUDE ( [name]) 
GO

Table Creation and Dummy Data insert:

create table Users
(
EMAILID int,
NAME varchar(200),
Country varchar(200)
)


insert into Users
select 1,'Mark', 'US'
union all
select 2, 'Ray','IN'
union all
select 3,'Tina','US'
union all
select 4, 'Collin','US'

Rewriting\ conversion of 2 queries to single

Declare  @Manager varchar(200)
       , @Owner varchar(200)
       , @ManagerEmail varchar(200)
       , @OwnerEmail varchar(200)
       , @Country varchar(200)


select @ManagerEmail=1 ,@OwnerEmail=4

SELECT  @Manager =(case EMAILID when @ManagerEmail then NAME else @Manager end),
        @Country =(case EMAILID when @ManagerEmail then Country else @Country end),
        @Owner  = (case EMAILID when @OwnerEmail then NAME else @Owner end)      
FROM Users 
WHERE EMAILID = @ManagerEmail
or EMAILID = @OwnerEmail



select @Manager,@Country,@Owner  

Index Required for Table Vendors to avoid 'Table Scan':

CREATE NONCLUSTERED INDEX [IX_EmailID_NameCountry] ON [dbo].[Users] 
(
    [EMAILID] ASC
)
INCLUDE ( [NAME],
[Country])
  • I have restructured the question. Hope that provides more information on the current logic. – rohinMVP Feb 18 '15 at 6:55
  • 1
    There few more things required .. structure and dummy data of Tables (Users,DivisionOne,DivisionTwo,fnSplitString,Vendors,tblProjects) along with SP(data_Update) execution parameters – AA.SC Feb 18 '15 at 7:31
0

Definitely follow JamesZ's suggestions. Also watch for parameter sniffing amd make sure your statistics are up to date.

You said in your post that you have many similar sp_'s

I think your first sp_ could get out of control if you started adding more and more vendors/paremeters. Why not make it an sp_ on its own and call it when needed or in a loop? It might not be faster but definitely cleaner.

create procedure usp_GET_VENDOR
    (
        @param int
    )
    as
    begin
        select name from vendors where id=@param
    end

You could do something similar with the owner and manager.

There are a few ways to get data from the sp_; OUTPUT, Inserting into a temp table, openquery or openrowset. This example is inserting it into a temp table. Using openrowset/openquery would be similar. Instead of the exec ... put your openrowset/openquery there.

declare @x table
    (
        sequence int identity(1,1) primary key
        , xx datetime
    )

insert into @x(xx)
    exec [dbo].[Find_Next_Business_Day] @dte_START_DATE = '01/01/2011', @int_BUSINESS_DAYS = 5

select * from @x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.