Overclock.net - An Overclocking Community - View Single Post - How to optimize this SQL query

View Single Post
post #1 of (permalink) Old 11-28-2018, 09:42 AM - Thread Starter
Mrzev
New to Overclock.net
 
Mrzev's Avatar
 
Join Date: Feb 2008
Location: Texas
Posts: 2,258
Rep: 96 (Unique: 76)
How to optimize this SQL query

I have this query where it basically looks for all the items in my table that has metadata and list all the unique combinations and how many times they appear. I ran this on a database that had 50,000 items, with 280,000 metadata values with 1756 unique combinations. It took 11 seconds. How can i optimize this? How can i write an index for this? The where clause will also change where i may want to list the results of items in the year 2018. I hate optimizing things....


This is the C# code\query i wrote.
Code:
            var qq = from a in db.Assets
                     where a.Metadatas.Count() > 0
                     join b in db.Metadatas on a.Id equals b.AssetId
                     group b by new { b.MetadataLookupField, b.MetadataLookupValue } into g
                     orderby g.Key.MetadataLookupField, g.Key.MetadataLookupValue
                     select new
                     {
                         key = g.Key,
                         value = g.Count()
                     };



Mrzev is offline