Question on improving a query

Question

Post reply

Question on improving a query

CodeLake

SSCrazy

Points: 2082
More actions
November 9, 2006 at 9:30 pm

#117606

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 Analyzer

use 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