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

Forum Jump: 

How to optimize this SQL query

 
Thread Tools
post #1 of 4 (permalink) Old 11-28-2018, 09:42 AM - Thread Starter
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  
Sponsored Links
Advertisement
 
post #2 of 4 (permalink) Old 12-03-2018, 04:47 AM - Thread Starter
New to Overclock.net
 
Mrzev's Avatar
 
Join Date: Feb 2008
Location: Texas
Posts: 2,258
Rep: 96 (Unique: 76)
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.



Mrzev is offline  
post #3 of 4 (permalink) Old 12-03-2018, 05:04 AM
Manual breath enabled
 
Streetdragon's Avatar
 
Join Date: Apr 2013
Location: In my celler
Posts: 1,174
Rep: 26 (Unique: 22)
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

Gameingcube
(18 items)
CPU
Ryzen 3900X
Motherboard
Aorus X570 Master
GPU
1080 TI Founders Edition
GPU
1080 TI Founders Edition
RAM
F4-3600C16Q-32GTZR
Hard Drive
2000GB Seagate Barracuda Compute ST2000DM008 256MB
Hard Drive
2000GB Seagate Barracuda Compute ST2000DM008 256MB
Hard Drive
samsung m2 970 evo
Power Supply
Leadex 80 Plus Platinum Netzteil, schwarz - 1200 Watt
Cooling
heatkiller IV pro
Cooling
EK-FC1080 GTX Ti - Nickel
Cooling
EK-FC1080 GTX Ti - Nickel
Cooling
Alphacool Eisbecher D5 250mm Plexi
Cooling
Alphacool NexXxoS XT45 Full Copper 480mm
Cooling
XSPC Multiport Radiator EX240
Cooling
Scythe Grand Flex 4Pin PWM
Case
thermaltake core x9
Operating System
Windows 10
▲ hide details ▲
Streetdragon is offline  
Sponsored Links
Advertisement
 
post #4 of 4 (permalink) Old 12-04-2018, 02:38 PM - Thread Starter
New to Overclock.net
 
Mrzev's Avatar
 
Join Date: Feb 2008
Location: Texas
Posts: 2,258
Rep: 96 (Unique: 76)
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)
                    {



Mrzev is offline  
Reply

Quick Reply
Message:
Options

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.
Password
Please enter a password for your user account. Note that passwords are case-sensitive.
Password:
Confirm Password:
Email Address
Please enter a valid email address for yourself.
Email Address:

Log-in



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page


Forum Jump: 

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
Trackbacks are Off
Pingbacks are Off
Refbacks are Off