[Building Sakai] Another slow query

[Paul Dagnall]

Jeremy
I recently created a new method of getting the members from a group in
samigo. It was slow because the query took around 2 seconds and it had to do
it for each student in the site to determine if the student belonged in a
group the assessment was released to. We had a site with 200 students, and
so that took around five minutes to load the page every time.

I changed it so it would just call one query, one time. It made a dramatic
improvement. The page loads in 15 seconds now. We're still testing it but
I'll share it when I feel it's ok.

Paul Dagnall
Application Developer & Administrator
University of Dayton


On Mon, Oct 17, 2011 at 10:15 AM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:

> We have been working with MySQL/Oracle on performance issues, besides the
> Samigo queries, this was one of our top slow queries, and what their support
> had to say about it.****
>
> ** **
>
> I have been looking at the query
>
> SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME FROM
> SAKAI_REALM_RL_FN INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'2F736974652F313531303437' INNER JOIN SAKAI_REALM_ROLE ON
> SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY INNER JOIN
> SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;
>
> which is one of those showing up often in the slow query log. I noticed
> that the query optimizer chooses to do a table (or rather index) scan on the
> SAKAI_REALM_ROLE table even though there is an index it can use. It might
> not be faster to force it to use an index and I don't know to what extend
> you are able to influence the exact queries generated by your application,
> but it might be worth a shot.
>
> When you have a chance, can I get you to run the queries below to explore
> some alternative query paths? (the reformatting is just to make it easier to
> read). The \P tail -10 ensures only the last to lines of the queries are
> displayed, so you are not flooded with data.
>
> \P tail -10
>
> -- The original query - for reference
> EXPLAIN
> SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM_RL_FN
> INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'7268796E63686F63657068616C69616E'
> INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
> SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;
>
> SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM_RL_FN
> INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'7268796E63686F63657068616C69616E'
> INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
> SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;
>
> -- Forcing to use the index on SAKAI_REALM_ROLE.ROLE_KEY (the PRIMARY KEY)
> EXPLAIN
> SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM_RL_FN
> INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'7268796E63686F63657068616C69616E'
> INNER JOIN SAKAI_REALM_ROLE FORCE INDEX (PRIMARY) ON
> SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;
>
> SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM_RL_FN
> INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'7268796E63686F63657068616C69616E'
> INNER JOIN SAKAI_REALM_ROLE FORCE INDEX (PRIMARY) ON
> SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;
>
> -- Force the JOIN order
> EXPLAIN
> SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
> SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM
> INNER JOIN SAKAI_REALM_RL_FN ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY
> INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
> SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY
> WHERE SAKAI_REALM.REALM_ID = x'7268796E63686F63657068616C69616E';
>
> SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
> SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM
> INNER JOIN SAKAI_REALM_RL_FN ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY
> INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
> SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY
> WHERE SAKAI_REALM.REALM_ID = x'7268796E63686F63657068616C69616E';
>
> -- Alternative for forced JOIN ordier
> EXPLAIN
> SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
> SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM_RL_FN
> INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'7268796E63686F63657068616C69616E'
> INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
> SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;
>
> SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
> SAKAI_REALM_FUNCTION.FUNCTION_NAME
> FROM SAKAI_REALM_RL_FN
> INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
> SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
> x'7268796E63686F63657068616C69616E'
> INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
> SAKAI_REALM_RL_FN.ROLE_KEY
> INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
> SAKAI_REALM_RL_FN.FUNCTION_KEY;****
> This message is private and confidential. If you have received it in error,
> please notify the sender and remove it from your system.
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111019/209978ca/attachment.html