/auk

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Dashboard sql optimization #241

Open
ruebot opened this Issue Jan 8, 2019 · 1 comment

Comments

Assignees

@ruebot ruebot

Labels
Dashboard Rails optimization
Projects
None yet
Milestone
No milestone
1 participant
@ruebot
@ruebot
Member

ruebot commented Jan 8, 2019 •
edited

We have a couple really slow queries on our dashboard that causes a very slow page load.

These two are the main culprits:

auk/app/helpers/dashboards_helper.rb

Lines 61 to 67 in bdd62ac

def get_largest_collection
largest_collection = WasapiFile.group(:user_id)
.group(:collection_id)
.sum(:size)
.max_by { |_k, v| v }
number_to_human_size(largest_collection[1])
end 

883.0ms: SELECT SUM("wasapi_files"."size") AS sum_size, "wasapi_files"."user_id" AS wasapi_files_user_id, "wasapi_files"."collection_id" AS wasapi_files_collection_id FROM "wasapi_files" GROUP BY "wasapi_files"."user_id", "wasapi_files"."collection_id";

auk/app/helpers/dashboards_helper.rb

Lines 77 to 79 in bdd62ac

def get_total_number_of_warcs
WasapiFile.distinct.count(:filename)
end 

22309.8ms: SELECT COUNT(DISTINCT "wasapi_files"."filename") FROM "wasapi_files";

@ruebot ruebot added Dashboard optimization Rails labels Jan 8, 2019

@ruebot ruebot self-assigned this Jan 8, 2019

@ruebot

This comment has been minimized.

Sign in to view
Member

ruebot commented Jan 8, 2019

SELECT COUNT(*) FROM (SELECT DISTINCT "wasapi_files"."filename" FROM "wasapi_files") AS temp;

Takes the get_total_number_of_warcs query from ~22309.8ms to 1706.4ms

ruebot added a commit that referenced this issue Jan 8, 2019

@ruebot
Optimize get_total_number_of_warcs query; address #241.
Verified
This commit was signed with a verified signature.
@ruebot ruebot Nick Ruest
GPG key ID: 417FAF1A0E1080CD Learn about signing commits
Loading status checks…
b909e27

ruebot added a commit that referenced this issue Jan 8, 2019

@ruebot
Optimize get_total_number_of_warcs query; address #241.
Verified
This commit was signed with a verified signature.
@ruebot ruebot Nick Ruest
GPG key ID: 417FAF1A0E1080CD Learn about signing commits
Loading status checks…
5a1c4f2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment