How should I optimize this SQL query?

Question

I have five tables called Asset Master, Category Master, Asset Category, Asset Parameter, and Parameter Master.

I have more than 50 parameters in the Parameter Master table, every parameter value stored in Asset Parameter table as per asset.

Every asset assign to more than one category so, there is one table called Asset Category which is middle table for Category Master and Asset Master.

Now, I want that 50 parameters value as well as category and asset information in single query, which is written below.

This query give result perfectly but need to optimize it. Anyone suggest me proper way to optimize it.

I am using Left join cause Parameter value's type is text in db and need to handle null value.

SELECT [ac].[CategoryID]
        ,[am].[AssetID] 
        ,[am].[FullAssetID] 
        ,[am].[AssetTitle]
        ,[am].[SmallDescription]
        ,[am].[LongDescription]
        ,[am].[Paneltype]
        ,[am].[Image]
        ,[am].[ThumbImage]
        ,[am].[CreatedDate]
        ,[am].[ModifiedDate]
        ,[am].[Status]
        ,[cm].[CategoryName]
        ,[cm].[Description]
        ,[cm].[ParentCategoryID]
        ,[ap].[ParameterValue] AS 'Longitude'
        ,[ap1].[ParameterValue] AS 'Latitude'
        ,[ap2].[ParameterValue] AS 'Address'
        ,[ap3].[ParameterValue] AS 'Address1'
        ,[ap4].[ParameterValue] AS 'Address2'
        ,[ap5].[ParameterValue] AS 'Address3'
        ,[ap6].[ParameterValue] AS 'Address4'
        ,[ap7].[ParameterValue] AS 'Telephone'
        ,[ap8].[ParameterValue] AS 'Email'
        ,[ap9].[ParameterValue] AS 'Website'
        ,[ap10].[ParameterValue] AS 'Postcode'
        ,[ap11].[ParameterValue] AS 'Url'
        ,[ap12].[ParameterValue] AS 'OpeningTimes'
        ,[ap13].[ParameterValue] AS 'OpeningDates'
        ,[ap14].[ParameterValue] AS 'PriceMin'
        ,[ap15].[ParameterValue] AS 'PriceMax'
        ,[ap16].[ParameterValue] AS 'BookOnline'
        ,[ap17].[ParameterValue] AS 'BookOnlineUrl'
        ,[ap18].[ParameterValue] AS 'CheckIn'
        ,[ap19].[ParameterValue] AS 'CheckOut'
        ,[ap20].[ParameterValue] AS 'StartDate'
        ,[ap21].[ParameterValue] AS 'EndDate'
        ,[ap22].[ParameterValue] AS 'Seasons'
        ,[ap23].[ParameterValue] AS 'Months'
        ,[ap24].[ParameterValue] AS 'Sleeps'
        ,[ap25].[ParameterValue] AS 'WiFi'
        ,[ap26].[ParameterValue] AS 'Creditcards'
        ,[ap27].[ParameterValue] AS 'PetsWelcome'
        ,[ap28].[ParameterValue] AS 'DisabledAccess'
        ,[ap29].[ParameterValue] AS 'DisabledDescription'
        ,[ap30].[ParameterValue] AS 'LicensedRestaurant'
        ,[ap31].[ParameterValue] AS 'NoRestaurantEveningMealsAvailable'
        ,[ap32].[ParameterValue] AS 'LaundryFacilities'
        ,[ap33].[ParameterValue] AS 'DryingFacilities'
        ,[ap34].[ParameterValue] AS 'Television'
        ,[ap35].[ParameterValue] AS 'Lounge'
        ,[ap36].[ParameterValue] AS 'PayParking'
        ,[ap37].[ParameterValue] AS 'FreeParking'
        ,[ap38].[ParameterValue] AS 'WashingMachine'
        ,[ap39].[ParameterValue] AS 'Dishwasher'
        ,[ap40].[ParameterValue] AS 'Microwave'
        ,[ap41].[ParameterValue] AS 'CDPlayerss'
        ,[ap42].[ParameterValue] AS 'Video'
        ,[ap43].[ParameterValue] AS 'Shower'
        ,[ap44].[ParameterValue] AS 'Bath'
        ,[ap45].[ParameterValue] AS 'BathWithShower'
        ,[ap46].[ParameterValue] AS 'DVDPlayer'
        ,[ap47].[ParameterValue] AS 'CotAvailable'
        ,[ap48].[ParameterValue] AS 'ElectricHookup'
        ,[ap49].[ParameterValue] AS 'Shop'
        ,[ap50].[ParameterValue] AS 'ChemicalDisposal'
        ,[ap51].[ParameterValue] AS 'StarRating'
        ,[ap52].[ParameterValue] AS 'FoodCategory'

    FROM [AssetMaster] AS am
    INNER JOIN [Asset_Category] AS ac ON [am].AssetID = [ac].AssetID
    INNER JOIN [CategoryMaster] AS cm ON [cm].[CategoryID]=[am].[CategoryID]
    LEFT JOIN 
            (   [AssetParameter] AS ap 
                INNER JOIN [ParameterMaster] AS pm ON [pm].[ParameterID] = [ap].[ParameterID] AND [pm].[ParameterName]='Longitude'
            ) ON [am].[AssetID] = [ap].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap1 
                INNER JOIN [ParameterMaster] AS pm1 ON [pm1].[ParameterID] = [ap1].[ParameterID] AND [pm1].[ParameterName]='Latitude'
            ) ON [am].[AssetID] = [ap1].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap2 
                INNER JOIN [ParameterMaster] AS pm2 ON [pm2].[ParameterID] = [ap2].[ParameterID] AND [pm2].[ParameterName]='Address'
            ) ON [am].[AssetID] = [ap2].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap3 
                INNER JOIN [ParameterMaster] AS pm3 ON [pm3].[ParameterID] = [ap3].[ParameterID] AND [pm3].[ParameterName]='Address1'
            ) ON [am].[AssetID] = [ap3].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap4 
                INNER JOIN [ParameterMaster] AS pm4 ON [pm4].[ParameterID] = [ap4].[ParameterID] AND [pm4].[ParameterName]='Address2'
            ) ON [am].[AssetID] = [ap4].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap5 
                INNER JOIN [ParameterMaster] AS pm5 ON [pm5].[ParameterID] = [ap5].[ParameterID] AND [pm5].[ParameterName]='Address3'
            ) ON [am].[AssetID] = [ap5].[AssetID]           

    LEFT JOIN 
            (   [AssetParameter] AS ap6
                INNER JOIN [ParameterMaster] AS pm6 ON [pm6].[ParameterID] = [ap6].[ParameterID] AND [pm6].[ParameterName]='Address4'
            ) ON [am].[AssetID] = [ap6].[AssetID]           

    LEFT JOIN 
            (   [AssetParameter] AS ap7
                INNER JOIN [ParameterMaster] AS pm7 ON [pm7].[ParameterID] = [ap7].[ParameterID] AND [pm7].[ParameterName]='Telephone'
            ) ON [am].[AssetID] = [ap7].[AssetID]           

    LEFT JOIN 
            (   [AssetParameter] AS ap8 
                INNER JOIN [ParameterMaster] AS pm8 ON [pm8].[ParameterID] = [ap8].[ParameterID] AND [pm8].[ParameterName]='Email'
            ) ON [am].[AssetID] = [ap8].[AssetID]           

    LEFT JOIN 
            (   [AssetParameter] AS ap9 
                INNER JOIN [ParameterMaster] AS pm9 ON [pm9].[ParameterID] = [ap9].[ParameterID] AND [pm9].[ParameterName]='Website'
            ) ON [am].[AssetID] = [ap9].[AssetID]           

    LEFT JOIN 
            (   [AssetParameter] AS ap10 
                INNER JOIN [ParameterMaster] AS pm10 ON [pm10].[ParameterID] = [ap10].[ParameterID] AND [pm10].[ParameterName]='Postcode'
            ) ON [am].[AssetID] = [ap10].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap11 
                INNER JOIN [ParameterMaster] AS pm11 ON [pm11].[ParameterID] = [ap11].[ParameterID] AND [pm11].[ParameterName]='Url'
            ) ON [am].[AssetID] = [ap11].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap12 
                INNER JOIN [ParameterMaster] AS pm12 ON [pm12].[ParameterID] = [ap12].[ParameterID] AND [pm12].[ParameterName]='Opening Time'
            ) ON [am].[AssetID] = [ap12].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap13 
                INNER JOIN [ParameterMaster] AS pm13 ON [pm13].[ParameterID] = [ap13].[ParameterID] AND [pm13].[ParameterName]='Opening Dates'
            ) ON [am].[AssetID] = [ap13].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap14 
                INNER JOIN [ParameterMaster] AS pm14 ON [pm14].[ParameterID] = [ap14].[ParameterID] AND [pm14].[ParameterName]='Price Min'
            ) ON [am].[AssetID] = [ap14].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap15 
                INNER JOIN [ParameterMaster] AS pm15 ON [pm15].[ParameterID] = [ap15].[ParameterID] AND [pm15].[ParameterName]='Price Max'
            ) ON [am].[AssetID] = [ap15].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap16  
                INNER JOIN [ParameterMaster] AS pm16 ON [pm16].[ParameterID] = [ap16].[ParameterID] AND [pm16].[ParameterName]='Book Online'
            ) ON [am].[AssetID] = [ap16].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap17 
                INNER JOIN [ParameterMaster] AS pm17 ON [pm17].[ParameterID] = [ap17].[ParameterID] AND [pm17].[ParameterName]='Book Online Url'
            ) ON [am].[AssetID] = [ap17].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap18 
                INNER JOIN [ParameterMaster] AS pm18 ON [pm18].[ParameterID] = [ap18].[ParameterID] AND [pm18].[ParameterName]='Check In'
            ) ON [am].[AssetID] = [ap18].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap19 
                INNER JOIN [ParameterMaster] AS pm19 ON [pm19].[ParameterID] = [ap19].[ParameterID] AND [pm19].[ParameterName]='Check Out'
            ) ON [am].[AssetID] = [ap19].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap20 
                INNER JOIN [ParameterMaster] AS pm20 ON [pm20].[ParameterID] = [ap20].[ParameterID] AND [pm20].[ParameterName]='Date Start Date'
            ) ON [am].[AssetID] = [ap20].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap21 
                INNER JOIN [ParameterMaster] AS pm21 ON [pm21].[ParameterID] = [ap21].[ParameterID] AND [pm21].[ParameterName]='Date End Date'
            ) ON [am].[AssetID] = [ap21].[AssetID]


    LEFT JOIN 
            (   [AssetParameter] AS ap22 
                INNER JOIN [ParameterMaster] AS pm22 ON [pm22].[ParameterID] = [ap22].[ParameterID] AND [pm22].[ParameterName]='Season'
            ) ON [am].[AssetID] = [ap22].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap23 
                INNER JOIN [ParameterMaster] AS pm23 ON [pm23].[ParameterID] = [ap23].[ParameterID] AND [pm23].[ParameterName]='Months'
            ) ON [am].[AssetID] = [ap23].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap24 
                INNER JOIN [ParameterMaster] AS pm24 ON [pm24].[ParameterID] = [ap24].[ParameterID] AND [pm24].[ParameterName]='Sleeps'
            ) ON [am].[AssetID] = [ap24].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap25 
                INNER JOIN [ParameterMaster] AS pm25 ON [pm25].[ParameterID] = [ap25].[ParameterID] AND [pm25].[ParameterName]='WiFi'
            ) ON [am].[AssetID] = [ap25].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap26 
                INNER JOIN [ParameterMaster] AS pm26 ON [pm26].[ParameterID] = [ap26].[ParameterID] AND [pm26].[ParameterName]='Creditcards'
            ) ON [am].[AssetID] = [ap26].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap27  
                INNER JOIN [ParameterMaster] AS pm27 ON [pm27].[ParameterID] = [ap27].[ParameterID] AND [pm27].[ParameterName]='Pets Welcome'
            ) ON [am].[AssetID] = [ap27].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap28 
                INNER JOIN [ParameterMaster] AS pm28 ON [pm28].[ParameterID] = [ap28].[ParameterID] AND [pm28].[ParameterName]='Disabled Access'
            ) ON [am].[AssetID] = [ap28].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap29 
                INNER JOIN [ParameterMaster] AS pm29 ON [pm29].[ParameterID] = [ap29].[ParameterID] AND [pm29].[ParameterName]='Disabled Description'
            ) ON [am].[AssetID] = [ap29].[AssetID]

    LEFT JOIN
            (   [AssetParameter] AS ap30 
                INNER JOIN [ParameterMaster] AS pm30 ON [pm30].[ParameterID] = [ap30].[ParameterID] AND [pm30].[ParameterName]='Licensed Restaurant'
            ) ON [am].[AssetID] = [ap30].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap31 
                INNER JOIN [ParameterMaster] AS pm31 ON [pm31].[ParameterID] = [ap31].[ParameterID] AND [pm31].[ParameterName]='No Restaurant Evening Meals Availbale'
            ) ON [am].[AssetID] = [ap31].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap32 
                INNER JOIN [ParameterMaster] AS pm32 ON [pm32].[ParameterID] = [ap32].[ParameterID] AND [pm32].[ParameterName]='Laundry Facilites'
            ) ON [am].[AssetID] = [ap32].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap33 
                INNER JOIN [ParameterMaster] AS pm33 ON [pm33].[ParameterID] = [ap33].[ParameterID] AND [pm33].[ParameterName]='Drying Facilities'
            ) ON [am].[AssetID] = [ap33].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap34 
                INNER JOIN [ParameterMaster] AS pm34 ON [pm34].[ParameterID] = [ap34].[ParameterID] AND [pm34].[ParameterName]='Television'
            ) ON [am].[AssetID] = [ap34].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap35 
                INNER JOIN [ParameterMaster] AS pm35 ON [pm35].[ParameterID] = [ap35].[ParameterID] AND [pm35].[ParameterName]='Lounge'
            ) ON [am].[AssetID] = [ap35].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap36 
                INNER JOIN [ParameterMaster] AS pm36 ON [pm36].[ParameterID] = [ap36].[ParameterID] AND [pm36].[ParameterName]='Pay Parking'
            ) ON [am].[AssetID] = [ap36].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap37 
                INNER JOIN [ParameterMaster] AS pm37 ON [pm37].[ParameterID] = [ap37].[ParameterID] AND [pm37].[ParameterName]='Free Parking'
            ) ON [am].[AssetID] = [ap37].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap38 
                INNER JOIN [ParameterMaster] AS pm38 ON [pm38].[ParameterID] = [ap38].[ParameterID] AND [pm38].[ParameterName]='Washing Machine'
            ) ON [am].[AssetID] = [ap38].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap39 
                INNER JOIN [ParameterMaster] AS pm39 ON [pm39].[ParameterID] = [ap39].[ParameterID] AND [pm39].[ParameterName]='Dishwasher'
            ) ON [am].[AssetID] = [ap39].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap40 
                INNER JOIN [ParameterMaster] AS pm40 ON [pm40].[ParameterID] = [ap40].[ParameterID] AND [pm40].[ParameterName]='Microwave'
            ) ON [am].[AssetID] = [ap40].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap41 
                INNER JOIN [ParameterMaster] AS pm41 ON [pm41].[ParameterID] = [ap41].[ParameterID] AND [pm41].[ParameterName]='CD Player'
            ) ON [am].[AssetID] = [ap41].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap42 
                INNER JOIN [ParameterMaster] AS pm42 ON [pm42].[ParameterID] = [ap42].[ParameterID] AND [pm42].[ParameterName]='Video'
            ) ON [am].[AssetID] = [ap42].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap43 
                INNER JOIN [ParameterMaster] AS pm43 ON [pm43].[ParameterID] = [ap43].[ParameterID] AND [pm43].[ParameterName]='Shower'
            ) ON [am].[AssetID] = [ap43].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap44 
                INNER JOIN [ParameterMaster] AS pm44 ON [pm44].[ParameterID] = [ap44].[ParameterID] AND [pm44].[ParameterName]='Bath'
            ) ON [am].[AssetID] = [ap44].[AssetID]          

    LEFT JOIN 
            (   [AssetParameter] AS ap45 
                INNER JOIN [ParameterMaster] AS pm45 ON [pm45].[ParameterID] = [ap45].[ParameterID] AND [pm45].[ParameterName]='Bath With Shower'
            ) ON [am].[AssetID] = [ap45].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap46 
                INNER JOIN [ParameterMaster] AS pm46 ON [pm46].[ParameterID] = [ap46].[ParameterID] AND [pm46].[ParameterName]='DVD Player'
            ) ON [am].[AssetID] = [ap46].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap47 
                INNER JOIN [ParameterMaster] AS pm47 ON [pm47].[ParameterID] = [ap47].[ParameterID] AND [pm47].[ParameterName]='Cot Available'
            ) ON [am].[AssetID] = [ap47].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap48 
                INNER JOIN [ParameterMaster] AS pm48 ON [pm48].[ParameterID] = [ap48].[ParameterID] AND [pm48].[ParameterName]='Electric Hook Up'
            ) ON [am].[AssetID] = [ap48].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap49 
                INNER JOIN [ParameterMaster] AS pm49 ON [pm49].[ParameterID] = [ap49].[ParameterID] AND [pm49].[ParameterName]='Shop'
            ) ON [am].[AssetID] = [ap49].[AssetID]

    LEFT JOIN 
            (   [AssetParameter] AS ap50 
                INNER JOIN [ParameterMaster] AS pm50 ON [pm50].[ParameterID] = [ap50].[ParameterID] AND [pm50].[ParameterName]='Chemical Disposal'
            ) ON [am].[AssetID] = [ap50].[AssetID]
    LEFT JOIN 
            (   [AssetParameter] AS ap51 
                INNER JOIN [ParameterMaster] AS pm51 ON [pm51].[ParameterID] = [ap51].[ParameterID] AND [pm51].[ParameterName]='Star Rating'
            ) ON [am].[AssetID] = [ap51].[AssetID]
    LEFT JOIN 
            (   [AssetParameter] AS ap52 
                INNER JOIN [ParameterMaster] AS pm52 ON [pm52].[ParameterID] = [ap52].[ParameterID] AND [pm52].[ParameterName]='Food Category'
            ) ON [am].[AssetID] = [ap52].[AssetID]
    Order By [cm].[CategoryID], [am].[AssetTitle], [am].[ModifiedDate] DESC

Answer 1

If the speed of selecting data is more important to you than changing data, you could turn each LEFT JOIN subselect into a CLUSTERED INDEX VIEW and select from those.

Disclaimer

Although not explicitly mentioned, if you have the opportunity to alter your database design, I agree that that would be the best course of action.

Create longitude indexed view

CREATE VIEW dbo.VIEW_Longitude WITH SCHEMABINDING AS 
  SELECT  ap.AssetID
          , mp.ParameterName
  FROM    [AssetParameter] AS ap 
          INNER JOIN [ParameterMaster] AS pm 
             ON [pm].[ParameterID] = [ap].[ParameterID] 
                AND [pm].[ParameterName] = 'Longitude'
GO
CREATE CLUSTERED INDEX UIX_VIEW_LONGITUDE 
   ON dbo.VIEW_Longitude (AssetID) INCLUDE (ParameterName)          

You should end up with a statement equivalent to this

  SELECT [ac].[CategoryID]
          ,[am].[AssetID] 
          ,[am].[FullAssetID] 
          ,[am].[AssetTitle]
          ,[am].[SmallDescription]
          ,[am].[LongDescription]
          ,[am].[Paneltype]
          ,[am].[Image]
          ,[am].[ThumbImage]
          ,[am].[CreatedDate]
          ,[am].[ModifiedDate]
          ,[am].[Status]
          ,[cm].[CategoryName]
          ,[cm].[Description]
          ,[cm].[ParentCategoryID]
          ,[ap].[ParameterValue] AS 'Longitude'
          ,[ap1].[ParameterValue] AS 'Latitude'
          ,[ap2].[ParameterValue] AS 'Address'
          ,[ap3].[ParameterValue] AS 'Address1'
          ,[ap4].[ParameterValue] AS 'Address2'
          ,[ap5].[ParameterValue] AS 'Address3'
          ,[ap6].[ParameterValue] AS 'Address4'
          ,[ap7].[ParameterValue] AS 'Telephone'
          ,[ap8].[ParameterValue] AS 'Email'
          ,[ap9].[ParameterValue] AS 'Website'
          ,[ap10].[ParameterValue] AS 'Postcode'
          ,[ap11].[ParameterValue] AS 'Url'
          ,[ap12].[ParameterValue] AS 'OpeningTimes'
          ,[ap13].[ParameterValue] AS 'OpeningDates'
          ,[ap14].[ParameterValue] AS 'PriceMin'
          ,[ap15].[ParameterValue] AS 'PriceMax'
          ,[ap16].[ParameterValue] AS 'BookOnline'
          ,[ap17].[ParameterValue] AS 'BookOnlineUrl'
          ,[ap18].[ParameterValue] AS 'CheckIn'
          ,[ap19].[ParameterValue] AS 'CheckOut'
          ,[ap20].[ParameterValue] AS 'StartDate'
          ,[ap21].[ParameterValue] AS 'EndDate'
          ,[ap22].[ParameterValue] AS 'Seasons'
          ,[ap23].[ParameterValue] AS 'Months'
          ,[ap24].[ParameterValue] AS 'Sleeps'
          ,[ap25].[ParameterValue] AS 'WiFi'
          ,[ap26].[ParameterValue] AS 'Creditcards'
          ,[ap27].[ParameterValue] AS 'PetsWelcome'
          ,[ap28].[ParameterValue] AS 'DisabledAccess'
          ,[ap29].[ParameterValue] AS 'DisabledDescription'
          ,[ap30].[ParameterValue] AS 'LicensedRestaurant'
          ,[ap31].[ParameterValue] AS 'NoRestaurantEveningMealsAvailable'
          ,[ap32].[ParameterValue] AS 'LaundryFacilities'
          ,[ap33].[ParameterValue] AS 'DryingFacilities'
          ,[ap34].[ParameterValue] AS 'Television'
          ,[ap35].[ParameterValue] AS 'Lounge'
          ,[ap36].[ParameterValue] AS 'PayParking'
          ,[ap37].[ParameterValue] AS 'FreeParking'
          ,[ap38].[ParameterValue] AS 'WashingMachine'
          ,[ap39].[ParameterValue] AS 'Dishwasher'
          ,[ap40].[ParameterValue] AS 'Microwave'
          ,[ap41].[ParameterValue] AS 'CDPlayerss'
          ,[ap42].[ParameterValue] AS 'Video'
          ,[ap43].[ParameterValue] AS 'Shower'
          ,[ap44].[ParameterValue] AS 'Bath'
          ,[ap45].[ParameterValue] AS 'BathWithShower'
          ,[ap46].[ParameterValue] AS 'DVDPlayer'
          ,[ap47].[ParameterValue] AS 'CotAvailable'
          ,[ap48].[ParameterValue] AS 'ElectricHookup'
          ,[ap49].[ParameterValue] AS 'Shop'
          ,[ap50].[ParameterValue] AS 'ChemicalDisposal'
          ,[ap51].[ParameterValue] AS 'StarRating'
          ,[ap52].[ParameterValue] AS 'FoodCategory'
  FROM  [AssetMaster] AS am
        INNER JOIN [Asset_Category] AS ac     ON [am].AssetID      = [ac].AssetID
        INNER JOIN [CategoryMaster] AS cm     ON [cm].[CategoryID] = [am].[CategoryID]
        LEFT JOIN dbo.VIEW_Longitude ap       ON [am].[AssetID]    = [ap].[AssetID]
        LEFT JOIN dbo.VIEW_Latitude ap1       ON [am].[AssetID]    = [ap1].[AssetID]
        LEFT JOIN dbo.VIEW_Address ap2        ON [am].[AssetID]    = [ap2].[AssetID]
        LEFT JOIN dbo.VIEW_Address1 ap3       ON [am].[AssetID]    = [ap3].[AssetID]
        LEFT JOIN dbo.VIEW_Address2 ap4       ON [am].[AssetID]    = [ap4].[AssetID]
        LEFT JOIN dbo.VIEW_Address3 ap5       ON [am].[AssetID]    = [ap5].[AssetID]
        LEFT JOIN dbo.VIEW_Address4 ap6       ON [am].[AssetID]    = [ap6].[AssetID]
        LEFT JOIN dbo.VIEW_Telephone ap7      ON [am].[AssetID]    = [ap7].[AssetID]
        LEFT JOIN dbo.VIEW_Email ap8          ON [am].[AssetID]    = [ap8].[AssetID]
        LEFT JOIN dbo.VIEW_Website ap9        ON [am].[AssetID]    = [ap9].[AssetID]
        LEFT JOIN dbo.VIEW_Postcode ap10      ON [am].[AssetID]    = [ap10].[AssetID]
        LEFT JOIN dbo.VIEW_Url ap11           ON [am].[AssetID]    = [ap11].[AssetID]
        LEFT JOIN dbo.VIEW_Time ap12          ON [am].[AssetID]    = [ap12].[AssetID]
        LEFT JOIN dbo.VIEW_Dates ap13         ON [am].[AssetID]    = [ap13].[AssetID]
        LEFT JOIN dbo.VIEW_Min ap14           ON [am].[AssetID]    = [ap14].[AssetID]
        LEFT JOIN dbo.VIEW_Max ap15           ON [am].[AssetID]    = [ap15].[AssetID]
        LEFT JOIN dbo.VIEW_Online ap16        ON [am].[AssetID]    = [ap16].[AssetID]
        LEFT JOIN dbo.VIEW_Url ap17           ON [am].[AssetID]    = [ap17].[AssetID]
        LEFT JOIN dbo.VIEW_In ap18            ON [am].[AssetID]    = [ap18].[AssetID]
        LEFT JOIN dbo.VIEW_Out ap19           ON [am].[AssetID]    = [ap19].[AssetID]
        LEFT JOIN dbo.VIEW_Date ap20          ON [am].[AssetID]    = [ap20].[AssetID]
        LEFT JOIN dbo.VIEW_Date ap21          ON [am].[AssetID]    = [ap21].[AssetID]
        LEFT JOIN dbo.VIEW_Season ap22        ON [am].[AssetID]    = [ap22].[AssetID]
        LEFT JOIN dbo.VIEW_Months ap23        ON [am].[AssetID]    = [ap23].[AssetID]
        LEFT JOIN dbo.VIEW_Sleeps ap24        ON [am].[AssetID]    = [ap24].[AssetID]
        LEFT JOIN dbo.VIEW_WiFi ap25          ON [am].[AssetID]    = [ap25].[AssetID]
        LEFT JOIN dbo.VIEW_Creditcards ap26   ON [am].[AssetID]    = [ap26].[AssetID]
        LEFT JOIN dbo.VIEW_Welcome ap27       ON [am].[AssetID]    = [ap27].[AssetID]
        LEFT JOIN dbo.VIEW_Access ap28        ON [am].[AssetID]    = [ap28].[AssetID]
        LEFT JOIN dbo.VIEW_Description ap29   ON [am].[AssetID]    = [ap29].[AssetID]
        LEFT JOIN dbo.VIEW_Restaurant ap30    ON [am].[AssetID]    = [ap30].[AssetID]
        LEFT JOIN dbo.VIEW_Availbale ap31     ON [am].[AssetID]    = [ap31].[AssetID]
        LEFT JOIN dbo.VIEW_Facilites ap32     ON [am].[AssetID]    = [ap32].[AssetID]
        LEFT JOIN dbo.VIEW_Facilities ap33    ON [am].[AssetID]    = [ap33].[AssetID]
        LEFT JOIN dbo.VIEW_Television ap34    ON [am].[AssetID]    = [ap34].[AssetID]
        LEFT JOIN dbo.VIEW_Lounge ap35        ON [am].[AssetID]    = [ap35].[AssetID]
        LEFT JOIN dbo.VIEW_Parking ap36       ON [am].[AssetID]    = [ap36].[AssetID]
        LEFT JOIN dbo.VIEW_Parking ap37       ON [am].[AssetID]    = [ap37].[AssetID]
        LEFT JOIN dbo.VIEW_Machine ap38       ON [am].[AssetID]    = [ap38].[AssetID]
        LEFT JOIN dbo.VIEW_Dishwasher ap39    ON [am].[AssetID]    = [ap39].[AssetID]
        LEFT JOIN dbo.VIEW_Microwave ap40     ON [am].[AssetID]    = [ap40].[AssetID]
        LEFT JOIN dbo.VIEW_Player ap41        ON [am].[AssetID]    = [ap41].[AssetID]
        LEFT JOIN dbo.VIEW_Video ap42         ON [am].[AssetID]    = [ap42].[AssetID]
        LEFT JOIN dbo.VIEW_Shower ap43        ON [am].[AssetID]    = [ap43].[AssetID]
        LEFT JOIN dbo.VIEW_Bath ap44          ON [am].[AssetID]    = [ap44].[AssetID]
        LEFT JOIN dbo.VIEW_Shower ap45        ON [am].[AssetID]    = [ap45].[AssetID]
        LEFT JOIN dbo.VIEW_Player ap46        ON [am].[AssetID]    = [ap46].[AssetID]
        LEFT JOIN dbo.VIEW_Available ap47     ON [am].[AssetID]    = [ap47].[AssetID]
        LEFT JOIN dbo.VIEW_Up ap48            ON [am].[AssetID]    = [ap48].[AssetID]
        LEFT JOIN dbo.VIEW_Shop ap49          ON [am].[AssetID]    = [ap49].[AssetID]
        LEFT JOIN dbo.VIEW_Disposal ap50      ON [am].[AssetID]    = [ap50].[AssetID]
        LEFT JOIN dbo.VIEW_Rating ap51        ON [am].[AssetID]    = [ap51].[AssetID]
        LEFT JOIN dbo.VIEW_Category ap52      ON [am].[AssetID]    = [ap52].[AssetID]
Order By [cm].[CategoryID], [am].[AssetTitle], [am].[ModifiedDate] DESC

Answer 2

What you really need is to revisit your design. You can never get good performance with an EAV table with that many different possible types. EAV tables are SQL antipatterns. Use them only for the very few things that need to be customized not for every possible value. This design is inherently unworkable under load.

Answer 3

One thing that you could look into is using PIVOT. This would still allow you to retrieve your results as you are now, and it would allow you to drop most the joins that y ou are doing.