How can I make this SQL statement run faster? 1

[EzLogic]

VFP 9 SP2
Server 2003
4GB Ram
Local Disk
Dual Xeon 3Ghz

FMRComMaster has 10 records
IID I AutoInc indexed
ComDate D Index
plus few more fields

FMRCom has 2,137,895 records (2 mil)
IID I AutoInc Indexed
SIM C(20) Indexed
CustNo C(10) Indexed
fk_FMRComMast I Indexed
plus other fields

ARCust has about 4000 records
CustNo C(10) Indexed
plus other fields


Query 1 takes .35 seconds on local machine.. about 3-5 seconds on Network station over 1GB LAN

close DATABASES all 
clear ALL 
CD d:\ezcellerp\data

USE fmrcom in 0 
USE fmrcommaster in 0 

lnSec = SECONDS()
ldStart = CTOD('01/01/2012')
ldEnd = CTOD("01/01/2012")

SELECT ;
			FMRCom.Sim,;
			FMRCom.PIN,;
			FMRCom.Account,;
			FMRCom.MSISDN,;
			FMRCom.ActDate,;
			FMRCom.STATUS,;
			FMRCom.UsedDate,;
			FMRCom.VALUE AS CardValue,;
			FMRCom.CustNo,;
			ARCust.company,;
			ARCust.Repname,;
			FMRCom.Royalty,;
			FMRCom.PayAmount,;
			FMRComMaster.ComDate AS ComDate;
			FROM FMRCom ;
				LEFT OUTER JOIN FMRComMaster ;
			ON FMRCom.fk_FMRComMast = FMRComMaster.IID ;
				LEFT OUTER JOIN ARCust ;
			ON FMRCom.CustNo = ARCust.CustNo ;
				WHERE fk_FMRComMast = 38 ;
			ORDER BY FMRCom.CustNo,FMRCom.UsedDate ;
			into cursor curFMRDetail

messagebox(SECONDS() - lnSec)

Query 2 takes about 4 seconds on local machine, and 30+ seconds on network station (same station)...

close DATABASES all 
clear ALL 
CD d:\ezcellerp\data

USE fmrcom in 0 
USE fmrcommaster in 0 

lnSec = SECONDS()
ldStart = CTOD('01/01/2012')
ldEnd = CTOD("01/01/2012")

SELECT ;
			FMRCom.Sim,;
			FMRCom.PIN,;
			FMRCom.Account,;
			FMRCom.MSISDN,;
			FMRCom.ActDate,;
			FMRCom.STATUS,;
			FMRCom.UsedDate,;
			FMRCom.VALUE AS CardValue,;
			FMRCom.CustNo,;
			ARCust.company,;
			ARCust.Repname,;
			FMRCom.Royalty,;
			FMRCom.PayAmount,;
			FMRComMaster.ComDate AS ComDate;
			FROM FMRCom ;
				LEFT OUTER JOIN FMRComMaster ;
			ON FMRCom.fk_FMRComMast = FMRComMaster.IID ;
				LEFT OUTER JOIN ARCust ;
			ON FMRCom.CustNo = ARCust.CustNo ;
				WHERE BETWEEN(FMRComMaster.ComDate,ldStart,ldEnd) ;
			AND FMRCom.PayAmount <> 0 ;
			ORDER BY FMRCom.CustNo,FMRCom.UsedDate ;
			into cursor curFMRDetail

messagebox(SECONDS() - lnSec)

I need to be able to pull date ranges and when i switched to Query 2, its crawling now..

is there any way i can make it faster?



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[foxuser]

try these two memory setting , see if it helps.

SYS(3050,1,32000000) && use memory
SYS(3050,2,32000000) && use memory

 

[Olaf Doschke]

Not sure, if BETWEEN(FMRComMaster.ComDate,ldStart,ldEnd) is optimisable, I'd use sql syntax: (FRMComMaster.ComDate Between m.ldStart And m.ldEnd)

Check SYS(3054) to see what indexes are really used.

Bye, Olaf.

 

[EzLogic]

I tried both and didn't see any improvements.

here is the result when i do the sys(3054,1)

Rushmore optimization level for table fmrcom: none
Using index tag Comdate to rushmore optimize table fmrcommaster
Rushmore optimization level for table fmrcommaster: partial
Rushmore optimization level for table arcust: none

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

