SQL Server Performance Forum – Threads Archive

best practice

Hi all which is the best practice? look at these queries SELECT w.ward_id, r.room_id as room, b.bed_id AS bed, p.patient_id, w.start_dt AS ward_start_dt, p.full_name, p.birth_dt, p.gender, DATEDIFF(day, w.start_dt, GETDATE()) AS LOS,
DATEDIFF(day, d.start_dt, GETDATE()) AS LOS_WARD, d.depstay_id, p.huisarts_code
FROMdbo.bedstay b
JOINdbo.roomstay r ON (b.end_dt IS NULL)
AND
r.roomstay_id = b.roomstay_id
AND b.bedstay_id =(SELECT MAX(b2.bedstay_id)
FROM bedstay b2
WHERE b2.roomstay_id = r.roomstay_id
) JOINdbo.wardstay w ON w.wardstay_id = r.wardstay_id
JOINdbo.depstay dON d.depstay_id = w.depstay_id
JOIN dbo.admission aON a.admission_id = d.admission_id
JOINdbo.patient pON p.patient_id = a.patient_id SELECT w.ward_id, r.room_id as room, b.bed_id AS bed, p.patient_id, w.start_dt AS ward_start_dt, p.full_name, p.birth_dt, p.gender, DATEDIFF(day, w.start_dt, GETDATE()) AS LOS,
DATEDIFF(day, d.start_dt, GETDATE()) AS LOS_WARD, d.depstay_id, p.huisarts_code
FROM dbo.admission a INNER JOIN
dbo.depstay d ON a.admission_id = d.admission_id INNER JOIN
dbo.wardstay w ON d.depstay_id = w.depstay_id INNER JOIN
dbo.roomstay r ON w.wardstay_id = r.wardstay_id INNER JOIN
dbo.bedstay b ON r.roomstay_id = b.roomstay_id inner join
dbo.patient p ON p.patient_id = a.patient_id
WHERE (b.end_dt IS NULL)
AND
(b.bedstay_id =
(SELECT MAX(b2.bedstay_id)
FROM bedstay b2
WHERE b2.roomstay_id = r.roomstay_id)) I tend to narrow my result set as quickly al possible so I opt for the first one admission 210000 rows
bedstay 310000
depstay 250000
patient 425000
roomstay 280000
wardstay 255000 /****** Object: Table [dbo].[admission] Script Date: 07-09-2005 12:52:30 ******/
CREATE TABLE [admission] (
[admission_id] [int] NOT NULL ,
[patient_id] [varchar] (7) NOT NULL ,
[admission_dt] [datetime] NULL ,
[discharge_dt] [datetime] NULL ,
[specialist] [varchar] (5) NULL ,
[co_specialist] [varchar] (5) NULL ,
[source] [varchar] (3) NULL ,
[destination] [varchar] (3) NULL ,
[deceased_dt] [datetime] NULL ,
[deceased] [varchar] (1) NULL ,
[episode_id] [varchar] (3) NULL ,
[specialism] [varchar] (5) NULL ,
[adt_id] [int] NULL ,
[event_type] [varchar] (3) NULL ,
CONSTRAINT [PK_admission] PRIMARY KEY CLUSTERED
(
[admission_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_admission_patient] FOREIGN KEY
(
[patient_id]
) REFERENCES [patient] (
[patient_id]
) ON DELETE CASCADE
) ON [PRIMARY]
GO /****** Object: Index IX_admission on Table [dbo].[admission] Script Date: 07-09-2005 12:57:47 ******/
CREATE INDEX [IX_admission] ON [dbo].[admission]([patient_id]) ON [PRIMARY]
GO /****** Object: Table [dbo].[bedstay] Script Date: 07-09-2005 12:53:05 ******/
CREATE TABLE [bedstay] (
[bedstay_id] [int] NOT NULL ,
[roomstay_id] [int] NULL ,
[bed_id] [int] NULL ,
[start_dt] [datetime] NULL ,
[end_dt] [datetime] NULL ,
[adt_id] [int] NULL ,
[event_type] [varchar] (3) NULL ,
CONSTRAINT [PK_bedstay] PRIMARY KEY CLUSTERED
(
[bedstay_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_bedstay_roomstay] FOREIGN KEY
(
[roomstay_id]
) REFERENCES [roomstay] (
[roomstay_id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
/****** Object: Index IX_bedstay on Table [dbo].[bedstay] Script Date: 07-09-2005 12:56:50 ******/
CREATE INDEX [IX_bedstay] ON [dbo].[bedstay]([roomstay_id]) ON [PRIMARY]
GO /****** Object: Index bedstay77 on Table [dbo].[bedstay] Script Date: 07-09-2005 12:57:03 ******/
CREATE INDEX [bedstay77] ON [dbo].[bedstay]([roomstay_id], [end_dt], [bed_id]) ON [PRIMARY]
GO /****** Object: Index bedstay7 on Table [dbo].[bedstay] Script Date: 07-09-2005 12:57:17 ******/
CREATE INDEX [bedstay7] ON [dbo].[bedstay]([roomstay_id], [bedstay_id], [bed_id], [start_dt], [end_dt]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO /****** Object: Table [dbo].[depstay] Script Date: 07-09-2005 12:53:33 ******/
CREATE TABLE [depstay] (
[depstay_id] [int] NOT NULL ,
[admission_id] [int] NULL ,
[department_id] [varchar] (4) NULL ,
[source] [varchar] (4) NULL ,
[destination] [varchar] (4) NULL ,
[start_dt] [smalldatetime] NULL ,
[end_dt] [smalldatetime] NULL ,
[adt_id] [int] NULL ,
[event_type] [varchar] (3) NULL ,
CONSTRAINT [PK_depstay] PRIMARY KEY CLUSTERED
(
[depstay_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_depstay_admission] FOREIGN KEY
(
[admission_id]
) REFERENCES [admission] (
[admission_id]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
/****** Object: Index IX_depstay_1 on Table [dbo].[depstay] Script Date: 07-09-2005 12:56:03 ******/
CREATE INDEX [IX_depstay_1] ON [dbo].[depstay]([admission_id]) ON [PRIMARY]
GO /****** Object: Index IX_depstay on Table [dbo].[depstay] Script Date: 07-09-2005 12:56:20 ******/
CREATE INDEX [IX_depstay] ON [dbo].[depstay]([admission_id], [depstay_id], [department_id]) ON [PRIMARY]
GO /****** Object: Table [dbo].[patient] Script Date: 07-09-2005 12:53:54 ******/
CREATE TABLE [patient] (
[patient_id] [varchar] (7) NOT NULL ,
[surname] [varchar] (30) NULL ,
[first_name] [varchar] (20) NULL ,
[initials] [varchar] (12) NULL ,
[prefix] [varchar] (10) NULL ,
[name_type] [varchar] (1) NULL ,
[partner_surname] [varchar] (30) NULL ,
[partner_prefix] [varchar] (10) NULL ,
[partner_name_type] [varchar] (1) NULL ,
[addr_surname] [varchar] (20) NULL ,
[addr_prefix] [varchar] (12) NULL ,
[addr_name_type] [varchar] (1) NULL ,
[birth_dt] [datetime] NULL ,
[gender] [varchar] (1) NULL ,
[street] [varchar] (32) NULL ,
[house_number] [varchar] (10) NULL ,
[zip_code] [varchar] (6) NULL ,
[city] [varchar] (32) NULL ,
[country] [varchar] (25) NULL ,
[phone] [varchar] (15) NULL ,
[deceased_dt] [datetime] NULL ,
[deceased] [varchar] (1) NULL ,
[huisarts_code] [varchar] (6) NULL ,
[type] [varchar] (1) NULL ,
[add_dt] [datetime] NULL ,
[add_uid] [varchar] (10) NULL ,
[edit_dt] [datetime] NULL ,
[edit_uid] [varchar] (10) NULL ,
[event_type] [varchar] (3) NULL ,
[present_location] [varchar] (12) NULL ,
[adt_id] [int] NULL ,
CONSTRAINT [PK_patient] PRIMARY KEY CLUSTERED
(
[patient_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[roomstay] Script Date: 07-09-2005 12:54:31 ******/
CREATE TABLE [roomstay] (
[roomstay_id] [int] NOT NULL ,
[wardstay_id] [int] NULL ,
[room_id] [varchar] (5) NULL ,
[start_dt] [smalldatetime] NULL ,
[end_dt] [smalldatetime] NULL ,
[adt_id] [int] NULL ,
[event_type] [varchar] (3) NULL ,
CONSTRAINT [PK_roomstay] PRIMARY KEY CLUSTERED
(
[roomstay_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_roomstay_wardstay] FOREIGN KEY
(
[wardstay_id]
) REFERENCES [wardstay] (
[wardstay_id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO /****** Object: Index IX_roomstay on Table [dbo].[roomstay] Script Date: 07-09-2005 12:55:32 ******/
CREATE INDEX [IX_roomstay] ON [dbo].[roomstay]([wardstay_id]) ON [PRIMARY]
GO /****** Object: Table [dbo].[wardstay] Script Date: 07-09-2005 12:54:49 ******/
CREATE TABLE [wardstay] (
[wardstay_id] [int] NOT NULL ,
[depstay_id] [int] NULL ,
[ward_id] [varchar] (3) NULL ,
[source] [varchar] (3) NULL ,
[destination] [varchar] (3) NULL ,
[start_dt] [datetime] NULL ,
[end_dt] [datetime] NULL ,
[adt_id] [int] NULL ,
[event_type] [varchar] (3) NULL ,
CONSTRAINT [PK_wardstay] PRIMARY KEY CLUSTERED
(
[wardstay_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_wardstay_depstay] FOREIGN KEY
(
[depstay_id]
) REFERENCES [depstay] (
[depstay_id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
/****** Object: Index IX_wardstay on Table [dbo].[wardstay] Script Date: 07-09-2005 12:55:02 ******/
CREATE INDEX [IX_wardstay] ON [dbo].[wardstay]([depstay_id]) ON [PRIMARY]
GO

Without trying this myself, I would guess both are likely to produce almost identical execution plans. Have you examined this already? Are you experiencing any issues? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>