Home » SQL & PL/SQL » SQL & PL/SQL » Suggestiions on improving performance in this SQL (merged 3)
Suggestiions on improving performance in this SQL (merged 3) [message #414033] Mon, 20 July 2009 03:03 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

i'm running a bit of SQL that reads from a financial table all records who's 'transno' does not exist in another table.

It is painfully slow, so I wondered if anyone can suggest a better/quicker way of doing it.

here is the SQL:
SELECT to_char(wip.transno) as TRANSACTION, wip.transtype, wip.entity, wip.charge_code, wip.company_code, wip.project_code, wip.projectclass, wip.resource_code, wipv.currency_code,  wipv.amount
	FROM niku.ppa_wip wip, niku.ppa_wip_values wipv
	WHERE wip.entity='DSTUK'
	and wip.transtype = 'L'
	and wip.transno = wipv.transno
	and wipv.currency_type = 'HOME'
	and (wip.transdate between to_date('01/05/2008', 'dd/mm/yyyy') AND to_date('01/07/2008', 'dd/mm/yyyy'))
	and wip.transno NOT IN(select gl.transno from niku.dsti_gl_control gl)


any advice is appreciated.
Thanks in advance,
Matt
Re: Suggestions on improving performance in this SQL [message #414044 is a reply to message #414033] Mon, 20 July 2009 03:41 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Look at the syntax/ usage for NOT EXISTS.
Previous Topic: LOB
Next Topic: Need a Query to update....below are requirements
Goto Forum:
  


Current Time: Sun Dec 11 07:55:56 CST 2016

Total time taken to generate the page: 0.08004 seconds