Overclock.net - An Overclocking Community - Reply to Topic
Thread: How to optimize this SQL query Reply to Thread

Register Now

In order to be able to post messages on the Overclock.net - An Overclocking Community forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.
User Name:
If you do not want to register, fill this field only and the name will be used as user name for your post.
Please enter a password for your user account. Note that passwords are case-sensitive.
Confirm Password:
Email Address
Please enter a valid email address for yourself.
Email Address:


  Additional Options
Miscellaneous Options

  Topic Review (Newest First)
12-04-2018 02:38 PM
Mrzev No, its C# code using entity framework that is querying a SQL db. There are a few ways to query it, and this style is very close to SQL. ( i would also need to lookup how to do a join using the other style)

Here is a couple examples from the other style I tend to use more.

  var userPermissions = db.ApplicationUserSourcePermissions.Where(a => a.ApplicationUserId == new Guid(User.Identity.GetUserId()));
            if (!userPermissions.Where(a => a.SourceId == myAsset.SourceId).Any())  // If User does not have permission
                return null;


                    var grouped = data
                    .GroupBy(u => u.Item1)
                    .Select(grp => grp.ToList())
                    //construct filter                    
                    foreach (var itemGroup in grouped)
12-03-2018 05:04 AM
Streetdragon this is SQL? ok new form for me.

where is the select, or do you need all data?
headdata = db.Assets or so
metadata = main table i think nothing new for you

Select count(headdata.id) AS counter,headdata.id,metadata.a,metadata.b
FROM metadatta
left outer join headdata on headdata.id = metadata.id
group by headdata.id,metadata.a,metadata.b

edit: where metadatta.year = 2018
and if you need: and headdata.id is not null
something like that
12-03-2018 04:47 AM
Mrzev I gave up and went back to Azure Search. =( I had no idea how long it would take me to replace the functionality and how well it would scale, so i had to give up.
11-28-2018 09:42 AM
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.
            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()

Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off