Re: [SQL] How to find entries missing in 2nd table?
> > SELECT controller_id FROM control > > WHERE controller_id NOT IN > > (SELECT DISTINCT controller_id FROM datapack); > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a > huge performance problem. Is that true on PostgreSQL also? >From my experience, it does not preform as well as the standard group by >clause. I noticed a ~20% increase in query run times. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to find entries missing in 2nd table?
On 7/13/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > SELECT controller_id FROM control> > WHERE controller_id NOT IN> > (SELECT DISTINCT controller_id FROM datapack);> The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a > huge performance problem. Is that true on PostgreSQL also?From my experience, it does not preform as well as the standard group by clause. I noticed a ~20%increase in query run times. So in that case this would be better:SELECT controller_id FROM controlWHERE controller_id NOT IN(SELECT controller_id FROM datapack); or SELECT controller_id FROM controlWHERE controller_id NOT IN(SELECT controller_id FROM datapack GROUP BY controller_id); Guess you need to do some explain plans to see which would be best. Good luck! == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] How to find entries missing in 2nd table?
On 7/12/06, Exner, Peter <[EMAIL PROTECTED]> wrote: Hi,what aboutSELECT controller_id FROM controlWHERE controller_id NOT IN(SELECT DISTINCT controller_id FROM datapack);The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a huge performance problem. Is that true on PostgreSQL also? == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] How to find entries missing in 2nd table?
> > > > SELECT controller_id FROM control > > > > WHERE controller_id NOT IN > > > > (SELECT DISTINCT controller_id FROM datapack); > > > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT > > is a > > > huge performance problem. Is that true on PostgreSQL also? > > > > From my experience, it does not preform as well as the standard group by > > clause. I noticed a ~20% > > increase in query run times. > > > > So in that case this would be better: > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT controller_id FROM datapack); > > or > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT controller_id FROM datapack GROUP BY controller_id); Well in this case, the group by or distinct is simple not needed for the query to preform correctly. The additional group by clause in the second query could cause it to preform additional processing which "may" cause it to preform slower. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Trigger, record "old" is not assigned yet
Hi, I’ve created a trigger BEFORE INSERT OR UPDATE on a table and, indeed, when the trigger is raised before insertion the record “old” is not assigned. Is there a way to distinguish in the trigger procedure from an insert statement to an update statement? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
Re: [SQL] Trigger, record "old" is not assigned yet
For plpgsql use TG_OP. See link below. http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html On Thursday 13 July 2006 03:50 pm, Daniel Caune wrote: > Hi, > > > > I've created a trigger BEFORE INSERT OR UPDATE on a table and, indeed, > when the trigger is raised before insertion the record "old" is not > assigned. Is there a way to distinguish in the trigger procedure from > an insert statement to an update statement? > > > > Regards, > > > > > > -- > > Daniel CAUNE > > Ubisoft Online Technology > > (514) 490 2040 ext. 3613 -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Trigger, record "old" is not assigned yet
On 7/13/06, Adrian Klaver <[EMAIL PROTECTED]> wrote: For plpgsql use TG_OP. See link below.http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.htmlOn Thursday 13 July 2006 03:50 pm, Daniel Caune wrote: > Hi, I've created a trigger BEFORE INSERT OR UPDATE on a table and, indeed,> when the trigger is raised before insertion the record "old" is not> assigned. Is there a way to distinguish in the trigger procedure from > an insert statement to an update statement? Regards,>>> Daniel CAUNE>> Ubisoft Online Technology>> (514) 490 2040 ext. 3613 --Adrian Klaver[EMAIL PROTECTED]Or to be more specific:IF (TG_OP = 'UPDATE') THEN== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
[SQL] Can I do this smarter?
I have three tables: customers, salesorders and invoices. Customers have salesorders and salesorders have invoices ( child tables have foreign key columns to their parent). I want to get a list of all invoices with their customers. This what I came up with: select invoices.objectid, invoices.invoicenumber, invoices.invoicedate, (select customer from salesorders where objectid = invoices.salesorderobjectid), (select customernumber from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid)), (select lastname from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid)) from invoices Can I do this smarter as the three subselects select the same customer three times and I would think that 1 time is enough? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Can I do this smarter?
On 7/13/06, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: I have three tables: customers, salesorders and invoices. Customers havesalesorders and salesorders have invoices ( child tables have foreignkey columns to their parent).I want to get a list of all invoices with their customers. This what I came up with:selectinvoices.objectid,invoices.invoicenumber,invoices.invoicedate,(select customer from salesorders where objectid = invoices.salesorderobjectid),(select customernumber from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid)),(select lastname from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid))from invoicesCan I do this smarter as the three subselects select the same customer three times and I would think that 1 time is enough? SELECT invoices.objectid, invoices.invoicenumber, invoices.invoicedate, salesorders.customer, customers.customernumber, customers.lastnameFROM invoices INNER JOIN salesorders ON ( salesorders.objectid = invoices.salesorderobjectid)INNER JOIN customers ON ( customers.objectid = salesorder.customer)You should do INNER and OUTER joins for connecting the tables by their foreign keys. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Can I do this smarter?
Hi Aaron, On Thu, 2006-07-13 at 22:52 -0500, Aaron Bono wrote: > SELECT >invoices.objectid, >invoices.invoicenumber, >invoices.invoicedate, >salesorders.customer, >customers.customernumber, >customers.lastname > FROM invoices > INNER JOIN salesorders ON ( >salesorders.objectid = invoices.salesorderobjectid > ) > INNER JOIN customers ON ( >customers.objectid = salesorder.customer > ) > > You should do INNER and OUTER joins for connecting the tables by > their foreign keys. Thanks for the quick (and working ;-)) response. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
