Re: Query Performance Decrease
Hi Rick, here is the query. Let me know what you think.
update tableaprerollup
set InstalledProductId = null
;
set session group_concat_max_len = 10000000
;
truncate tablea
;
alter table tablea add IDList longtext
;
create FULLTEXT INDEX IDList on tablea(IDList)
;
drop table if exists tl
;
create table tl as
Select InstalledProductId,
Lease_Cust_id
from tablel
where InstalledProductId is not null
group by InstalledProductId
;
create index InstalledProductId on tl(InstalledProductId)
;
create index Lease_Cust_id on tl(Lease_Cust_id)
;
insert into tablea (LastModifiedBy, LastModifiedTimestamp, LastModifiedUsing, Quantity, InstalledAtSiteID, InstalledAtCustomerName, InstalledAtCustomerCity, InstalledAtCustomerState, InstalledAtCustomerZip, InstalledAtCustomerCountry, InstalledAtCustomerAddress, ContractId, ShipDate, SerialNumber, CustomerId, ProductId, BusinessUnitId, ServiceLevelId, ConfiguredBTU, ConfiguredWatts, ConfiguredCubicFeet, ConfiguredSPI, CurrentServiceAmount, ServiceAmountFor12Months, ServiceAmountFor24Months, ServiceAmountFor36Months, GenericData, GenericComment, CurrentSoftwareAmount, SoftwareAmountFor12Months, SoftwareAmountFor24Months, SoftwareAmountFor36Months, MDF, DiscountCategory, ResellerAMID, ResellerName, SourceDate, ContractStart, ContractEnd, ContactName, ContactPhone, ContactEmail, ContactURL, AMPID, OrderingContactName, OrderingContactPhone, OrderingContactEmail, DNB_Dmst_Ultm_DUNS_No, DNB_Global_Ultm_DUNS_No, DNB_HQ_DUNS_No, DNB_Parnt_DUNS_No, DNB_Site_DUNS_No, DataSource, LeaseContractNumber, LeaseStart, LeaseEnd, LeaseType, LeaseTotalDuration, LeaseLapsedDuration, LeaseRemainingDuration, LeaseTotalAmount, LeaseMonthlyPayment, LeaseLapsedAmount, LeaseRemainingAmount, LeaseIsActive, EOSLDate, LeaseTotalAmountAllItems, GroupType, Channel, AgeBand, IDList)
Select
USER(),
NOW(),
'HP Data Builder',
SUM(Quantity),
InstalledAtSiteID,
InstalledAtCustomerName,
InstalledAtCustomerCity,
InstalledAtCustomerState,
InstalledAtCustomerZip,
InstalledAtCustomerCountry,
InstalledAtCustomerAddress,
ContractId,
DATE_ADD(MIN(ShipDate), INTERVAL (DATEDIFF(MAX(ShipDate), MIN(ShipDate))/2) DAY),
trim(', ' from group_concat(distinct SerialNumber separator ', ')),
CustomerId,
ProductId,
ip.BusinessUnitId,
ServiceLevelId,
SUM(p.BTU),
SUM(p.Watts),
SUM(p.CubicFeet),
SUM(p.PowerUsage),
SUM(CurrentServiceAmount),
SUM(ServiceAmountFor12Months),
SUM(ServiceAmountFor24Months),
SUM(ServiceAmountFor36Months),
GenericData,
GenericComment,
SUM(CurrentSoftwareAmount),
SUM(SoftwareAmountFor12Months),
SUM(SoftwareAmountFor24Months),
SUM(SoftwareAmountFor36Months),
MDF,
DiscountCategory,
ResellerAMID,
ResellerName,
CURDATE(),
ContractStart,
ContractEnd,
ContactName,
ContactPhone,
ContactEmail,
ContactURL,
AMPID,
OrderingContactName,
OrderingContactPhone,
OrderingContactEmail,
DNB_Dmst_Ultm_DUNS_No,
DNB_Global_Ultm_DUNS_No,
DNB_HQ_DUNS_No,
DNB_Parnt_DUNS_No,
DNB_Site_DUNS_No,
DataSource,
LeaseContractNumber,
DATE_ADD(MIN(LeaseStart), INTERVAL (DATEDIFF(MAX(LeaseStart), MIN(LeaseStart))/2) DAY),
DATE_ADD(MIN(LeaseEnd), INTERVAL (DATEDIFF(MAX(LeaseEnd), MIN(LeaseEnd))/2) DAY),
LeaseType,
LeaseTotalDuration,
LeaseLapsedDuration,
LeaseRemainingDuration,
SUM(LeaseTotalAmount),
SUM(LeaseMonthlyPayment),
SUM(LeaseLapsedAmount),
SUM(LeaseRemainingAmount),
MAX(LeaseIsActive),
MIN(ip.EOSLDate),
LeaseTotalAmountAllItems,
GroupType,
MAX(Channel),
l.Lease_Cust_Id,
trim(',' from group_concat(distinct ip.Id separator ','))
FROM tableaprerollup as ip
INNER JOIN tableb as p on ip.ProductId = p.Id
LEFT JOIN tl as l on ip.Id = l.InstalledProductId
where ip.ObligationType IN ('L')
GROUP BY ip.BusinessUnitId, ip.CustomerId, p.ShortNameId, LeaseContractNumber, l.Lease_Cust_Id
;
drop table if exists rollupCompare
;
create table rollupcompare as
Select Id as InstalledProductId, IDList as InstalledProductPreRollupId
from tablea
where Quantity = 1
;
alter table rollupcompare modify InstalledProductPreROllupId int
;
create index ID on rollupcompare(InstalledProductPreRollupId)
;
call NC_Rollup()
;
update tableaprerollup as a, rollupcompare as b
set a.InstalledProductId = b.InstalledProductId
where a.Id = b.InstalledProductPreRollupId
;
alter table tablea
drop column IDList
;