Overclock.net - An Overclocking Community

Overclock.net - An Overclocking Community (https://www.overclock.net/forum/)
-   Coding and Programming (https://www.overclock.net/forum/142-coding-programming/)
-   -   How to optimize this SQL query (https://www.overclock.net/forum/142-coding-programming/1714592-how-optimize-sql-query.html)

Mrzev 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.
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 12-03-2018 04:47 AM

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.

Streetdragon 12-03-2018 05:04 AM

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

Mrzev 12-04-2018 02:38 PM

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.
Code:



  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())
                    .ToList();                   
                   
                    //construct filter                   
                    foreach (var itemGroup in grouped)
                    {



All times are GMT -7. The time now is 01:34 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

User Alert System provided by Advanced User Tagging (Pro) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
vBulletin Security provided by vBSecurity (Pro) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.

vBulletin Optimisation provided by vB Optimise (Pro) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.