SQL question

I have 2 tables with same structure:
BIGTABLE ( WORD C(10) ) && 10,0000 RECORDS
SMALLTABLE ( WORD C(10) ) && 2 RECORDS
both are indexed on WORD.

SMALLTABLE has 2 recs:
"CAT"
"DOG"

BIGTABLE has numerous 'words' including those found in SMALLTABLE

the following in fully optmized:
SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ("CAT","DOG")

the folloinw is not optmized at all:
SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ( SELECT WORD
FROM SMALLTABLE )

Why is the 2nd example not optmized? And I'm am missing in the 2nd
example to make it optmized? I need to use SMALLTABLE in table form
rather than in sting form.

THANK YOU
Mike Farnesi

Stuart
Mon May 17 18:50:18 CDT 2004

Mike Farnesi wrote:

> I have 2 tables with same structure:
> BIGTABLE ( WORD C(10) ) && 10,0000 RECORDS
> SMALLTABLE ( WORD C(10) ) && 2 RECORDS
> both are indexed on WORD.
>
> SMALLTABLE has 2 recs:
> "CAT"
> "DOG"
>
> BIGTABLE has numerous 'words' including those found in SMALLTABLE
>
> the following in fully optmized:
> SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ("CAT","DOG")
>
> the folloinw is not optmized at all:
> SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ( SELECT WORD
> FROM SMALLTABLE )
>
> Why is the 2nd example not optmized? And I'm am missing in the 2nd
> example to make it optmized? I need to use SMALLTABLE in table form
> rather than in sting form.
>
> THANK YOU
> Mike Farnesi

Hi Mike

Why not SELECT BIGTABLE.WORD FROM BIGTABLE JOIN SMALLTABLE ON
BIGTABLE.WORD = SMALLTABLE.WORD INTO CURSOR TEMP?

Or, for VFP6 and earlier:
SELECT BIGTABLE.WORD FROM BIGTABLE, SMALLTABLE WHERE BIGTABLE.WORD =
SMALLTABLE.WORD INTO CURSOR TEMP

Regards

Stuart Dunkeld

Gene
Mon May 17 19:20:43 CDT 2004

mfarnesi@hotmail.com (Mike Farnesi) wrote:

[snip]

>Why is the 2nd example not optmized? And I'm am missing in the 2nd
>example to make it optmized? I need to use SMALLTABLE in table form
>rather than in sting form.

Do you have an index on deleted()? AFAIK, this is needed to get
a report of full optimisation. Bear in mind that in some cases, an
index on deleted() can actually result in slower results.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Holger
Tue May 18 02:43:40 CDT 2004

Hi,

> the following in fully optmized:
> SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ("CAT","DOG")
>
> the folloinw is not optmized at all:
> SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ( SELECT WORD
> FROM SMALLTABLE )
>
> Why is the 2nd example not optmized?

your 2nd example is not optimized because the subselect has no optimizable
WHERE condition.

But don't worry about the "not optimized" query.
It doesn't say anything about it's speed.
Sometimes optimized queries can be slower than non optimized queries.

--
Holger Vorberg
MS Visual FoxPro MVP, Germany

Anders
Tue May 18 04:59:05 CDT 2004

Hi Stuart
JOIN was introduced in VFP5 wasn't it? If no it was in VFP6,

-Anders

"Stuart Dunkeld" <user@example.net> wrote in message
news:15dqc.1117$kx1.848@newsfe5-gui.server.ntli.net...
> Mike Farnesi wrote:
>
> > I have 2 tables with same structure:
> > BIGTABLE ( WORD C(10) ) && 10,0000 RECORDS
> > SMALLTABLE ( WORD C(10) ) && 2 RECORDS
> > both are indexed on WORD.
> >
> > SMALLTABLE has 2 recs:
> > "CAT"
> > "DOG"
> >
> > BIGTABLE has numerous 'words' including those found in SMALLTABLE
> >
> > the following in fully optmized:
> > SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ("CAT","DOG")
> >
> > the folloinw is not optmized at all:
> > SELECT WORD FROM BIGTABLE INTO CURSOR TEMP WHERE WORD IN ( SELECT WORD
> > FROM SMALLTABLE )
> >
> > Why is the 2nd example not optmized? And I'm am missing in the 2nd
> > example to make it optmized? I need to use SMALLTABLE in table form
> > rather than in sting form.
> >
> > THANK YOU
> > Mike Farnesi
>
> Hi Mike
>
> Why not SELECT BIGTABLE.WORD FROM BIGTABLE JOIN SMALLTABLE ON
> BIGTABLE.WORD = SMALLTABLE.WORD INTO CURSOR TEMP?
>
> Or, for VFP6 and earlier:
> SELECT BIGTABLE.WORD FROM BIGTABLE, SMALLTABLE WHERE BIGTABLE.WORD =
> SMALLTABLE.WORD INTO CURSOR TEMP
>
> Regards
>
> Stuart Dunkeld
>

Trey
Tue May 18 10:24:53 CDT 2004

it's definitely in 6.

iirc, it's been in vfp since select-sql - i remember at some point thinking
it was odd that vfp had left joins but sql 6.0 still used *= syntax... [but
we could have been on vfp5 by then]

"Anders Altberg" <x_pragma@telia.com> wrote in message
news:uGUZTsMPEHA.3328@TK2MSFTNGP09.phx.gbl...
> Hi Stuart
> JOIN was introduced in VFP5 wasn't it? If no it was in VFP6,
>
> -Anders
>