How to speed up a multiple select query 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 &gt; 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 &gt; 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('&lt;x&gt;'+replace(@Vids, ';', '&lt;/x&gt;&lt;x&gt;')+'&lt;/x&gt;' 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)&gt;0
    BEGIN
      SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
      SET @value = SUBSTRING(@Docs, @pos, @len)

      IF CHARINDEX('|', @value) &gt;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.
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.