Closed Bug 364162 Opened 18 years ago Closed 16 years ago

Code determining if named queries should be shown in the page footer is suboptimal

Categories

(Bugzilla :: Creating/Changing Bugs, defect)

2.23.3
defect
Not set
minor

Tracking

()

RESOLVED FIXED
Bugzilla 3.2

People

(Reporter: bugzilla-mozilla, Assigned: LpSolit)

Details

(Keywords: perf)

Attachments

(1 file, 1 obsolete file)

The following SQL queries are called near the beginning of a show_bug.cgi page load: SELECT id FROM namedqueries WHERE userid = '5407' SELECT id,userid,name,query,query_type FROM namedqueries WHERE id IN (1472,2077,766,2196,2195,1750,1325,1784,1785) ORDER BY name SELECT lif.namedquery_id FROM namedqueries_link_in_footer lif INNER JOIN namedquery_group_map ngm ON ngm.namedquery_id = lif.namedquery_id WHERE lif.user_id = '5407' AND lif.namedquery_id NOT IN (1472,2077,766,2196,2195,1750,1325,1784,1785) AND ngm.group_id IN (16,12,3,9,13,15,4,1,6,14,11,2,5,7,8) Then almost at the end, it does this for every named query: SELECT 1 FROM namedqueries_link_in_footer WHERE namedquery_id = '1472' AND user_id = '5407' SELECT 1 FROM namedqueries_link_in_footer WHERE namedquery_id = '2077' AND user_id = '5407' SELECT 1 FROM namedqueries_link_in_footer WHERE namedquery_id = '766' AND user_id = '5407' At least above SQL should be called just once; not per query. Perhaps it can be avoided by the earlier SQL statements? For the whole SQL log, please see attachment 248929 [details].
Keywords: perf
Assignee: general → create-and-change
Severity: normal → minor
Component: Bugzilla-General → Creating/Changing Bugs
We could define a new Bugzilla::User method: selected_queries() (or a name like that) which would do a single SQL call, getting all queries the user wants to see in the footer. Or even better, put the link_in_footer column in the namedqueries table, so that new_from_list() would get this information at the same time it builds the list. This means the namedqueries_link_in_footer table would be for other users queries only, i.e. those you don't own.
We could also just load the information into the object at creation time using a join. That would require modifications to Bugzilla::Object, but could definitely be done, and I don't think it would significantly slow down Bugzilla::User at all.
By looking at the code, the easiest and safest fix is to preload this information in Bugzilla->user->queries. This is trivial to do. Note that this bug seems to affect some users on b.m.o, per comments 45 and 52 in bug 453459.
Target Milestone: --- → Bugzilla 3.2
Attached patch patch, v1 (obsolete) — Splinter Review
This should make all pages faster as we now query namedqueries_link_in_footer only once for *all* user queries instead of once *per* user query. Also, this should improve userprefs.cgi?tab=saved-searches a bit as this information is now already stored.
Assignee: create-and-change → LpSolit
Status: NEW → ASSIGNED
Attachment #340798 - Flags: review?(bugzilla-mozilla)
Comment on attachment 340798 [details] [diff] [review] patch, v1 Since this code modifies internal variables in Bugzilla::Search::Saved, I'd like to see it as a preload function there.
Attached patch patch, v2Splinter Review
Implement preload().
Attachment #340798 - Attachment is obsolete: true
Attachment #340835 - Flags: review?(bugzilla-mozilla)
Attachment #340798 - Flags: review?(bugzilla-mozilla)
Attachment #340835 - Flags: review?(wicked)
Comment on attachment 340835 [details] [diff] [review] patch, v2 wicked, are you faster than bkor?
Attachment #340835 - Flags: review?(bugzilla-mozilla) → review?(mkanat)
Comment on attachment 340835 [details] [diff] [review] patch, v2 Looks great!
Attachment #340835 - Flags: review?(wicked)
Attachment #340835 - Flags: review?(mkanat)
Attachment #340835 - Flags: review+
Flags: approval3.2+
Flags: approval+
tip: Checking in Bugzilla/User.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/User.pm,v <-- User.pm new revision: 1.176; previous revision: 1.175 done Checking in Bugzilla/Search/Saved.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search/Saved.pm,v <-- Saved.pm new revision: 1.8; previous revision: 1.7 done 3.2rc2: Checking in Bugzilla/User.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/User.pm,v <-- User.pm new revision: 1.164.2.4; previous revision: 1.164.2.3 done Checking in Bugzilla/Search/Saved.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search/Saved.pm,v <-- Saved.pm new revision: 1.7.2.1; previous revision: 1.7 done
Status: ASSIGNED → RESOLVED
Closed: 16 years ago
Resolution: --- → FIXED
Summary: Inefficient SQL: Determining if named query should be shown in footer → Code determining if named queries should be shown in the page footer is suboptimal
You need to log in before you can comment on or make changes to this bug.

Attachment

General

Created:
Updated:
Size: