Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
Re: Slow Query problem
- From: "Premsun Choltanwanich" <Premsun(at)nsasia(dot)co(dot)th>
- To: "Andreas Joseph Krogh" <andreak(at)officenet(dot)no>, <pgsql-sql(at)postgresql(dot)org>
- Subject: Re: Slow Query problem
- Date: Wed, 30 Jan 2008 10:38:49 +0700
- Message-id: <47A0539B.C5F7.004C.0@nsasia.co.th> <text/plain>
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-874">
<META content="MSHTML 6.00.2900.3243" name=GENERATOR></HEAD>
<BODY style="MARGIN: 4px 4px 1px; FONT: 10pt MS Sans Serif">
<DIV><STRONG>SQL I use for create related table and view:</STRONG></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_payment_detail<BR>(<BR> "sysid" bigserial NOT NULL,<BR> receiptno varchar(10) NOT NULL,<BR> refpath varchar(255) NOT NULL,<BR> refno varchar(100) NOT NULL,<BR> CONSTRAINT t_payment_detail_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITHOUT OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_receipt_cancel<BR>(<BR> receiptsysid int8 NOT NULL,<BR> amount float8 NOT NULL,<BR> receiptcomment varchar(255) NOT NULL,<BR> CONSTRAINT t_receipt_cancel_pkey PRIMARY KEY (receiptsysid)<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_charge<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> chargelistsysid int8 NOT NULL,<BR> refno varchar(20) NOT NULL,<BR> chargedate timestamp NOT NULL,<BR> quantity float8 NOT NULL,<BR> unitprice float8 NOT NULL,<BR> amount float8 NOT NULL,<BR> vat float8 NOT NULL,<BR> service float8 NOT NULL,<BR> CONSTRAINT t_charge_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV><BR>CREATE TABLE t_posbill<BR>(<BR> "sysid" bigserial NOT NULL,<BR> billno varchar(20) NOT NULL DEFAULT ''::character varying,<BR> billdate timestamp NOT NULL DEFAULT '1601-01-01 00:00:00'::timestamp without time zone,<BR> mbrsysid int8 NOT NULL DEFAULT 0,<BR> totalamount float8 NOT NULL DEFAULT 0,<BR> totalvat float8 NOT NULL DEFAULT 0,<BR> totalservice float8 NOT NULL DEFAULT 0,<BR> CONSTRAINT t_posbill_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITHOUT OIDS;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_creditnotes<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> chargelistsysid int8 NOT NULL,<BR> chitno varchar(20) NOT NULL,<BR> chitdate timestamp NOT NULL,<BR> refno varchar(20) NOT NULL,<BR> chargedate timestamp NOT NULL,<BR> quantity float8 NOT NULL,<BR> unitprice float8 NOT NULL,<BR> amount float8 NOT NULL,<BR> vat float8 NOT NULL,<BR> service float8 NOT NULL,<BR> CONSTRAINT t_creditnotes_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_invoice<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> chargelistsysid int8 NOT NULL,<BR> invno varchar(50) NOT NULL,<BR> invdate timestamp NOT NULL,<BR> totalvalue float8 NOT NULL,<BR> totalvat float8 NOT NULL,<BR> totalservice float8 NOT NULL,<BR> CONSTRAINT t_invoice_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_receipt<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> receiptno varchar(10) NOT NULL,<BR> receiptdate timestamp NOT NULL,<BR> paymethod varchar(30) NOT NULL,<BR> amount float8 NOT NULL,<BR> flagprint bool NOT NULL DEFAULT false,<BR> CONSTRAINT t_receipt_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_moneytransfer<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> transferno varchar(10) NOT NULL,<BR> transferdate timestamp NOT NULL,<BR> transferamount float8 NOT NULL,<BR> CONSTRAINT t_moneytransfer_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_carryforward<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> cfno varchar(10) NOT NULL,<BR> cfdate timestamp NOT NULL,<BR> amount float8 NOT NULL,<BR> CONSTRAINT t_carryforward_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV><BR>CREATE OR REPLACE VIEW v_invtransaction_main AS <BR>((((( SELECT 'C'::text || t_charge.refno::text AS transinvno, t_charge.mbrsysid, 'CHIT'::text AS particular, t_charge.chargedate AS transdate, sum(t_charge.amount) AS totamount, sum(t_charge.vat) AS totvat, sum(t_charge.service) AS totservice, 'Dr' AS cr_dr, t_charge.refno AS transrefno<BR> FROM t_charge<BR> GROUP BY 'C'::text || t_charge.refno::text, t_charge.mbrsysid, 'CHIT'::text , t_charge.chargedate, t_charge.refno<BR>UNION ALL <BR> SELECT 'B'::text || t_posbill.billno::text AS transinvno, t_posbill.mbrsysid, 'POS'::text AS particular, t_posbill.billdate AS transdate, t_posbill.totalamount AS totamount, t_posbill.totalvat AS totvat, t_posbill.totalservice AS totservice, 'Dr' AS cr_dr, t_posbill.billno AS transrefno<BR> FROM t_posbill)<BR>UNION ALL <BR> SELECT 'CN'::text || t_creditnotes.refno::text AS transinvno, t_creditnotes.mbrsysid, 'Credit Note'::text AS particular, t_creditnotes.chargedate AS transdate, - sum(t_creditnotes.amount) AS totamount, - sum(t_creditnotes.vat) AS totvat, - sum(t_creditnotes.service) AS totservice, 'Cr' AS cr_dr, t_creditnotes.refno AS transrefno<BR> FROM t_creditnotes<BR> GROUP BY 'CN'::text || t_creditnotes.refno::text, t_creditnotes.mbrsysid, 'Credit Note'::text, t_creditnotes.chargedate, t_creditnotes.refno)<BR>UNION ALL <BR> SELECT 'I'::text || t_invoice.invno::text AS transinvno, t_invoice.mbrsysid, 'Monthly Invoice '::text AS particular, t_invoice.invdate AS transdate, sum(t_invoice.totalvalue) AS totamount, sum(t_invoice.totalvat) AS totvat, sum(t_invoice.totalservice) AS totservice, 'Dr' AS cr_dr, t_invoice.invno AS transrefno<BR> FROM t_invoice<BR> GROUP BY 'I'::text || t_invoice.invno::text, t_invoice.mbrsysid, 'Monthly Invoice'::text, t_invoice.invdate,t_invoice.invno)<BR>UNION ALL <BR> SELECT 'P'::text || t_receipt.receiptno::text AS transinvno, t_receipt.mbrsysid, t_receipt.paymethod::text AS particular, t_receipt.receiptdate AS transdate, t_receipt.amount * (- 1::double precision) AS totamount, '0' AS totvat, '0' AS totservice, 'Cr' AS cr_dr, t_receipt.receiptno AS transrefno<BR> FROM t_receipt<BR> WHERE NOT (t_receipt."sysid" IN ( SELECT t_receipt_cancel.receiptsysid FROM t_receipt_cancel)))<BR>UNION ALL <BR> SELECT 'T'::text || t_moneytransfer.transferno::text AS transinvno, t_moneytransfer.mbrsysid, 'Transfer'::text AS particular, t_moneytransfer.transferdate AS transdate, t_moneytransfer.transferamount * (- 1::double precision) AS totamount, '0' AS totvat, '0' AS totservice, 'Dr' AS cr_dr, t_moneytransfer.transferno AS transrefno<BR> FROM t_moneytransfer)<BR>UNION ALL <BR> SELECT 'CF'::text || t_carryforward.cfno::text AS transinvno, t_carryforward.mbrsysid, 'Carry Forward'::text AS particular, t_carryforward.cfdate AS transdate, t_carryforward.amount AS totamount, '0' AS totvat, '0' AS totservice, 'Dr' AS cr_dr, t_carryforward.cfno AS transrefno<BR> FROM t_carryforward;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE OR REPLACE VIEW v_invtransaction AS <BR> SELECT t_payment_detail.receiptno, v_invtransaction_main.transinvno, v_invtransaction_main.mbrsysid, v_invtransaction_main.particular, v_invtransaction_main.transdate, v_invtransaction_main.totamount, v_invtransaction_main.totvat, v_invtransaction_main.totservice, v_invtransaction_main.cr_dr, v_invtransaction_main.transrefno<BR> FROM t_payment_detail<BR> RIGHT JOIN v_invtransaction_main ON v_invtransaction_main.transrefno::text = t_payment_detail.refno::text AND v_invtransaction_main.particular = t_payment_detail.refpath::text;<BR></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><BR>>>> Andreas Joseph Krogh <andreak(at)officenet(dot)no> 1/28/2008 19:26 >>><BR>On Monday 28 January 2008 08:18:24 Premsun Choltanwanich wrote:<BR>> Dear All,<BR>><BR>> I am currently using PostgreSQL database version 8.0.13. My problem<BR>> relates to a slow result when a query using a defined view joins to another<BR>> table for a result.<BR>><BR>> Background: I have 7 tables of invoice transactions. The tables are<BR>> slightly different in that they record different data (some different<BR>> columns in each table). There are about 250,000 records when a union view<BR>> is created. A simply query on this union performs satisfactorily.<BR>><BR>> The invoice table union view is then joined with a table of receipts (which<BR>> have a total of about 150,000 records).<BR>><BR>> It takes around 3.5 seconds for "select * from view_transaction where<BR>> member_id = 999 and receipt_no is null" (which returns unpaid invoices).<BR>><BR>> By hard coding I created a single table from the 7 invoice tables (instead<BR>> of creating a union) and then used it with receipt table. This time for<BR>> the same query improved to 1.8 seconds.<BR>><BR>> To further improve things I tried to code the selection rather than to use<BR>> a view, and so "select * from temp_transaction where member_id = 999 and<BR>> receipt_no is null" provided the result in .5 second. (2 records returned<BR>> containing the details of receipt_no, transaction_no, transaction_type,<BR>> transaction_amount, member_id).<BR>><BR>> I would prefer to be able to have completed the above by using unions and<BR>> views. Is it possible to do this, or am I better creating a permanent<BR>> table of invoices and writing the query as I did above?<BR>><BR>> Any comments on this and suggestions would be appreciated. If there is<BR>> documentation where I can read up please let me have a link.<BR><BR>It is very hard to help without you providing the schema for the tables/views <BR>involved. It sounds like you don't have any indexes if you experience <BR>performance-problems on queries like "select * from view_transaction where <BR>member_id = 999 and receipt_no is null". But again, without the definition <BR>of the view and underlying tables, it's very hard to help.<BR><BR>-- <BR>Andreas Joseph Krogh <andreak(at)officenet(dot)no><BR>Senior Software Developer / Manager<BR>------------------------+---------------------------------------------+<BR>OfficeNet AS | The most difficult thing in the world is to |<BR>Karenslyst All? 11 | know how to do a thing and to watch |<BR>PO. Box 529 Sk?yen | somebody else doing it wrong, without |<BR>0214 Oslo | comment. |<BR>NORWAY | |<BR>Tlf: +47 24 15 38 90 | |<BR>Fax: +47 24 15 38 91 | |<BR>Mobile: +47 909 56 963 | |<BR>------------------------+---------------------------------------------+<BR></DIV><BR>
<p>
<font size="2" color="#0033cc" face="Trebuchet MS"><b>NETsolutions Asia
Limited </b></font>
</p>
<p>
<font size="2" color="#0033cc" face="Trebuchet MS"><b>+66 (2) 237 7247 </b></font>
</p>
<p>
<a href="http://www.nsasia.co.th";><img border="0" alt="NETsolutions Asia Limited" src="cid:RQHFQWYUJULD.44e30700.jpg">
</a> </p>
</BODY></HTML>
- References:
- Slow Query problem
- From: Premsun Choltanwanich
- Re: Slow Query problem
- From: Andreas Joseph Krogh
- Slow Query problem
- Prev by Date: Re: Slow Query problem
- Next by Date: Re: Proposed archival read only trigger on rows - prevent history modification
- Previous by thread: Re: Slow Query problem
- Next by thread: Proposed archival read only trigger on rows - prevent history modification
- Indexes: