Benchmark to test difference between querying three normalized tables v one denormalized table for 10K records

hospital_schema.sql

USE [Hospital]

GO
CREATE TABLE [dbo].[Admissions](
	[Id] [int] NOT NULL,
	[PatientId] [int] NOT NULL,
	[RoomId] [int] NOT NULL,
	[DateAdmitted] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DenormalizedAdmissions](
	[Id] [int] NOT NULL,
	[PatientName] [nvarchar](100) NOT NULL,
	[RoomName] [nvarchar](100) NOT NULL,
	[DateAdmitted] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Patients](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rooms](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [patient_id_index] ON [dbo].[Admissions]
(
	[PatientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [room_id_index] ON [dbo].[Admissions]
(
	[RoomId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Admissions]  WITH NOCHECK ADD FOREIGN KEY([PatientId])
REFERENCES [dbo].[Patients] ([Id])
GO
ALTER TABLE [dbo].[Admissions]  WITH NOCHECK ADD FOREIGN KEY([RoomId])
REFERENCES [dbo].[Rooms] ([Id])
GO

Program.cs

using BenchmarkDotNet.Running;

internal class Program
{
    private static void Main(string[] args)
    {
        //choose on command line from multiple benchmarks
        BenchmarkSwitcher.FromAssembly(typeof(Program).Assembly).Run();
    }
}

SQLJoinBenchmark.cs

using System;
using System.Data;
using System.Data.SqlClient;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Columns;
using BenchmarkDotNet.Configs;
using BenchmarkDotNet.Jobs;
using BenchmarkDotNet.Reports;

namespace BenchmarkDotNet.Samples
{
    [Config(typeof(Config))]
    [SimpleJob(RuntimeMoniker.Net70)]
    public class SQLJoinBenchmarks
    {
        private string SQLConnectionString 
            = "Data Source=(localdb)\\mssqllocaldb;Database=hospital;Integrated Security=sspi;";

        [Benchmark]
        public void Read_Normalized()
        {
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {
                connection.Open();
                using (SqlCommand command = 
                    new SqlCommand("SELECT p.Name as PatientName, r.Name as RoomName, a.DateAdmitted " +
                                                          "FROM Admissions a " +
                                                          "JOIN Patients p ON a.PatientId = p.Id " +
                                                          "JOIN Rooms r ON a.RoomId = r.Id", connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // Do something with the data
                        }
                    }
                }
            }
        }

        [Benchmark(Baseline = true)]
        public void Read_Denormalized()
        {
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {
                connection.Open();
                using (SqlCommand command = 
                    new SqlCommand("SELECT PatientName, RoomName, DateAdmitted FROM DenormalizedAdmissions", connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // Do something with the data
                        }
                    }
                }
            }
        }

        [GlobalSetup(Target = nameof(Read_Normalized))]
        public void GlobalSetup_Normalized()
        {
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "DELETE FROM Admissions; DELETE FROM Patients; DELETE FROM Rooms;";
                    command.ExecuteNonQuery();
                }

                // Create a DataTable to hold the data for the Patients table
                DataTable patientsDataTable = new DataTable();
                patientsDataTable.Columns.Add("Id", typeof(int));
                patientsDataTable.Columns.Add("Name", typeof(string));

                // Create a DataTable to hold the data for the Rooms table
                DataTable roomsDataTable = new DataTable();
                roomsDataTable.Columns.Add("Id", typeof(int));
                roomsDataTable.Columns.Add("Name", typeof(string));

                // Create a DataTable to hold the data for the Admissions table
                DataTable admissionsDataTable = new DataTable();
                admissionsDataTable.Columns.Add("Id", typeof(int));
                admissionsDataTable.Columns.Add("PatientId", typeof(int));
                admissionsDataTable.Columns.Add("RoomId", typeof(int));
                admissionsDataTable.Columns.Add("DateAdmitted", typeof(DateTime));

                // Generate 10,000 records of sample data
                Random random = new Random();
                for (int i = 1; i <= 10000; i++)
                {
                    // Add a record to the Patients table
                    patientsDataTable.Rows.Add(i, $"Patient {i}");

                    // Add a record to the Rooms table
                    roomsDataTable.Rows.Add(i, $"Room {i}");

                    // Add a record to the Admissions table
                    admissionsDataTable.Rows.Add(i, i, i, DateTime.Now.AddDays(-random.Next(365)));
                }
                // Create a new SqlBulkCopy object
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    // Set the destination table name
                    bulkCopy.DestinationTableName = "Patients";
                    bulkCopy.WriteToServer(patientsDataTable);

                    bulkCopy.DestinationTableName = "Rooms";
                    bulkCopy.WriteToServer(roomsDataTable);

                    bulkCopy.DestinationTableName = "Admissions";
                    bulkCopy.WriteToServer(admissionsDataTable);
                }
            }
        }

        [GlobalSetup(Target = nameof(Read_Denormalized))]
        public void GlobalSetup_Denormalized()
        {
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "DELETE FROM DenormalizedAdmissions;";
                    command.ExecuteNonQuery();
                }
                // Create a DataTable to hold the data for the DenormalizedAdmissions table
                DataTable denormalizedAdmissionsDataTable = new DataTable();
                denormalizedAdmissionsDataTable.Columns.Add("Id", typeof(int));
                denormalizedAdmissionsDataTable.Columns.Add("PatientName", typeof(string));
                denormalizedAdmissionsDataTable.Columns.Add("RoomName", typeof(string));
                denormalizedAdmissionsDataTable.Columns.Add("DateAdmitted", typeof(DateTime));


                // Generate 10,000 records of sample data
                Random random = new Random();
                for (int i = 1; i <= 10000; i++)
                {
                    // Add a record to the DenormalizedAdmissions table
                    denormalizedAdmissionsDataTable.Rows.Add(i, $"Patient {i}", $"Room {i}", DateTime.Now.AddDays(-random.Next(365)));
                }
                // Create a new SqlBulkCopy object
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    // Set the destination table name
                    bulkCopy.DestinationTableName = "DenormalizedAdmissions";
                    bulkCopy.WriteToServer(denormalizedAdmissionsDataTable);
                }
            }
        }

        private class Config : ManualConfig
        {
            public Config()
            {
                SummaryStyle =
                    SummaryStyle.Default.WithRatioStyle(RatioStyle.Percentage);
            }
        }
    }
}

Depending on the query patterns you will have the most, I would definitely move the clustered index from Id, on the Admission table, to PatientId, RoomId or (most probably in the real world) on DateAdmitted

Thanks Davide, I should clarify it's not for a real app, I'm just playing around for a blog post maybe so no defined query patterns as such.