Re: [SQL] Query tuning
Hi kapil, Here you have specified 3 tables, does JOB_TYPE_FIRST and JOB_TYPE_SECOND both contain all the JOBID in the third table? Maybe i can help you if you elaborate your problem a bit more. Regards, Moiz Kothari On Thu, May 15, 2008 at 11:30 AM, <[EMAIL PROTECTED]> wrote: > Hi, > > > > > > I have a query which is run across 3 tables JOB_TYPE_FIRST, JOB_TYPE_SECOND > and JOB_ALLOCATION_WORKLIST. > > > > The column JOBID is referenced in JOB_ALLOCATION_WORKLIST table and primary > key in both JOB_TYPE_FIRST, JOB_TYPE_SECOND tables. > > > > There is one more column BOOK_ID which is supplied as the binding parameter > to the query. The query looks like: > > > > select count(distinct(j.JOBID)) as jobCount > > from JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j > > where (( a.JOBID = j.JOBID) > > and (a.BOOK_ID = :bookId)) > > or ((b.JOBID = j.JOBID) > > and (b.BOOK_ID = :bookId)); > > > > As the records in the database are too large it is having huge cost and > stalling the server and takes lot of time. > > > > Can anyone suggest a better way to fetch the results for the query or tune > it? Any help would be highly appreciated. > > > > > > Thanks & Regards, > > Kapil > > * Please do not print this email unless it is absolutely necessary. * > > The information contained in this electronic message and any attachments to > this message are intended for the exclusive use of the addressee(s) and may > contain proprietary, confidential or privileged information. If you are not > the intended recipient, you should not disseminate, distribute or copy this > e-mail. Please notify the sender immediately and destroy all copies of this > message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient > should check this email and any attachments for the presence of viruses. The > company accepts no liability for any damage caused by any virus transmitted > by this email. > > www.wipro.com > -- Hobby Site : http://dailyhealthtips.blogspot.com
[SQL] Find all instances of a column in the entire database.
Hi Everyone, I am hoping that you might be able to give me some assistance with the following task! I have a database with nearly 200 tables and I need to find all tables that contain a column of myColumnName. I was hoping there might be a built-in function for this task, but I have been unable to find any information through our good friend Mr. Google or by perusing the fine manual. Thanks in advance for any thoughts you might have. - Beau -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find all instances of a column in the entire database.
On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote: > I am hoping that you might be able to give me some assistance with the > following task! > I have a database with nearly 200 tables and I need to find all tables > that contain a column of myColumnName. select * from information_schema.columns where column_name = 'myColumnName'; depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find all instances of a column in the entire database.
Hi depesz, Thanks very much! That works wonderfully well... Is this listed in the manual anywhere? because after two hours of reading, I didn't find it anywhere! None the less - thanks again. - Beau On 16/05/2008, at 11:56 PM, hubert depesz lubaczewski wrote: On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote: I am hoping that you might be able to give me some assistance with the following task! I have a database with nearly 200 tables and I need to find all tables that contain a column of myColumnName. select * from information_schema.columns where column_name = 'myColumnName'; depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find all instances of a column in the entire database.
am Sat, dem 17.05.2008, um 0:04:05 +1000 mailte Gavin 'Beau' Baumanis folgendes: > Hi depesz, > > Thanks very much! > That works wonderfully well... > > Is this listed in the manual anywhere? because after two hours of > reading, I didn't find it anywhere! Of course, the whole information schema: http://www.postgresql.org/docs/current/static/information-schema.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query tuning
On Thu, May 15, 2008 at 12:00 AM, <[EMAIL PROTECTED]> wrote: > Hi, > > select count(distinct(j.JOBID)) as jobCount > from JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j > where (( a.JOBID = j.JOBID) > and (a.BOOK_ID = :bookId)) > or ((b.JOBID = j.JOBID) > and (b.BOOK_ID = :bookId)); Have you tried joining a with j and b with j separately, and then doing a union on those two sets? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query tuning
On Fri, 2008-05-16 at 09:21 -0600, Scott Marlowe wrote: > On Thu, May 15, 2008 at 12:00 AM, <[EMAIL PROTECTED]> wrote: > > Hi, > > > > select count(distinct(j.JOBID)) as jobCount > > from JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j > > where (( a.JOBID = j.JOBID) > > and (a.BOOK_ID = :bookId)) > > or ((b.JOBID = j.JOBID) > > and (b.BOOK_ID = :bookId)); > > Have you tried joining a with j and b with j separately, and then > doing a union on those two sets? That seems the only way to get a sane answer to this query, which is otherwise an unconstrained join on both sides of the OR. Great example of a query which runs slow because the question is phrased incorrectly. Count(distinct) is pretty much the only function that will give the same answer as a correctly phrased query. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find all instances of a column in the entire database.
[EMAIL PROTECTED] ("Gavin 'Beau' Baumanis") writes:
> I am hoping that you might be able to give me some assistance with the
> following task!
> I have a database with nearly 200 tables and I need to find all tables
> that contain a column of myColumnName.
>
> I was hoping there might be a built-in function for this task, but I
> have been unable to find any information through our good friend Mr.
> Google or by perusing the fine manual.
>
> Thanks in advance for any thoughts you might have.
I have a schema which has a number of attributes called "name_id":
metadata=# select n.nspname, r.relname from pg_class r, pg_namespace n,
pg_attribute a where a.attname = 'name_id' and r.oid = attrelid and n.oid =
relnamespace;
nspname | relname
--+-
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
registry | redacted
(23 rows)
Change 'name_id' to 'myColumnName' and you should get what you're
expecting...
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/lsf.html
Signs of a Klingon Programmer - 17. "Klingon multitasking systems do
not support "time-sharing". When a Klingon program wants to run, it
challenges the scheduler in hand-to-hand combat and owns the machine."
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
