CODEWITHSUNDEEP
sqlpostgresqlquery-optimizationodoo
Priyan RockZ
Priyan RockZ

Reputation: 1615

SQL JOIN query Optimization with subqueries

I used below subquery for get attached records. i need to know is it Optimized query for my task (seems within three month its exists records more than 10,000 ).then is it support for that data load.?

can i use JOIN keyword instead of this below method.please advice me to sort this out. currently i'm using postgresql as my backend.

select worker,worktype,paymenttype,sum(output)as totalkgs_ltrs,sum(overkgs)as overkgs_ltrs,sum(workedhrs) as workedhrs,sum(scrap) as scrap,sum(cashworkincome) as cashworkincome,sum(pss) as pss
from (select
  comp.name as company, 
  est.name as estate, 
  div.name as division, 
  wkr.name as worker, 
  txn.date as updateddate, 
  txn.type as worktype, 
  txn.payment_type as paymenttype, 
  txn.names as workedhrs, 
  txn.norm as norm, 
  txn.output as output, 
  txn.over_kgs as overkgs, 
  txn.scrap as scrap, 
  txn.cash_work_income as cashworkincome,
  txn.pss as pss
from 
  bpl_daily_transaction_master txn, 
  res_company comp, 
  bpl_division_n_registration div, 
  bpl_estate_n_registration est, 
  bpl_worker wkr
where 
  comp.id = txn.bpl_company_id and
  div.id  = txn.bpl_division_id and
  est.id  = txn.bpl_estate_id and
  wkr.id  = txn.worker_id
)as subq
group by worker,worktype,paymenttype

here shows my result when i execute this query

refer this image

here is the subquery's code & results tagged at bottom section

select
  comp.name as company, 
  est.name as estate, 
  div.name as division, 
  wkr.name as worker, 
  txn.date as updateddate, 
  txn.type as worktype, 
  txn.payment_type as paymenttype, 
  txn.names as workedhrs, 
  txn.norm as norm, 
  txn.output as output, 
  txn.over_kgs as overkgs, 
  txn.scrap as scrap, 
  txn.cash_work_income as cashworkincome,
  txn.pss as pss
from 
  bpl_daily_transaction_master txn, 
  res_company comp, 
  bpl_division_n_registration div, 
  bpl_estate_n_registration est, 
  bpl_worker wkr
where 
  comp.id = txn.bpl_company_id and
  div.id  = txn.bpl_division_id and
  est.id  = txn.bpl_estate_id and
  wkr.id  = txn.worker_id

this is above main query result and its shows all records

refer this image

Upvotes: 1

Views: 218

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

select wkr.name as worker,txn.type as worktype,txn.payment_type as paymenttype,sum(txn.output)as totalkgs_ltrs,sum(txn.over_kgs)as overkgs_ltrs,
       sum(txn.names) as workedhrs,sum(txn.scrap) as scrap,sum(txn.cash_work_income) as cashworkincome,sum(txn.pss) as pss

from 
  bpl_daily_transaction_master txn
inner join res_company comp
   on comp.id = txn.bpl_company_id 
inner join bpl_division_n_registration div
   on div.id  = txn.bpl_division_id
inner join bpl_estate_n_registration est
   on est.id  = txn.bpl_estate_id
inner join bpl_worker wkr
   on wkr.id  = txn.worker_id

group by wkr.name,txn.type,txn.payment_type

What you are are doing in your subquery is old ANSI SQL -89 syntax for joining tables which is not recommended. But as far as performance is concerned I don't think there is difference as confirmed on this stackoverflow thread.

According to "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so the human-readable syntax makes no difference.

Upvotes: 2

Related Questions