1

I am currently working on below table structure to pull the data/ generate any one time report.

Table ABC:

ID  Field       Value
1   Name        Nagarjuna
1   Gender      Male
1   Industry    Actor
2   Name        Sachin RT
2   Gender      Male
2   Industry    Sports
3   Name        Theresa
3   Gender      Female
3   Industry    Social Worker
4   Name        Saha
4   Gender      Female
4   Industry    Software Engineer
5   Name        Swe
5   Gender      Female
5   Industry    Doctor

In order to get the report in the format Name,gender,industry values, I am writing the below query

Select  
  vname.Value, vgender.value, vindustry.value 
from ABC [vname] with (nolock)   
join ABC [vgender] with (nolock)
  on vname.id = vgender.id  
join ABC [vindustry] with (nolock)
  on vgender.id = vindustry.id

where vname.field = 'Name' 
  and vgender.field = 'Gender'
  and vindustry.field = 'Industry'

I am currently working on the table which has around 500k of records and for each id type there would be around 10 - 30 entries, so if more records are there for one id type then more joins would be added up.

Because of this executing it is taking a lot of time.

Is there any way that we can reduce this time taken for execution and also this table is used by an website where there would be around 1000 to 50000 users.

  • What indexes do you have on the table? I would put clustered index on (id,field). – Tab Alleman Dec 26 '14 at 13:59
  • There is a pivot function in sql server for this. Not sure if it will speed up the query but it is more readable. Plus adding another field is easier. – Tien Dinh Dec 27 '14 at 20:37
2

This is your query:

Select vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock) join
     ABC [vgender] with (nolock)
     on vname.id = vgender.id join
     ABC [vindustry] with (nolock)
     on vgender.id = vindustry.id
where vname.field = 'Name' and
      vgender.field = 'Gender' and
      vindustry.field = 'Industry';

You can try indexes on this query. I would try ABC(id, field, value) and ABC(field, id, value).

However, you might also try the following:

select max(case when field = 'Name' then value end) as Name,
       max(case when field = 'Gender' then value end) as Gender,
       max(case when field = 'Industry' then value end) as Industry      
where field in ('Name', 'Gender', 'Industry')  
from ABC
group by id;
2

First: please do not create any new indexes to support this. That is crazy. Why? Because you have a 3 column table and the Clustered PK should already be on 2 of them: ([ID], [Field]). Hence, creating another index would amount to nothing more than duplicating the table on disk (not to mention also requiring all DML operations to write to 2 indexes instead of 1) with no possible benefit.

Second: please take a look at the PIVOT clause. For details, please see the MSDN pages for:

The example below shows that it does what you are looking for.

SET NOCOUNT ON;

DECLARE @Data TABLE
(
  ID INT NOT NULL,
  Field NVARCHAR(50) NOT NULL,
  Value NVARCHAR(4000) NOT NULL,
  PRIMARY KEY (ID, Field)
);

INSERT INTO @Data VALUES (1, N'Name', N'Nagarjuna');
INSERT INTO @Data VALUES (1, N'Gender', N'Male');
INSERT INTO @Data VALUES (1, N'Industry', N'Actor');
INSERT INTO @Data VALUES (2, N'Name', N'Sachin RT');
INSERT INTO @Data VALUES (2, N'Gender', N'Male');
INSERT INTO @Data VALUES (2, N'Industry', N'Sports');
INSERT INTO @Data VALUES (3, N'Name', N'Theresa');
INSERT INTO @Data VALUES (3, N'Gender', N'Female');
INSERT INTO @Data VALUES (3, N'Industry', N'Social Worker');
INSERT INTO @Data VALUES (4, N'Name', N'Saha');
INSERT INTO @Data VALUES (4, N'Gender', N'Female');
INSERT INTO @Data VALUES (4, N'Industry', N'Software Engineer');
INSERT INTO @Data VALUES (5, N'Name', N'Swe');
INSERT INTO @Data VALUES (5, N'Gender', N'Female');
INSERT INTO @Data VALUES (5, N'Industry', N'Doctor');


