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