SQL QUESTION
Hello,
I have a table which keeps a large number of records that may have the same
info but has a few different values including dates, in various fields
(this is a basic overview). Anyway, I wish to pick out the records with the
highest date in a certain field.
Example TABLE mytable:
pkey
pname
rx
fdate
I want to pick individual records with the highest fdate.
So I write:
SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
FROM mytable a, mytable b
WHERE a.rx=b.rx
GROUP BY a.pkey,a.pname,a.rx
ORDER BY a.pname
Now this seems to work but my database is running out of /tmp space to
perform the query. Is there anything I could change or maybe rewrite more
efficiently as for this operation to work? We are talking about millions of
initial records.
> SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
> FROM mytable a, mytable b
> WHERE a.rx=b.rx
> GROUP BY a.pkey,a.pname,a.rx
> ORDER BY a.pname>
> Now this seems to work but my database is running out of /tmp space to
> perform the query. Is there anything I could change or maybe rewrite more
> efficiently as for this operation to work? We are talking about millions of
> initial records.
You are creating nearly a cartesian product because you use only column
rx in WHERE and not pkey. Only the GROUP BY reduces the number of rows
at the end of the pipe. This take a lot of time, memory and disk space.
I don't exactly understand what you mean, maybe
SELECT a.pkey, a.pname, a.rx, a.fdate
FROM mytable a
WHERE a.fdate = ( select max(b.fdate) from mytable b
WHERE a.pkey = b.pkey )
-- AND a.pkey between 1000111 and 1000222 -- other restrictions
ORDER BY a.pnameThis correlated subselect is expensive, it works faster if you create an
index on (pkey, fdate).
Thomas
Michael J. Prichard wrote:
> Hello,
>
> I have a table which keeps a large number of records that may have the same
> info but has a few different values including dates, in various fields
> (this is a basic overview). Anyway, I wish to pick out the records with the
> highest date in a certain field.
>
> Example TABLE mytable:
>
> pkey
> pname
> rx
> fdate
>
> I want to pick individual records with the highest fdate.
>
> So I write:
>
> SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
> FROM mytable a, mytable b
> WHERE a.rx=b.rx
> GROUP BY a.pkey,a.pname,a.rx
> ORDER BY a.pname>
> Now this seems to work but my database is running out of /tmp space to
> perform the query. Is there anything I could change or maybe rewrite more
> efficiently as for this operation to work? We are talking about millions of
> initial records.
Michael,
You need to define a (or some) temp dbspaces. There have been many
postings to this group regarding temp dbspaces - do a search at Deja:
http://www.deja.com
There is also Informix documentation regarding temp dbspaces -
see the Administrator's Guide.
HTH,
Avi.
--
/\\ \\ /| Avi Abrami, Analyst/Programmer, Telegate Ltd.
/__\\ \\ / | 7 Haplada Street, Or-Yehuda, ISRAEL
/ \\ \\/ | Phone:+972-3-5384717 Fax:+972-3-5335877 eMail:avia@telegate.co.il
In article <38AB8FA4.B0E6BE55@telegate.co.il>, Avi Abrami
<avia@telegate.co.il> writes
>Michael J. Prichard wrote:
>
>> Hello,
>>
>> I have a table which keeps a large number of records that may have the same
>> info but has a few different values including dates, in various fields
>> (this is a basic overview). Anyway, I wish to pick out the records with the
>> highest date in a certain field.
>>
>> Example TABLE mytable:
>>
>> pkey
>> pname
>> rx
>> fdate
>>
>> I want to pick individual records with the highest fdate.
>>
>> So I write:
>>
>> SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
>> FROM mytable a, mytable b
>> WHERE a.rx=b.rx
>> GROUP BY a.pkey,a.pname,a.rx
>> ORDER BY a.pname>>
>> Now this seems to work but my database is running out of /tmp space to
>> perform the query. Is there anything I could change or maybe rewrite more
>> efficiently as for this operation to work? We are talking about millions of
>> initial records.
>
>Michael,
>You need to define a (or some) temp dbspaces. There have been many
>postings to this group regarding temp dbspaces - do a search at Deja:
>
> http://www.deja.com
>
>There is also Informix documentation regarding temp dbspaces -
>see the Administrator's Guide.
>
>HTH,
>Avi.
>
Also look into the
PSORT_NPROCS - number of processors used for sorting
PSORT_DBTEMP - list of dbspaces used for work files when sorting
environment variables.
>--
> /\\ \\ /| Avi Abrami, Analyst/Programmer, Telegate Ltd.
> /__\\ \\ / | 7 Haplada Street, Or-Yehuda, ISRAEL
>/ \\ \\/ | Phone:+972-3-5384717 Fax:+972-3-5335877 eMail:avia@telegate.co.il
>
>
>
--
David Williams
In article <Xt%p4.12339$OJ1.1637297@tw12.nn.bcandid.com>,
"Michael J. Prichard" <chakraboy@mail.excite.com> wrote:
> Hello,
>
> I have a table which keeps a large number of records that may have
> the same info but has a few different values including dates, in
> various fields (this is a basic overview). Anyway, I wish to pick
> out the records with the highest date in a certain field.
>
> Example TABLE mytable:
>
> pkey
> pname
> rx
> fdate
>
> I want to pick individual records with the highest fdate.
>
> So I write:
>
> SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
> FROM mytable a, mytable b
> WHERE a.rx=b.rx
> GROUP BY a.pkey,a.pname,a.rx
> ORDER BY a.pname>
> Now this seems to work but my database is running out of /tmp space
> to perform the query. Is there anything I could change or maybe
> rewrite more efficiently as for this operation to work? We are
> talking about millions of initial records.
Michael,
I don't know why you neede to run a self join. I notice that Thomas
suggested a correlated subquery as an alternative to your method of
filtering a cartesian product. I have a different suggestion:
I assume the columns pkey, pname, and rx are the columns that may me
identical between two rows with different dates. (This is questionable
on it own - if pkey is a primary key column, it shoyld be unique
anyway. But I digress...)
select pkey, pname, rx, max(fdate)
from mytable
group by pkey, pname, rx
order by mytable
The group by will still use temp space to sort but not nearly as much.
And for all you know, your temp-dbspace(s) may be too small anyway.
HTH
--
+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <Xt%p4.12339$OJ1.1637297@tw12.nn.bcandid.com>,
"Michael J. Prichard" <chakraboy@mail.excite.com> wrote:
> Hello,
>
> I have a table which keeps a large number of records that may have
> the same info but has a few different values including dates, in
> various fields (this is a basic overview). Anyway, I wish to pick
> out the records with the highest date in a certain field.
>
> Example TABLE mytable:
>
> pkey
> pname
> rx
> fdate
>
> I want to pick individual records with the highest fdate.
>
> So I write:
>
> SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
> FROM mytable a, mytable b
> WHERE a.rx=b.rx
> GROUP BY a.pkey,a.pname,a.rx
> ORDER BY a.pname>
> Now this seems to work but my database is running out of /tmp space
> to perform the query. Is there anything I could change or maybe
> rewrite more efficiently as for this operation to work? We are
> talking about millions of initial records.
Michael,
I don't know why you neede to run a self join. I notice that Thomas
suggested a correlated subquery as an alternative to your method of
filtering a cartesian product. I have a different suggestion:
I assume the columns pkey, pname, and rx are the columns that may me
identical between two rows with different dates. (This is questionable
on it own - if pkey is a primary key column, it shoyld be unique
anyway. But I digress...)
select pkey, pname, rx, max(fdate)
from mytable
group by pkey, pname, rx
order by mytable
The group by will still use temp space to sort but not nearly as much.
And for all you know, your temp-dbspace(s) may be too small anyway.
HTH
--
+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Before you buy.