SELECT [ID], [Name], [Gender], [Industry]
FROM (
        SELECT tmp.ID, tmp.Field, tmp.Value
        FROM   @Data tmp
     ) base
PIVOT (
         MAX([Value])
         FOR [Field]
         IN ([Name], [Gender], [Industry])
      ) pvt
ORDER BY [ID];

Returns:

ID  Name        Gender   Industry
--  ----        ------   --------
1   Nagarjuna   Male     Actor
2   Sachin RT   Male     Sports
3   Theresa     Female   Social Worker
4   Saha        Female   Software Engineer
5   Swe         Female   Doctor

Third: the biggest hindrance to performance is the denormalized [Field] column that has repeating string values. This really should be normalized to be a FieldTypeID TINYINT column that Foreign Keys to a new lookup table FieldType. For this particular query you don't need to JOIN back to FieldType, but it is always best to have the lookup values defined in the database.

The benefits of switching the [Field] column from (N)VARCHAR to TINYINT (or SMALLINT if it is conceivable to go over 255 possible values in the future) are:

  • Rows are smaller so more rows fit onto a single datapage. This reduces both Physical and Logical Reads.

  • Filtering, Sorting, Grouping is much faster on integer types than on strings, especially if the field is using a case-insensitive collation. Hence, ALL queries, not just this one, that use this field for these types of operations will perform better.

  • If you ever need to change the value of a particular Field value (e.g. "Industry" to "Profession"), then it is a very simple 1 row change in the lookup table as opposed to needing to update that data across the entire ABC table. Which also means that you don't need to worry about the situation where the app code changes to insert the updated value of "Profession" but someone forgot to update the existing "Industry" values so now you have both.

DECLARE @FieldType TABLE
(
  FieldTypeID TINYINT NOT NULL PRIMARY KEY, -- no IDENTITY!
  FieldTypeName NVARCHAR(50) NOT NULL
);

INSERT INTO @FieldType VALUES (1, N'Name');
INSERT INTO @FieldType VALUES (2, N'Gender');
INSERT INTO @FieldType VALUES (3, N'Industry');

DECLARE @DataImproved TABLE
(
  ID INT NOT NULL,
  FieldTypeID TINYINT NOT NULL, -- FK to FieldType.FieldTypeID
  Value NVARCHAR(4000) NOT NULL,
  PRIMARY KEY (ID, FieldTypeID)
);

INSERT INTO @DataImproved VALUES (1, 1, N'Nagarjuna');
INSERT INTO @DataImproved VALUES (1, 2, N'Male');
INSERT INTO @DataImproved VALUES (1, 3, N'Actor');
INSERT INTO @DataImproved VALUES (2, 1, N'Sachin RT');
INSERT INTO @DataImproved VALUES (2, 2, N'Male');
INSERT INTO @DataImproved VALUES (2, 3, N'Sports');
INSERT INTO @DataImproved VALUES (3, 1, N'Theresa');
INSERT INTO @DataImproved VALUES (3, 2, N'Female');
INSERT INTO @DataImproved VALUES (3, 3, N'Social Worker');
INSERT INTO @DataImproved VALUES (4, 1, N'Saha');
INSERT INTO @DataImproved VALUES (4, 2, N'Female');
INSERT INTO @DataImproved VALUES (4, 3, N'Software Engineer');
INSERT INTO @DataImproved VALUES (5, 1, N'Swe');
INSERT INTO @DataImproved VALUES (5, 2, N'Female');
INSERT INTO @DataImproved VALUES (5, 3, N'Doctor');

SELECT [ID], [1] AS [Name], [2] AS [Gender], [3] AS [Industry]
FROM (
        SELECT tmp.ID, tmp.FieldTypeID, tmp.Value
        FROM   @DataImproved tmp
     ) base
PIVOT (
         MAX([Value])
         FOR [FieldTypeID]
         IN ([1], [2], [3])
      ) pvt
ORDER BY [ID];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy