From: Date: February 20 2003 3:32pm
Subject: Re: Space for results table exhausted
List-Archive: http://lists.mysql.com/maxdb/12585
Message-Id: <b32ot3$g7i$1@main.gmane.org>

Peter,
Thanks for the reply. First, I made a mistake in my example in not including
a field from the tblAOType in the output that would've shown a need to use
that table. Aside from that, I had already tried what you suggested before
writing with no change in the results. Here's a better example of the
offending query with the most restricting condition first. (Using the
Explain command renders that same result as before).

SELECT
"OCPC"."TBLAOTYPE"."AOTYPE","OCPC"."TBLARCHOWNER"."NAME","OCPC"."TBLPROJAO".
"PROJAO","OCPC"."TBLPROJAO"."SHOWONRESULTS","OCPC"."TBLARCHOWNER"."PHONE","O
CPC"."TBLARCHOWNER"."ZIP","OCPC"."TBLARCHOWNER"."STATE","OCPC"."TBLARCHOWNER
"."CITY","OCPC"."TBLARCHOWNER"."ADDR1"

FROM "OCPC"."TBLAOTYPE","OCPC"."TBLARCHOWNER","OCPC"."TBLPROJAO"

WHERE (("OCPC"."TBLPROJAO"."PROJID" = 8329) AND
("OCPC"."TBLPROJAO"."SHOWONRESULTS" = TRUE)) AND "OCPC"."TBLARCHOWNER"."ID"=
"OCPC"."TBLPROJAO"."ARCHOWNERID" (+) AND "OCPC"."TBLAOTYPE"."AOTYPEID"=
"OCPC"."TBLPROJAO"."AOTYPEID" (+)

ORDER BY "OCPC"."TBLPROJAO"."PROJAO" ASC

This is just one example of a query I build on the fly in Visual Basic code.
Building views seems unreasonable and it doesn't seem to me that there are
THAT many records involved here. But if I'm mistaken then by all means
somebody straighten me out. :)

Thanks,
Jon



"Peter Willadt" <Willadt@stripped> wrote in message
news:200302200643.24717.willadt@stripped...
> Hello,
> On Mittwoch, 19. Februar 2003 19:20, JON wrote:
> >
> >  I have the following query created with the visual query tool:
> > SELECT
> >
"OCPC"."TBLARCHOWNER"."NAME","OCPC"."TBLPROJAO"."SHOWONRESULTS","OCPC"."TBL
> >A
> >
RCHOWNER"."PHONE","OCPC"."TBLARCHOWNER"."ZIP","OCPC"."TBLARCHOWNER"."STATE"
> >, "OCPC"."TBLARCHOWNER"."CITY","OCPC"."TBLARCHOWNER"."ADDR1" FROM
> > "OCPC"."TBLAOTYPE","OCPC"."TBLARCHOWNER","OCPC"."TBLPROJAO" WHERE
> > "OCPC"."TBLARCHOWNER"."ID"= "OCPC"."TBLPROJAO"."ARCHOWNERID" (+) AND
> > "OCPC"."TBLAOTYPE"."AOTYPEID"= "OCPC"."TBLPROJAO"."AOTYPEID" (+) AND
> > ((tblProjAO.ShowOnResults)=TRUE) and tblProjAO.ProjID = 8329 order by
> > ProjAO When I execute the query I get the Space for results table
exhausted
> > error. I'm using  kernal 7.3.0,  SQL Studio 7.4.3.6, ODBC driver
> > 7.04.03.00.
> >
> exhausting space for results mostly comes from building cartesian
products.
> But in this case, it doesn't look like that.
> Generally, it might be goos to put the most restricting conditions first
(in
> this case the last both) or to build a view resulting on these conditions
and
> have the query use this view instead of the underlying base table(s).
>
> In this particular case I wonder what reason there is to include TBLAOTYPE
in
> the query. It seems not to restirct the result set nor to appear in the
> output. I also wonder where the ProjAO in the order by clause comes from.
>
> Perhaps you might try rewriting your query like this and try again.
>
> SELECT
> TBLARCHOWNER.NAME,TBLPROJAO.SHOWONRESULTS,TBLARCHOWNER.PHONE,
> TBLARCHOWNER.ZIP,TBLARCHOWNER.STATE,
> TBLARCHOWNER.CITY,TBLARCHOWNER.ADDR1 FROM
> TBLARCHOWNER,
> TBLPROJAO
> WHERE tblProjAO.ShowOnResults=TRUE
> and tblProjAO.ProjID = 8329
> and TBLARCHOWNER.ID= TBLPROJAO.ARCHOWNERID (+)
> order by ProjAO
>
> Peter Willadt