SV: [firebird-support] Query optimization help
We had similar performance problems with an order by in a view, which is quite silly if the view is optimized isolated from the whole query. Particularly when you want to select one single record from a view, it seems that it selects everything, orders it, at then returns what you are looking for. We simply removed the order by in the view (which completely makes sense - why should it be there at all?) and put it in the query using the view instead. Much snappier! Poul -Oprindelig meddelelse- Fra: firebird-support@yahoogroups.com [mailto:firebird- supp...@yahoogroups.com] På vegne af Alexandre Benson Smith Sendt: 25. juli 2013 20:03 Til: firebird-support@yahoogroups.com Emne: Re: [firebird-support] Query optimization help Em 25/7/2013 10:53, Kevin Donn escreveu: On Thu, Jul 25, 2013 at 8:03 AM, Alexandre Benson Smith ibl...@thorsoftware.com.br wrote: Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that Alexandre, if my situation qualifies as a real life example I'd be happy to send you a development database to look at. Contact me directly. kd The fact that your query is slow using views doent mean that it will be fast without the views It could be possible that your query will be fastar if you use direct tables, but if you just translate the views into a single select, I don't think it will be any faster I completely rewrite of it using tables is another thing see you ! ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
Re: SV: [firebird-support] Query optimization help
On Fri, 26 Jul 2013 08:29:10 +, Poul Dige p...@tabulex.dk wrote: We had similar performance problems with an order by in a view, which is quite silly if the view is optimized isolated from the whole query. Particularly when you want to select one single record from a view, it seems that it selects everything, orders it, at then returns what you are looking for. We simply removed the order by in the view (which completely makes sense - why should it be there at all?) and put it in the query using the view instead. Much snappier! A view is not optimized in isolation as far as I am aware, the problem is more likely that the ORDER BY forces the optimizer to ignore some of the possible optimizations (although I can't discount the possibility that using the view adds additional restrictions for the optimizer). There is a reason that for example SQL Server forces you to add a TOP-clause if you define an ORDER BY in a view. Ordering a view usually only makes sense if you want the view itself to return a limited number of rows, and in other cases it will usually degrade performance, because intermediate sorts are bad for performance. Potentially the performance is similar if the query would be constructed by inlining the view. Mark
RE: [firebird-support] Query optimization help
Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Em 24/07/2013 12:07, Leyne, Sean s...@broadviewsoftware.com escreveu: ** Your query: select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ+0=5000 results in plan: PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX))) and increases runtime by about 30% from about 1.5s to 2s. Does that give you any clues about what to try next? Thanks for your effort! 1- Please do not top post/reply. 2 - I think that you may need to create an SP to optimize any further. Your views of Views with several Views using the common AGNTROLE and ACTION_ tables between them and then the DISTINCTs and UNIONs make it difficult to see the forest for the trees for humans and the FB optimizer. Sean [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Query optimization help
Hi ! Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that I had never find such a situation... see you !
Re: [firebird-support] Query optimization help
On Thu, Jul 25, 2013 at 8:03 AM, Alexandre Benson Smith ibl...@thorsoftware.com.br wrote: Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that Alexandre, if my situation qualifies as a real life example I'd be happy to send you a development database to look at. Contact me directly. kd [Non-text portions of this message have been removed]
Re: [firebird-support] Query optimization help
You can send me a copy too. I will try to help you. Em 25/07/2013 10:57, Kevin Donn kd...@msedd.com escreveu: ** On Thu, Jul 25, 2013 at 8:03 AM, Alexandre Benson Smith ibl...@thorsoftware.com.br wrote: Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that Alexandre, if my situation qualifies as a real life example I'd be happy to send you a development database to look at. Contact me directly. kd [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Query optimization help
Em 25/7/2013 10:53, Kevin Donn escreveu: On Thu, Jul 25, 2013 at 8:03 AM, Alexandre Benson Smith ibl...@thorsoftware.com.br wrote: Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that Alexandre, if my situation qualifies as a real life example I'd be happy to send you a development database to look at. Contact me directly. kd The fact that your query is slow using views doent mean that it will be fast without the views It could be possible that your query will be fastar if you use direct tables, but if you just translate the views into a single select, I don't think it will be any faster I completely rewrite of it using tables is another thing see you !
RE: [firebird-support] Query optimization help
Hi Kevin! I could use some guidance on how to optimize a query. The short version is that I have two queries that run fast, but when I combine them, the result is slow. The fast queries (with plans from FlameRobin) are: select * from COESTUD cs where cs.COESEQ=5000 PLAN (CS INDEX (COESTUD_)) select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX))) They both run in well under .1s but when I combine them it takes over 1.5s: select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ=5000 PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_))) Looking at your plan for the second 'good' query and the 'bad' query, the only difference I see is that the former uses COESTUD_SHSEQINDEX and the latter COESTUD_. Hence, I suspect that COESEQ is less selective than STUDENTSEQ and that in this particular query, COESTUD_ slows things down. Try adding +0 to COESEQ, i.e. select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ+0=5000 HTH, Set
Re: [firebird-support] Query optimization help
Your query: select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ+0=5000 results in plan: PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX))) and increases runtime by about 30% from about 1.5s to 2s. Does that give you any clues about what to try next? Thanks for your effort! kd On Wed, Jul 24, 2013 at 2:31 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no wrote: ** Hi Kevin! I could use some guidance on how to optimize a query. The short version is that I have two queries that run fast, but when I combine them, the result is slow. The fast queries (with plans from FlameRobin) are: select * from COESTUD cs where cs.COESEQ=5000 PLAN (CS INDEX (COESTUD_)) select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX))) They both run in well under .1s but when I combine them it takes over 1.5s: select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ=5000 PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_))) Looking at your plan for the second 'good' query and the 'bad' query, the only difference I see is that the former uses COESTUD_SHSEQINDEX and the latter COESTUD_. Hence, I suspect that COESEQ is less selective than STUDENTSEQ and that in this particular query, COESTUD_ slows things down. Try adding +0 to COESEQ, i.e. select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ+0=5000 HTH, Set [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
RE: [firebird-support] Query optimization help
Your query: select * from VIEWABLE_ENROLLMENTS ve join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ where ve.USER_ID=1 and cs.COESEQ+0=5000 results in plan: PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX (AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F NATURAL)) PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX (SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX))) and increases runtime by about 30% from about 1.5s to 2s. Does that give you any clues about what to try next? Thanks for your effort! 1- Please do not top post/reply. 2 - I think that you may need to create an SP to optimize any further. Your views of Views with several Views using the common AGNTROLE and ACTION_ tables between them and then the DISTINCTs and UNIONs make it difficult to see the forest for the trees for humans and the FB optimizer. Sean
[firebird-support] Query optimization help
I could use some guidance on how to optimize a query. The short version is
that I have two queries that run fast, but when I combine them, the result
is slow. The fast queries (with plans from FlameRobin) are:
select * from COESTUD cs where cs.COESEQ=5000
PLAN (CS INDEX (COESTUD_))
select * from VIEWABLE_ENROLLMENTS ve
join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and
ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
where ve.USER_ID=1
PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX
(AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F
NATURAL))
PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR
INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE
VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX
(SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX)))
They both run in well under .1s but when I combine them it takes over 1.5s:
select * from VIEWABLE_ENROLLMENTS ve
join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and
ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
where ve.USER_ID=1 and cs.COESEQ=5000
PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX
(AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F
NATURAL))
PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR
INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE
VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX
(SCHLHIST_FACILITYID), CS INDEX (COESTUD_)))
Here's the relevant ddl:
CREATE TABLE USERS(
ID integer NOT NULL,
USER_ID varchar(80) COLLATE EN_US,
);
CREATE UNIQUE INDEX USERS_ ON USERS (ID);
CREATE UNIQUE INDEX USERS_USER_ID ON USERS (USER_ID);
CREATE TABLE AGNTROLE(
AGENT varchar(30) COLLATE EN_US,
ROLE_ varchar(30) COLLATE EN_US
);
CREATE UNIQUE INDEX AGNTROLE_ ON AGNTROLE (AGENT,ROLE_);
CREATE TABLE ACTION_(
ROLE_ varchar(30),
CLASS varchar(30),
OBJECT varchar(100),
ACTION_ varchar(30),
GOODUNTIL timestamp
);
CREATE UNIQUE INDEX ACTION__ ON ACTION_ (ROLE_,CLASS,OBJECT,ACTION_);
CREATE INDEX ACTION__OBJECTINDEX ON ACTION_ (OBJECT);
CREATE TABLE FACILITY(
FACILITYID varchar(6) COLLATE EN_US,
DISTRICTCODE integer
);
CREATE UNIQUE INDEX FACILITY_ ON FACILITY (FACILITYID);
CREATE INDEX FACILITY_DISTRICTCODE ON FACILITY (DISTRICTCODE);
CREATE TABLE DISTRICT(
DISTRICTCODE integer
);
CREATE UNIQUE INDEX DISTRICT_ ON DISTRICT (DISTRICTCODE);
CREATE TABLE SCHLHIST(
STUDENTSEQ integer,
DOMID varchar(2) COLLATE EN_US,
DBID smallint,
SHSEQ integer,
FACILITYID varchar(6) COLLATE EN_US
);
CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ);
CREATE INDEX SCHLHIST_FACILITYID ON SCHLHIST (FACILITYID);
CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID);
CREATE TABLE STUDENT(
STUDENTSEQ integer
);
CREATE UNIQUE INDEX STUDENT_ ON STUDENT (STUDENTSEQ);
CREATE TABLE COESTUD(
COESEQ integer,
STUDENTSEQ integer,
DOMID varchar(2) COLLATE EN_US,
DBID smallint,
SHSEQ integer
);
CREATE UNIQUE INDEX COESTUD_ ON COESTUD (COESEQ,STUDENTSEQ);
CREATE INDEX COESTUD_SHSEQINDEX ON COESTUD (STUDENTSEQ,DOMID,DBID,SHSEQ);
create view VIEWABLE_ENROLLMENTS (USER_ID, STUDENTSEQ, DOMID, DBID, SHSEQ)
as
select vf.USER_ID, h.STUDENTSEQ, h.DOMID, h.DBID, h.SHSEQ
from VIEWABLE_FACILITIES vf join SCHLHIST h on vf.FACILITYID=h.FACILITYID
create view VIEWABLE_FACILITIES (USER_ID, FACILITYID) as
select distinct u.ID, f.FACILITYID from USERS u
join AGNTROLE ar on ar.AGENT in (cast(u.ID as varchar(10)), '*')
join ACTION_ act on
ar.ROLE_=act.ROLE_ and act.CLASS in ('FACILITY', '*') and
act.ACTION_ in ('ALTER', 'VIEW', '*') and
coalesce(act.GOODUNTIL, current_timestamp) = current_timestamp
join FACILITY f on act.OBJECT in (f.FACILITYID, '*')
union
select distinct al.USER_ID, f.FACILITYID from viewable_leas al
join facility f on al.DISTRICTCODE=f.DISTRICTCODE
create view VIEWABLE_LEAS (USER_ID, DISTRICTCODE) as
select distinct u.ID, d.DISTRICTCODE from USERS u
join AGNTROLE ar on ar.AGENT in (cast(u.ID as varchar(10)), '*')
join ACTION_ act on
ar.ROLE_=act.ROLE_ and act.CLASS in ('LEA', '*') and
act.ACTION_ in ('ALTER', 'VIEW', '*') and
coalesce(act.GOODUNTIL, current_timestamp) = current_timestamp
join DISTRICT d on
act.OBJECT in (cast(d.DISTRICTCODE as varchar(10)), '*')
[Non-text portions of this message have been removed]
