I have 4 tables. 1) tbl_administrators 2) tbl_designations 3) tbl_countries 4) tbl_admin_types.

There are 3 foreign keys in tbl_administrators corresponding to the primary keys of second, third and fourth tables.

To fetch the details of administrator from tbl_administrator, I can write the stored procedure in two ways.

Way 1
------
CREATE PROCEDURE [sp_show_logged_in_admin_user] (@param_admin_id int)
AS
SELECT
    tbl_administrators.full_name,
    (SELECT country_code FROM tbl_countries WHERE country_id=tbl_administrators.country_id) AS country_code,
    (SELECT designation FROM tbl_designations WHERE designation_id=tbl_administrators.designation_id) AS designation,
    (SELECT accessibility_level FROM tbl_admin_types WHERE admin_type_id=tbl_administrators.admin_type_id) AS accessibility_level
FROM
    tbl_administrators
WHERE
    tbl_administrators.administrator_id=@param_admin_i d
GO

Way 2
-----
CREATE PROCEDURE [sp_show_logged_in_admin_user1] (@param_admin_id int)
AS
SELECT
    tbl_administrators.full_name,
    tbl_countries.country_code,
    tbl_designations.designation,
    tbl_admin_types.accessibility_level
FROM
    (((tbl_administrators LEFT JOIN tbl_countries ON tbl_administrators.country_id=tbl_countries.countr y_id) LEFT JOIN tbl_designations ON tbl_administrators.designation_id=tbl_designations .designation_id) LEFT JOIN tbl_admin_types ON tbl_administrators.admin_type_id=tbl_admin_types.a dmin_type_id)
WHERE
    tbl_administrators.administrator_id=@param_admin_i d
GO

As data grows, which among them will give better performance ? (Currently, I cannot makeout any difference as there are only very few records)

Thanks

Madhu

Hello Madhu,

Its seems ur r requesting for only one record from administrator table. then there would not be much effect on performance, but

In ur way1:
U r using coorlated query, where all the 3 querys in select statement are executed for each row, so this will made ur query more slow if number of records increases in administrator table.

In ur way2:
where this will get all the records which r matched with administrator table, therefore much faster then way1.

and also put ur where clause ("tbl_administrators.administrator_id=@param_admin _id
") in ur first administrator joind ("tbl_administrators LEFT JOIN tbl_countries ON tbl_administrators.country_id=tbl_countries.countr y_id and tbl_administrators.administrator_id=@param_admin_i d").

Hope This will help u.

Amit Jain
Sr. Software Engineer

Dear Amit,

Thanks for the answer.

I am to generalise this so that I can use it in queries which return multiple records also. In my DB, there are a lot of master tables. So, I was worried about the performance.

Also, I never knew that I can put where condition in Join clause. Thanks for giving this new information.

Its better to use way2, if u have to fetch multiple records from administrator table.

Amit Jain
Sr. Software Engineer