Reputation: 249
Need some help for a Query Optimization
I have the following query but it seems to take more than one minute to render a result. Can anyone help me optimize the results? I believe the 2nd query below would help with the first line but not sure how to group
SELECT
(SELECT COUNT(delivery_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND remarks = ''
AND DeliveryTons.crop_season = 3)) AS Tickets,
(SELECT COUNT(delivery_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND DeliveryTons.crop_season = 3)) AS Deliveries,
(SELECT SUM(tonnage_adjusted)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND delivery_status != 'C'
AND DeliveryTons.crop_season = 3)) AS TonsDelivered,
(SELECT SUM(tonnage_adjusted)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND remarks = ''
AND DeliveryTons.crop_season = 3)) AS TonsMonitored,
(SELECT SUM(ACREAGE)
FROM CaneParcel
WHERE EXISTS
(SELECT DISTINCT(parcel_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND CaneParcel.FIELD_ID = DeliveryTons.parcel_id
AND DeliveryTons.crop_season = 3))) AS AcresMonitored,
(SELECT SUM(ACREAGE)
FROM CaneParcel
WHERE EXISTS
(SELECT DISTINCT(parcel_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND remarks = ''
AND CaneParcel.FIELD_ID = DeliveryTons.parcel_id
AND parcel_status = '3'
AND DeliveryTons.crop_season = 3))) AS ClosedAcres
SELECT COUNT(d.delivery_id),
FROM DeliveryTons d
INNER JOIN FarmerGroups F ON F.BSI_CODE = d.farmer_pay_bsi
WHERE t.assn = 'BSICGP'
AND crop_season = 3
AND remarks=''
GROUP BY d.delivery_id
Here is my DeliveryTons Table:
id crop_season delivery_id parcel_id reaping_code farmer_owner_name farmer_owner_bsi farmer_pay_bsi tonnage_adjusted parcel_status remarks delivery_status
77701 1 1 038 - 0358 CGP001 Not defined 0 2064 15.3625 2 P
77702 1 2 038 - 0358 CGP001 Not defined 0 2064 17.4625 2 P
77703 1 3 038 - 0358 CGP001 Not defined 0 2064 13.5875 2 P
77704 1 4 038 - 0358 CGP001 Not defined 0 2064 19.0250 2 P
77705 1 5 038 - 0358 CGP001 Not defined 0 2064 19.6375 2 P
77706 1 6 038 - 0358 CGP001 Not defined 0 2064 16.2125 2 P
77707 1 7 038 - 0358 CGP001 Not defined 0 2064 22.5375 2 P
77708 1 8 038 - 0358 CGP001 Not defined 0 2064 18.1000 2 P
77709 1 9 038 - 0358 CGP001 Not defined 0 2064 23.0000 2 P
77710 1 11 016 - 0482 BSFA156 CANUL, MARTIN 844 7866 19.3000 1 P
77711 1 12 016 - 0096 BSFA153 ALDANA, MARCOS 986 10024 15.2625 1 P
77712 1 13 004 - 0684 BSFA155 QUEME, BENANCIO 778 10063 17.8125 1 P
77713 1 14 004 - 0224 BSFA162 CHABLE, MARCELINO 198 198 20.5125 1 P
77714 1 15 BSFA160 0 212 16.0375 0 NO_TICKET P
77715 1 16 BSFA159 0 4570 12.3875 0 NO_TICKET P
77716 1 17 016 - 2473 BSFA151 0 9173 13.6875 3 P
77717 1 18 016 - 1566 BSFA149 Not defined 0 1094 15.7250 1 P
77718 1 19 BSFA160 0 148 20.2125 0 NO_TICKET P
77719 1 20 016 - 1566 BSFA149 Not defined 0 1094 12.7625 2 P
77720 1 21 016 - 1566 BSFA149 Not defined 0 1094 15.3875 2 P
77721 1 22 016 - 2152 BSFA175 0 926 15.2625 3 P
77722 1 23 BSFA175 0 5498 13.7250 0 NO_TICKET P
Cane Parcel Table:
geoid FIELD_ID BSI_CODE ACREAGE ASSN
0 004 - 0567 47 7.359515 CSCPA
1 004 - 0008 281 12.38654 CSCPA
2 004 - 0012 281 7.899037 CSCPA
3 004 - 0013 281 4.937749 CSCPA
4 004 - 0014 281 5.002847 CSCPA
5 004 - 0038 9050 6.731369 CSCPA
6 004 - 0045 9028 4.120158 CSCPA
7 004 - 0039 9050 8.519387 CSCPA
8 004 - 0147 289 14.801 BSCFA - CZ
9 004 - 0572 47 5.878942 CSCPA
10 004 - 0030 281 14.33925 CSCPA
11 004 - 1150 281 7.306061 CSCPA
12 004 - 1152 281 3.814288 CSCPA
13 004 - 0017 281 11.5479 CSCPA
FarmerGroups Table:
ID ASSN BSI_CODE FARMER CROP_SEASON
10473 BSCFA - OW 5752 GARCIA,EUGENIO R 2
10474 BSCFA - OW 8894 GARCIA,RAUL R 2
10475 BSCFA - OW 4427 PECH,EVELIA 2
10476 BSCFA - OW 5226 GONZALEZ,DALIA MARIA 2
10477 BSCFA - OW 5753 GONZALEZ,ELVIRA 2
10478 BSCFA - OW 9295 GONZALEZ,RAUL 2
10479 BSCFA - OW 9996 GONZALEZ,TRANSITO 2
10480 BSCFA - OW 10099 PERAZA,ALBERTA 2
10481 BSCFA - OW 4537 GARCIA,SANTIAGO SILVERIO 2
10482 BSCFA - OW 8573 ACK,AMELITA M 2
10483 BSCFA - OW 7467 ACK,EUCARIO 2
10484 BSCFA - OW 7468 ACK,MOISES 2
10485 BSCFA - OW 1848 AGUIRRE,NENCIE 2
10486 BSCFA - OW 4355 BLANCO,SILVIO 2
10487 BSCFA - OW 4476 CAL,ALBERTA GARCIA 2
10488 BSCFA - OW 4480 CAL,ENRIQUE 2
10489 BSCFA - OW 2545 CAL,ERNESTO T 2
10490 BSCFA - OW 6877 CAL,REMIGIO 2
Upvotes: 1
Views: 119
Answers (1)
Reputation: 48139
In the case that the FarmerGroups CAN create multiple rows for a single BSI_CODE record, pre-aggregate flags of the farmers groups table ONCE per BSI_CODE. THEN you can sum it without creating a false Cartesian result...
Think of the inner "PQ" (PreQuery) as doing a binary OR between rows. All you care about is for any single BSI_CODE, does it exist, does it have an empty status (or not), and its delivery status. So, given the sample data of...
FarmerGroups
BSI_CODE Assn Remarks Delivery_Status
A BSICGP test B
A BSICGP C
A BSICGP test C
A BSICGP A
---------------------------------
B BSICGP test B
B BSICGP test C
---------------------------------
C BSICGP test B
---------------------------------
D BSICGP
D BSICGP X
---------------------------------
E BSICGP test1 C
E BSICGP test2 C
---------------------------------
Now, the summary of each BSI_CODE would result as
BSI_CODE NoRemarks IsTonDelivery
A 1 1 (at least 1 record had no remarks, at least 1 record was NOT 'C' status)
B 0 1 (NO records had empty remarks, at least 1 was NOT 'C' status)
C 0 1 (only 1 record. It had a remark, but as NOT 'C' status)
D 1 1 (both no remarks, neither was 'C' status)
E 0 0 (both HAD remarks and BOTH were 'C' status).
So you can see 11 records from farmers group is summarized into 5 rows with respective "flag" columns of 1 or 0.
In a somewhat similar approach for your "CaneParcel", pre-sum the acreage based on the field ID so this too gets a single row per field, to be LEFT-JOINED to the delivery tons... The JOIN to the FarmerGroups will pre-qualify that part by transitive condition.
Now, if your DeliveryTons table has 5 records for "D" either summing, counting, or multiplying by the flag will result in 0 or the value trying to get the aggregate of. You can join to your DeliveryTons table, and since each of your subqueries were relying on the Crop_Season = 3, you can just apply that to your outer query but pulling the above query within...
select
count(*) as Deliveries,
sum( case when dt.remarks = '' then 1 else 0 end ) as Tickets,
sum( dt.Tonnage_Adjusted
* case when dt.delivery_status != 'C' then 1 else 0 end ) as TonsDelivered,
sum( dt.Tonnage_Adjusted
* case when dt.remarks = '' then 1 else 0 end ) as TonsMonitored,
sum( PQ2.totalAcreage ) AcresMonitored,
sum( case when dt.parcel_status = '3' then cp.Acreage else 0 end
* case when dt.remarks = '' then 1 else 0 end ) TotalClosedAcres
from
DeliveryTons dt
JOIN
(SELECT DISTINCT
BSI_CODE,
FROM
FarmerGroups fg
WHERE
ASSN = 'BSICGP' ) PQ
ON dt.farmer_pay_bsi = PQ.BSI_CODE
LEFT JOIN
(select
cp.Field_ID,
sum( cp.Acreage ) totalAcreage
from
CaneParcel cp
group by
cp.Field_ID ) PQ2
on dt.parcel_id = PQ2.Field_ID
where
dt.Crop_Season = 3
This may be a lot to digest, but do believe (without seeing your actual production data -- or even sample data) will work for you as two quick Pre-Query aggregates (PQ and PQ2) once and directly joined (left join) to the DeliveryTons table. I would try it in pieces just to see / confirm what is going on.
UPDATE PER COMMENT
Updated query. Without your sample data, my query was written with implied format / content. After seeing your data, notice there is no value in the farmerGroups table for comments and its in your tonnage table. Adjusted query to accommodate.
Upvotes: 1