0

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:

  • salesheader has only: 327,285 records
  • salesdetail has only: 1,017,513 records
  • members has only: 11,785 records
  • accounting_settings has only: 13 records

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:

  1. Adding indexes for the group by columns (ra.stax1able, ra.stax1, ra.stax2able, ra.stax2, ra.stax3able, ra.stax3, ra.snontaxable, ra.costcenterid, ra.depcode, ra.debitcoa)
  2. Adding indexes to the summarized fields (pd.tax, pd.tax2, pd.tax3, pd.taxex)
  3. Creating a view with the sql without the where part, then running the view
  4. Creating a storedprocedure which takes opendate and branchid as parameters

None of these changes have affected the performance (still takes 7-8 seconds)

What should i do?

Dany Balian
  • 608
  • 7
  • 16
  • There's no query plan in sybase central for version 5.5 – Dany Balian Nov 16 '15 at 12:59
  • check the GRAPHICAL_PLAN function information from the documentation here: http://infocenter.sybase.com/archive/topic/com.sybase.help.adaptive_server_anywhere_9.0.2/pdf/asa902/dbrfen9.pdf#sec-workingwdb-s-3995211 – Mike Gardner Nov 16 '15 at 15:44
  • @MichaelGardner this document is for sybase 9.. there's no graphical_plan in sybase 5.. – Dany Balian Nov 17 '15 at 22:39

2 Answers2

0

Your accounting_settings-table is not joined with other tables. First, check that.

To make maximum use of indexes make sure you have the index in place for:

  1. For columns which limits the I/O most (most likely ph.opendate, possibly ph.branchid depending on how many branchid's there exist)
  2. Making sure the joining columns match (ph.transact, ph.branchid) for big joins

So, try a composite index (opendate, branchid, memcode) for salesheader and (transact, branchid) for the salesdetail

slaakso
  • 8,331
  • 2
  • 16
  • 27
  • accounting_settings is joined as it should (settings by branch). all the columns specified in your answer are already indexed (even the composite joins) any other hints? – Dany Balian Nov 17 '15 at 22:39
  • What is the output if you run: "describe index for table salesdetail" and "describe index for table salesheader"? As for accounting_settings, it is better to use ra.branchid= ph.branchid. This may give optimizer more information about the query (not likely have effect here though). – slaakso Nov 18 '15 at 16:53
  • describe index is a command in Interactive SQL. Not sure about the 5.5 as it is bit old. – slaakso Nov 18 '15 at 21:02
  • Yeah i'm familiar with many of the profiling tools in sybase 12 however i'm out of bullets with 5.5; anyways i will try to get hand on a sybase 12 db, upgrade my current db and see if i can get any analysis on why this sql is slow! Then will try to enhance it on 12 and apply it on the 5.5 – Dany Balian Nov 19 '15 at 21:05
0

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?

Community
  • 1
  • 1
Dany Balian
  • 608
  • 7
  • 16