HI,

I am struck in the following query and want to know how to improve its performance for 2 mn records.

SELECT [1].areabuilding,

[10].yearvalue,

[2].bldgscheme as bldgscheme_dvt,[2].bldgclass as bldgclass_dvt,[2].bldgclasstier1 as bldgclass_dvttier1,[2].bldgdesc as bldgdescdvt,

[3].bldgscheme,[3].bldgclass,[3].bldgclasstier1,[3].bldgdesc,

[4].cflag,[4].cflagcode,[4].bldgclassflag,[4].numstoriesflag,[4].yearvalueflag,[4].occtypeflag

,[4].areabuildingflag,[4].bldgclasstier1flag,[4].occgrpflag,[4].isEligibleAcrossSource,[4].isEligibleInSource,

[5].flagname as flagFilter,

[6].flagname as insflag,

[7].flagname as Singleoccupancyflag,

[8].numstories,

[9].occscheme2,[9].occtype2,[9].occdesc,[9].occintmd,[9].occgrp,[9].occscheme1,[9].occtype1,[9].occgroup,

A.factid,A.basefactid,A.linkid,A.grpaddressid,A.inputid

FROM migration_db.DBO.fact_process7_10 a

INNER JOIN migration_db.dbo.dimareabuilding [1]

ON isnull(a.areabuildingid,1)=[1].areabuildingID

INNER JOIN

migration_db.dbo.dimbldgclass [2]

ON isnull(a.bldgclassdvtid,1)=[2].bldgclassID

INNER JOIN migration_db.dbo.dimbldgclass [3]

ON isnull(a.bldgclassid,1)=[3].bldgclassID

INNER JOIN migration_db.dbo.dimcflag [4]

ON isnull(a.cflagid,1)=[4].cflagID

INNER JOIN migration_db.dbo.dimflag [5]

ON isnull(a.flagid,1)=[5].flagID

INNER JOIN migration_db.dbo.dimflag [6]

ON isnull(a.Insflagid,1)=[6].flagID

INNER JOIN migration_db.dbo.dimflag [7]

ON isnull(a.Singleoccupancyid,1)=[7].flagID

INNER JOIN migration_db.dbo.dimnumstories [8]

ON isnull(a.numstoriesid,1)=[8].numstoriesID

INNER JOIN migration_db.dbo.dimoccupancy [9]

ON isnull(a.occupancyid,1)=[9].occupancyID

INNER JOIN migration_db.dbo.dimyear [10]

ON isnull(a.yearid,1)=[10].yearID

The following are null value counts :

Insflagid --- 1532677

Singleoccupancyid --732556

cflagid --1223643

flagid --1933134

Please suggest.

Regards,

Ankit