I'm trying to improve an sql query made in sybase sql anywhere 5.5 (I know it's old but that's my task, and upgrading sybase version is not currently an option)
select
sum(pd.tax) as totaltax1,sum(pd.tax2) as totaltax2,sum(pd.tax3) as totaltax3,
sum(pd.price) as totalttc,
sum(case when pd.tax<>0 then pd.taxex else 0 end) as tax1able,
sum(case when pd.tax2<>0 then pd.taxex else 0 end) as tax2able,
sum(case when pd.tax3<>0 then pd.taxex else 0 end) as tax3able,
sum(case when pd.tax+pd.tax2+pd.tax3=0 then pd.taxex else 0 end) as nontaxable,
isnull(ra.stax1able,'') as stax1able,isnull(ra.stax1,'') as stax1,
isnull(ra.stax2able,'') as stax2able,isnull(ra.stax2,'') as stax2,
isnull(ra.stax3able,'') as stax3able,isnull(ra.stax3,'') as stax3,
isnull(ra.snontaxable,'') as snontaxable,
isnull(ra.costcenterid,0) as costcenterid,isnull(ra.depcode,0) as depcode,isnull(ra.debitcoa,'') as debitcoa
from("dba".salesheader as ph join
"dba".salesdetail as pd on ph.transact=pd.transact and ph.branchid=pd.branchid) left outer join
"dba".members as m on ph.memcode=m.id left outer join
"dba".accounting_settings as ra on ra."type"=4 and ra.branchid=1
where ph.branchid=1 and ph.opendate=20150808 and ph.amount=ph.paid and(ph.memcode=0 or m.forceexport=0)
group by ra.stax1able,ra.stax1,ra.stax2able,ra.stax2,ra.stax3able,ra.stax3,ra.snontaxable,ra.costcenterid,ra.depcode,ra.debitcoa
Table Data:
The above query is taking 7 to 8 seconds which is huge! Any hints on improving the query?
P.S. All join columns have indexes (ph.transact, pd.transact, ph.branchid, pd.branchid, ph.memcode, m.id, ra.type, ra.branchid) also, all filtered columns in the where clause have indexes (ph.opendate, ph.amount, ph.paid, m.forceexport)
Things I've tried:
None of these changes have affected the performance (still takes 7-8 seconds)
What should i do?
Ok, I have managed to enhance the sql from 7-8 seconds to 188ms only by adding a foreign key between the salesheader and salesdetail tables.. however, I've been researching the net about this, and i have read that foreign keys do not enhance query performance.
Foreign keys do not directly speed up the execution of queries. They do have an indirect effect, because they guarantee that the referenced column is indexed. And the index will have an impact on performance. As you describe the problem, all the join relationships should include the primary key on one of the tables. (Does using Foreign Key speed up table joins)
Furthermore, after creating the foreign key, i have deleted it and retested the query, and it did take 7-8 seconds.
Any hints, on why in my case, the foreign key speed up the query?