Databases
StackOverflow2013
Postsdbo
POHow should I optimize this SQL query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow should I optimize this SQL query?
    primarykey
    data
    text
    <p>I have five tables called <code>Asset Master</code>, <code>Category Master</code>, <code>Asset Category</code>, <code>Asset Parameter</code>, and <code>Parameter Master</code>.</p> <p>I have more than 50 parameters in the <code>Parameter Master</code> table, every parameter value stored in <code>Asset Parameter</code> table as per asset.</p> <p>Every asset assign to more than one category so, there is one table called <code>Asset Category</code> which is middle table for <code>Category Master</code> and <code>Asset Master</code>.</p> <p>Now, I want that 50 parameters value as well as <code>category</code> and <code>asset</code> information in single query, which is written below.</p> <p>This query give result perfectly but need to optimize it. Anyone suggest me proper way to optimize it.</p> <p>I am using Left join cause Parameter value's type is text in db and need to handle null value.</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USsikender
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow should I optimize this SQL query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow should I optimize this SQL query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow should I optimize this SQL query?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COGet a query execution plan
      singulars
      1. POHow should I optimize this SQL query?
      1. USJustin
    2. COThis is what happens when people abuse [EAV models](http://en.wikipedia.org/wiki/Entity-attribute-value_model). I wouldn't even try to optimize this horror, just redesign your database.
      singulars
      1. POHow should I optimize this SQL query?
      1. USAaronaught
    3. COThanks for your comment but This not a any solution.
      singulars
      1. POHow should I optimize this SQL query?
      1. USsikender
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload