Improving SQL Sub-Query Performance

[erp84]

Is it possible to optimize the code below? Would I be better of creating multiple cursors and joining the cursors?

Currently I'm just filling a grid with the result of this query. It takes about 13-15 seconds to execute, which really isn't long but would like to reduce this as much as possible. Appreciate any direction!

SELECT T1.JOB, T1.LABOR, ROUND(T2.PURCHASES,2), T3.MAKE 

FROM	(SELECT JOB.JOB, SUM(LABOR.HOURS*LABOR.RATE) AS Labor FROM JOB LEFT OUTER JOIN LABOR ON JOB.JOB=LABOR.JOB WHERE JOB.STATUS <='4' GROUP BY JOB.JOB) T1 

LEFT JOIN	(SELECT JOB.JOB,  SUM(COST.PRICE*COST.QTY) AS PURCHASES FROM JOB LEFT OUTER JOIN COST on JOB.JOB=COST.JOB WHERE  JOB.STATUS<='4' GROUP BY JOB.JOB) T2 

ON T1.JOB=T2.JOB

LEFT JOIN	(SELECT JOB.JOB, SUM(REQ.QTY*PN.COST) as MAKE FROM ((JOB INNER JOIN WORDER ON JOB.JOB=WORDER.JOB) INNER JOIN REQ ON WORDER.WORDER=REQ.WORDER) INNER JOIN PN ON REQ.PN=PN.PN WHERE JOB.STATUS<='4' GROUP BY JOB.JOB) T3 

ON T1.JOB=T3.JOB

INTO CURSOR TEST READWRITE

 

[Mike Lewis]

Is it possible to optimize the code below?

Yes, just as you can with any query. The best way to optimise a query is to make sure you have the correct indexes in place.

In general, indexes are desirable on fields that take part in JOIN and WHERE clauses. In this case, you should look to see if there are indexes on fields such as JOB.JOB, WORDER.JOB, WORDER.WORDER, REQ.WORDER, and so on. If not, consider creating them.

Note that, while creating indexes is usually the best way to optimise the query, it might detract from the overall performance of the application. In general, the correct indexes can speed up queries, but they can slow down table updating.

Would I be better of creating multiple cursors and joining the cursors?

It's virtually impossible to predict the answer to that question. Intuitively, you would expect it not to make much difference, given that your existing query is creating cursors behind the scenes. But that is not necessarily the case. The only way to know for sure is to try doing it both ways, and to do careful timing tests with typical data.

Mike







__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

You may want to try this and check if the results are the same

SELECT JOB.JOB, SUM(LABOR.HOURS*LABOR.RATE) AS SALARY, ROUND(SUM(COST.PRICE*COST.QTY),2) AS PURCHASES, SUM(REQ.QTY*PN.COST) as MAKE ;
FROM JOB ;
LEFT JOIN LABOR ON JOB.JOB=LABOR.JOB ;
LEFT JOIN COST on JOB.JOB=COST.JOB ;
INNER JOIN WORDER ON JOB.JOB=WORDER.JOB ;
INNER JOIN REQ ON WORDER.WORDER=REQ.WORDER ;
INNER JOIN PN ON REQ.PN=PN.PN ;
WHERE JOB.STATUS<='4' ;
GROUP BY JOB.JOB ;
INTO CURSOR TEST READWRITE

hth

MarK

 

[erp84]

I just verified most of my join fields do have an index, I have a couple that don't so I'll add those over the weekend and hopefully I'll see better performance. Might even see an improvement simply re-indexing these tables since a couple of them exceed 1GB.

Mark, had my fingers crossed on this one, it does work but unfortunately it ended up being a tad slower. Going to see how multiple cursors work out.

Thanks guys!

 

[TamarGranor]

Have you tried using SYS(3054) to see what VFP will tell you about optimization?

Tamar

 

[Olaf Doschke]

If a table is very large (1GB) and even if rushmore reports full optimisation, this still depends on enough memory available. In a case I found out a rais of foreground/background memory enabled rushmore to do it's "magic". I reported about this recently, here: thread184-1692390 Read that from "For a few days a certain query on a large table (600MB) ran very slow. ..."

In your case you have the difficulty of subselects, which are joined, subselects cause temp table/cursor, which by default have no indexes, unless VFP creates temp indexes. It's always better to join with original tables. Unfortunately that's not always that simple.

Bye, Olaf.

 

[Olaf Doschke]

I have taken a bit to analyse your subqueries, I don't see any reason to do them, as you neither limit them by a current record of the outmost query nor filter them differently, you can do this in one more complex join series:

Select JOB.JOB, SUM(LABOR.HOURS*LABOR.RATE) AS Labor;
   ,   Round(SUM(COST.PRICE*COST.QTY),2) AS PURCHASES;
   ,   SUM(REQ.QTY*PN.COST) as MAKE;
FROM JOB ;
INNER JOIN WORDER ON JOB.JOB=WORDER.JOB;
INNER JOIN REQ ON WORDER.WORDER=REQ.WORDER;
INNER JOIN PN ON REQ.PN=PN.PN;
LEFT OUTER JOIN LABOR ON JOB.JOB=LABOR.JOB;
LEFT OUTER JOIN COST on JOB.JOB=COST.JOB;
WHERE JOB.STATUS<='4' GROUP BY JOB.JOB;
INTO CURSOR curResult ReadWrite

About the memory setting: Give rushmore enough room to generate it's optimisation "bitmaps" in memory, that needs at least 4 bytes per record of the data, if it was not filtered. So eg if your GB job table has millions of records before filtering and aggregating, rushmore needs several 4 MB of RAM per bitmap and needs a bitmap per where clause. Joins also cause a bitmap. sum all record numbers several times. Push it up. In this case you might need 100 MB RAM for rushmore to work.

In the case I talked about it also was a half GB long table and what's more important many records due to a short record length. If starting low with SYS(3050,1,10^6) I could reproduce the slow query, this was, what a developer at a customer was using, that was even lower than what I did recommend earlier. I only needed to go up to about 2MB and it worked already, but that was a query not including joins and not many where clauses.

It doesn't hurt to go higher, this is not what VFP takes from RAM right away, that RAM is just taken, if needed, when needed. Besides the default value set, if you never call sys(3050) being too high on todays systems, the problem is, VFP does not take more than you allow it, even if there is more RAM available. And rushmore then decides to not optimize! If you have a 16GB system and sysmonitor reports a usage of 6GB, you can easily push this to 2GB. In the near future, this really might be the default value to use, no matter if you get anywhere near that need or not.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

You might also want to try to fiter before the joins, since the filter condition only affects the JOB table

 SELECT JOB.JOB WHERE JOB IN (SELECT JOB.JOB FROM JOB WHERE JOB.STATUS <= "4"), ;
   SUM(LABOR.HOURS*LABOR.RATE) AS SALARY, ;
   ROUND(SUM(COST.PRICE*COST.QTY),2) AS PURCHASES, ;
   SUM(REQ.QTY*PN.COST) as MAKE ; 
FROM JOB ; 
LEFT OUTER JOIN LABOR ON JOB.JOB=LABOR.JOB ; 
LEFT OUTER JOIN COST on JOB.JOB=COST.JOB ; 
INNER JOIN WORDER ON JOB.JOB=WORDER.JOB ; 
INNER JOIN REQ ON WORDER.WORDER=REQ.WORDER ; 
INNER JOIN PN ON REQ.PN=PN.PN ; 
GROUP BY JOB.JOB ; 
INTO CURSOR TEST READWRITE

hth

MarK

 

[Olaf Doschke]

I can imagine one thing, that might not make the single query work for you, because joining many tables multiplies records, eg every combination of labor and cost per job is joined and so your sums get multiples of what you need. You can't sum distinct labour hours, so in the end this overall query wouldn't help you.

Still indexing and highering foregorund memory availabilty for rushmore will help.

Bye, Olaf.