How to Full RushMore Optimize this SQL statement...

[andreateh]

tableA
Index tagA : loc + row

tableB
Index tagB : ali + abu

SELECT  a.slipno,a.procod,a.lotnum,;
        RECNO() as rec_no,;
        a.curcod, b.abu FROM tableA a, tableB b;
        INTO CURSOR tmplot READWRITE;
	WHERE loc + row =;
        cLoc AND ali + abu =;
        c_sting

with SYS(3054,11) i get result below :

Using index tag TAGA to rushmore optimize table tableA
Rushmore optimization level for table tableA: full
Using index tag TAGB to rushmore optimize table tableB
Rushmore optimization level for table tableB: full

All table are fully optimize
but, with code below

SELECT  a.slipno,a.procod,a.lotnum,;
        RECNO() as rec_no,;
        a.curcod, b.abu FROM tableA a, tableB b;
        INTO CURSOR tmplot READWRITE;
	WHERE loc+row =;
        cLoc AND ali + abu =;
        cString + tableA.abu &&<---the change

the result become :


Using index tag TAGA to rushmore optimize table tableA
Rushmore optimization level for table tableA: full
Rushmore optimization level for table tableB: None

What should i do to make the second SQL statement become fully rushmore optimize ?

 

[craigber]

what happens if you make the last condition

cString + tableA.abu = ali + abu


One major problem is that you cannot rely on RECNO(). This is documented in the help file.



Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[andreateh]

If i do :
cString + tableA.abu = ali + abu

Using index tag TAGA to rushmore optimize table tableA
Rushmore optimization level for table tableA: partial
Rushmore optimization level for table tableB: None


One major problem is that you cannot rely on RECNO(). This is documented in the help file

Thanks for info above.

 

[craigber]

What are the index tags and keys for table A?

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[andreateh]

Craigber,

tableA
Index tagA : loc + row

tableB
Index tagB : ali + abu

 

[craigsboyd]

andreateh,

There is alot that you aren't really telling us. I mean, let's say that I wanted to reproduce what you are seeing, I just can't given the information you have provided. Your SQL from what I can see should blow up on the "abu" in the where clause cause you haven't qualified it as belonging to tableA or TableB. Also, it appears that your SQL would result in a Cartesian product of the respective tables... so you need to provide more information if you want a good answer to this question. Just to show you what I mean, here's what I can come up with from what you have told us so far.

CREATE CURSOR tableA (slipno I, procod I, lotnum I, curcod c(30), loc c(10), row c(10), abu c(10))
INSERT INTO tableA (slipno, procod, lotnum, curcod, loc, row, abu) ;
	VALUES (1, 1, 1, "TESTING", "ABCD", "EFGH", "IJKLM")
INDEX ON loc+row TO tmpaidx

CREATE CURSOR tableB (ali c(10), abu c(10))
INSERT INTO tableB (ali, abu) ;
	VALUES ("NOPQR", "IJKLM")
INDEX ON ali+abu TO tmpbidx
cLoc = "ABCD      EFGH      "
cstring = "NOPQR     "

SYS(3054,11)
SELECT  a.slipno,a.procod,a.lotnum,;
        RECNO() as rec_no,;
        a.curcod, b.abu FROM tableA a, tableB b;
       INTO CURSOR tmplot READWRITE;
    WHERE loc+row = cLoc AND ali + [COLOR=red]tableB[/color].abu =;
        cString + tableA.abu
SYS(3054,0)

...if you run the code above you can see that it isn't even close to what you say your results are. So anyways, take some time and modify the code above so it matches your situation and then we'll be able to perhaps be more effective in helping you.

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[craigber]

The query must EXACTLY match the index tag. See

http://www.craigberntson.com/Articles/kb004.htm

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[andreateh]

Thanks for your advice craigsboyd. Next time i'll try to descibe my question in more detail.

Actually the code u show above is just exactly like my case. the only different is

CREATE CURSOR tableA (slipno I, procod I, lotnum I, curcod c(30), loc c(10), row c(10), abu c(10))

INSERT INTO tableA (slipno, procod, lotnum, curcod, loc, row, abu) ;
VALUES (1, 1, 1, "TESTING", "ABCD", "EFGH", "IJKLM")

INDEX ON loc+row TO tmpaidx

CREATE CURSOR tableB (ali c(10), abu c(10))

INSERT INTO tableB (ali, abu) ;
    VALUES ("NOPQR", "IJKLM")

INDEX ON ali+abu TO tmpbidx

cLoc = "ABCD      EFGH      "
cstring = "NOPQR     "


SYS(3054,11)
SELECT  a.slipno,a.procod,a.lotnum,;
        RECNO() as rec_no,;
        a.curcod, b.abu FROM tableA a, tableB b;
        INTO CURSOR tmplot READWRITE;
        WHERE loc+row = cLoc AND;
       [red] tableB.ali + tableB.abu [/red]=;
        cString + tableA.abu
SYS(3054,0)

cString is a variable that take from the system's default value.