At least the comdate index is used, how good that optimises the query time depends on how restrictive the date range is.

As a side note: Applying a where clause on a left joined table makes the join an inner join, as you take records off the result, which have no match outside the date range, so you also refuse records without any joined FMRComMaster. In the end this would actually make the query faster, but maybe not with the full expected resultset.

It may be better to turn the join order around and join from the short table FMRComMaster towards the large table FMRCom instead, wouldn't that give the same result?

SELECT ;
            FMRCom.Sim,;
            FMRCom.PIN,;
            FMRCom.Account,;
            FMRCom.MSISDN,;
            FMRCom.ActDate,;
            FMRCom.STATUS,;
            FMRCom.UsedDate,;
            FMRCom.VALUE AS CardValue,;
            FMRCom.CustNo,;
            ARCust.company,;
            ARCust.Repname,;
            FMRCom.Royalty,;
            FMRCom.PayAmount,;
            FMRComMaster.ComDate AS ComDate;
            FROM FMRComMaster ;
                INNER JOIN FMRCom;
            ON FMRCom.fk_FMRComMast = FMRComMaster.IID ;
                LEFT OUTER JOIN ARCust ;
            ON FMRCom.CustNo = ARCust.CustNo ;
            WHERE (FMRComMaster.ComDate Between ldStart And ldEnd) ;
            AND FMRCom.PayAmount <> 0 ;
            ORDER BY FMRCom.CustNo,FMRCom.UsedDate ;
            into cursor curFMRDetail

Bye, Olaf.

 

[Olaf Doschke]

Is there a large amount of FRMCom records with PayAmount=0? Then indexing on PayAmount would also help, simply. You may also put FMRCom.PayAmount<>0 within the join condition.

Bye, Olaf.

 

[EzLogic]

wow, the Inner Join gave the same result and it was blazing fast.

it was 0.35 seconds.

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

I bet with the turned around order a left join is as fast, it's linking from few records to a large amount, which is faster than the other way around, even more so, if the low amount of records is filtered.

Bye, Olaf.

 

[TamarGranor]

The order of the fields in the ON clause is irrelevant. The optimizer figures out which table has useful indexes. That is:

           FROM FMRComMaster ;
                INNER JOIN FMRCom;
            ON FMRCom.fk_FMRComMast = FMRComMaster.IID ;

should be identical to:

           FROM FMRComMaster ;
                INNER JOIN FMRCom;
            ON FMRComMaster.IID = FMRCom.fk_FMRComMast ;

and to:

           FROM FMRCom ;
                INNER JOIN FMRComMaster ;
            ON FMRComMaster.IID = FMRCom.fk_FMRComMast ;

and to:

           FROM FMRCom ;
                INNER JOIN FMRComMaster ;
            ON FMRCom.fk_FMRComMast = FMRComMaster.IID ;

Identical may be too strong, since the optimizer does have to do some work, but they should all take essentially the same time.

Similarly, when you only have inner joins, the order you list the joins doesn't matter; the optimizer figures out the best order to join.

However, once there are outer joins, you have restrictions on the order the joins can be performed in, so the order you arrange the query matters more.

Tamar

 

[Olaf Doschke]

Tamar, I was not talking about the order of fields in the ON clause, reread please and look what I turned around in the SQL.

Bye, Olaf.

 

[TamarGranor]

If I'm not misunderstanding what you wrote, you were reversing the order of the tables in the list of joins. For inner joins, that doesn't make a difference either; the optimizer figures out which way to go.

I wanted to give you an example using data we both have, but unfortunately both Northwind and Tastrade use the same names for PKs and FKs in parent and child tables, so it would hard to demonstrate with that data.

Look at the SYS(3054,11) results when you try it both ways, where you have field names that make it easy to tell which table's tag is being used for the join.

Tamar

 

[Olaf Doschke]

But it wasn't an inner join, it was a left join.

Bye, Olaf.

 

[Olaf Doschke]

It would have been enough to change from left to inner join. If you mean that, OK. Got me.

Bye, Olaf.

 

[TamarGranor]

Looks like I misunderstood what you were saying originally. Sorry.

Tamar