How to speed up a multiple select query

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.

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])

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