Question on improving a query
-
Hi Guyz,
This is going to be a long question so please be cool
Actually the question is short but to explain what I want to do is hard. Try this in your SQL Query Analyzeruse Master
go
--
if exists (select name from master.dbo.sysdatabases where name = 'wrack_test')
drop database [wrack_test]
go
--
create database wrack_test
go
--
use wrack_test
go
--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FieldType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FieldType]
GO
--
CREATE TABLE [dbo].[FieldType]
(
FieldTypeId [int] IDENTITY (1, 1) NOT NULL ,
FieldName [varchar] (100) NOT NULL
)
GO
--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProfileField]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProfileField]
GO
--
CREATE TABLE [dbo].[ProfileField]
(
[ProfileFieldId] [int] IDENTITY (1, 1) NOT NULL ,
[FieldName] [varchar] (50) NOT NULL ,
[FieldTypeId] [int] NOT NULL
)
GO
--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProfileData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProfileData]
GO
--
CREATE TABLE [dbo].[ProfileData]
(
[ProfileDataId] [int] IDENTITY (1, 1) NOT NULL,
[ProfileDataKeyId] [int] NOT NULL,
[ProfileFieldId] [int] NOT NULL,
[DateData] [datetime] NULL,
[StringData] [varchar] (8000) NULL,
[IntData] [int] NULL,
[DecimalData] [decimal](18, 0) NULL
)
GO
--------------------------------------------------------------------------------
INSERT INTO FieldType (FieldName) VALUES ('Date');
INSERT INTO FieldType (FieldName) VALUES ('String');
INSERT INTO FieldType (FieldName) VALUES ('Integer');
INSERT INTO FieldType (FieldName) VALUES ('Decimal');
--------------------------------------------------------------------------------
insert into ProfileField (FieldName, FieldTypeId) VALUES ('FirstName', 2);
insert into ProfileField (FieldName, FieldTypeId) VALUES ('LastName', 2);
insert into ProfileField (FieldName, FieldTypeId) VALUES ('DateOfBirth', 1);
insert into ProfileField (FieldName, FieldTypeId) VALUES ('Age', 3);
insert into ProfileField (FieldName, FieldTypeId) VALUES ('Weight', 4);
--------------------------------------------------------------------------------
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (1, 1, 'A')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (1, 2, 'B')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DateData) VALUES (1, 3, current_timestamp)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, IntData) VALUES (1, 4, 20)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DecimalData) VALUES (1, 5, 65.50)
--
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (2, 1, 'C')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (2, 2, 'D')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DateData) VALUES (2, 3, current_timestamp)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, IntData) VALUES (2, 4, 21)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DecimalData) VALUES (2, 5, 70.25)
--
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (3, 1, 'E')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (3, 2, 'F')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DateData) VALUES (3, 3, current_timestamp)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, IntData) VALUES (3, 4, 22)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DecimalData) VALUES (3, 5, 74.55)
--
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (4, 1, 'G')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (4, 2, 'H')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DateData) VALUES (4, 3, current_timestamp)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, IntData) VALUES (4, 4, 26)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DecimalData) VALUES (4, 5, 79.25)
--
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (5, 1, 'I')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (5, 2, 'J')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DateData) VALUES (5, 3, current_timestamp)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, IntData) VALUES (5, 4, 31)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DecimalData) VALUES (5, 5, 95)
--
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (6, 1, 'WRACK')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, StringData) VALUES (6, 2, 'TEST')
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, IntData) VALUES (6, 4, 26)
insert into ProfileData (ProfileDataKeyId, ProfileFieldId, DecimalData) VALUES (6, 5, 70)
--
go
--
CREATE VIEW dbo.vw_FlatProfile
AS
SELECT T1.ProfileDataKeyId AS PlayerProfileId,
T1.Field1 AS FirstName,
T2.Field2 AS LastName,
T3.Field3 AS DateOfBirth,
T4.Field4 AS Age,
T5.Field5 AS Weight
FROM (SELECT ProfileDataKeyId, StringData AS Field1 FROM ProfileData WHERE ProfileFieldId = 1) T1
INNER JOIN (SELECT ProfileDataKeyId, StringData AS Field2 FROM ProfileData WHERE ProfileFieldId = 2) T2 ON T1.ProfileDataKeyId = T2.ProfileDataKeyId
INNER JOIN (SELECT ProfileDataKeyId, DateData AS Field3 FROM ProfileData WHERE ProfileFieldId = 3) T3 ON T1.ProfileDataKeyId = T3.ProfileDataKeyId
INNER JOIN (SELECT ProfileDataKeyId, IntData AS Field4 FROM ProfileData WHERE ProfileFieldId = 4) T4 ON T1.ProfileDataKeyId = T4.ProfileDataKeyId
INNER JOIN (SELECT ProfileDataKeyId, DecimalData AS Field5 FROM ProfileData WHERE ProfileFieldId = 5) T5 ON T1.ProfileDataKeyId = T5.ProfileDataKeyId
go
--
select * from vw_FlatProfile
Now you will see 5 rows as a result of the last statement which is a select statement from a view. The trouble I have is:
There are actually 6 records in the ProfileData table but the sixth record I inserted is missing the "DateOfBirth" field and thats why the inner join query is not returninig the whole record.
Is there a way to eliminate this INNER JOINs and make the view more flexible so it can cater for missing data?
The reason behind this is, say I need to insert a new ProfileField called "Nationality" of StringData and I change the system to let the user to enter the data so newly added records will have it but the old records wont have that field and if I change the view to cater for the new field then it wont return the old records..!
So any help is appreciated, I don't mind few changes here and there in table structure but the way the ProfileData is stored (vertically) can't be changed (working on an existing system). Also as the number of records grow the INNER JOINs really kills the performance.
Kindest Regards,WRACK
CodeLake -
Using LEFT JOINs instead of INNER JOINs should solve your immediate problem. Age should always be calculated from DateOfBirth.
-
Thanks for the quick reply. I know Age should be calculated but it was just used as an example of INTEGER data.
I tried LEFT JOINS but didn't work..!
Kindest Regards,WRACK
CodeLake -
Sorry about the previous one where I said it wasn't working, I just woke up so I forgot one of the join
It's working with LEFT JOINs and the best thing is missing fields are shown as NULL which is exactly I want
Thank you again.
Kindest Regards,WRACK
CodeLake
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply