Hi Nikolaos,
So we copied the data from Perf environment to my dev machine. There are 1024 records (82 pages)
I ran the report and used my mobile stop watch, it took the report to appear 5.16 minutes (i'm using in memory tmp table) I'm not sure how it took more in perf environment, which was about 7.5 minutes).
Here's my query when I run the report:(please not that the deviceStatus condition is from the query itself. However, for the custAccount and InventLocationId then it's from the contract parameters)
query:
SELECT Table1.Id, Table1.PurchOrderFormNum, Table1.Name, Table1.CustAccount
, TableX.DeviceId, DeviceStatus, PickupLocationId, PickupDate, ReturnDate, TransId, TableX.ItemId
,TableX.DeviceName,
TableX.dataAreaId, TableX.Id, Qty, TableX.JobSiteId, TableX.ClassId, TableX.ModelId
,TableY.JobSiteId
,lpa1.Address
,lpa2.Address
FROM Table1
JOIN TableX ON Table1.Id= TableX.Id
full OUTER JOIN TableY ON TableX.JobSiteId = TableY.JobSiteId
full OUTER JOIN LogisticsPostalAddress lpa1 ON TableY.DeliveryPostalAddress = lpa1.RecId
full OUTER JOIN LogisticsPostalAddress lpa2 ON Table1.DeliveryPostalAddress = lpa2.RecId
WHERE ((Table1.CustAccount = N'xxx')) AND ((InventLocationId = N'1111')) AND ((DeviceStatus = 55 OR DeviceStatus = 56 OR DeviceStatus = 40))
Now i'm following your instructions:
1. I commented out the insert line of code and it took 15 seconds (1 page, no records)
Q1: Any problem with this?
2. I changed to tempDB now (with insert line is not commented out) and it now took using mo mobile stop watch 5.35 minutes which is slower than in memory by 10 seconds
Q2: btw now the records are 1024 but they might be more, so should I switch to tmpDB anyways, is it considered faster? but how come it was slower than in memory?
I switched back to in Memory now
3. you asked me to capture trace
I noticed two sessions,
one session, where it contains my query and process report
and the other session is where the controller of the report took a lot of time
Q3: what should i understand from this? i'm new to this
4. part1: I don't have any indexes in my tmp table. However, i have the following three fields in my contract parameter (ItemId, CustAccount, inventLocationId)
Q4a: should i add an index to my tmp table? should the index contain the three fields? or should i add 3 indexes for each field. or should i add an index to another table?
part 2: I don't have nested loops, here's my code.
Q4b: any suggestions?
///
/// The ReportRDP class is the data provider for the Report report.
///
[SRSReportParameterAttribute(classstr(ReportContract)), SRSReportQueryAttribute(queryStr(ReportQuery))]
class ReportRDP extends SRSReportDataProviderBase
{
ReportTmp reportTmp;
///
/// Gets an ReportTmp record.
///
///
/// The ReportTmp record.
///
[SrsReportDataSetAttribute(' ReportTmp')]
public ReportTmp getReportTmp()
{
select reportTmp;
return reportTmp;
}
///
/// Processes the report business logic.
///
public void processReport()
{
ReportContract contract = this.parmDataContract() as ReportContract;
Query query = this.parmQuery();
if(contract.parmAccountNum() != null)
{
query.dataSourceTable((tablenum(Table1))).addRange(fieldNum(Table1, CustAccount)).value(contract.parmAccountNum());
}
if(contract.parmItemId() != null)
{
query.dataSourceTable((tablenum(TableX))).addRange(fieldNum(TableX, ItemId)).value(contract.parmItemId());
}
if(contract.parmInventLocation() != null)
{
query.dataSourceTable((tablenum(Table1))).addRange(fieldNum(Table1, InventLocationId)).value(contract. parmInventLocation());
}
QueryRun queryRun = new QueryRun(this.parmQuery());
while (queryRun.next())
{
Table1 table1 = queryRun.get(tableNum(Table1)) as Table1;
TableX tableX = queryRun.get(tableNum(TableX)) as TableX;
LogisticsPostalAddress logisticsPostalAddress = queryRun.getNo(5);
TableY tableY = queryRun.get(tableNum(TableY)) as TableY;
LogisticsPostalAddress tableYlogisticsPostalAddress = queryRun.getNo(4);
if(tableX)
{
if(tableX.ClassId == '111' && t.ModelIableX == '111-11')
{
continue;
}
reportTmp.clear();
reportTmp.CompanyLogo = FormLetter::companyLogo();
Table2 table2;
select table2 where table2.EnumBy == Enum::A && table2.TransId == tableX. TransId;
if(table2)
{
reportTmp.itemId = tableX.ItemId;
if(tableX.ItemIdAllocated != null)
{
reportTmp.Name = strFmt('%1 %2 %3', Item::find(tableX.temIdAllocated).Name, "As", table2.Name);
}
else
{
reportTmp.Name = table2.Name;
}
reportTmp.SalesUnit = table2.SalesUnit;
if(table2. ChargesGroupId != null && table2. ChargesGroupId == 'KkK')
{
reportTmp.SalesUnit = table2.SalesUnit ' (' table2.ChargesGroupId ')';
}
reportTmp.LineAmount = table2.LineAmount;
reportTmp.LineDisc = table2.LinePercent;
reportTmp.SalesPrice = table2.SalesPrice;
reportTmp.CustAccount = table2.CustAccount;
reportTmp.CStatus = table2.CStatus;
reportTmp.DataArea = tableX.dataAreaId;
reportTmp. Id = tableX. Id;
reportTmp.Device = tableX.DeviceId;
reportTmp.DeviceStatus = tableX.DeviceStatus;
reportTmp.PickupDate = tableX.PickupDate;
reportTmp.ReturnDate = tableX.ReturnDate;
reportTmp.Qty = tableX.Qty;
reportTmp.PickupLocationId = tableX.PickupLocationId;
reportTmp.CustName = table1.Name;
if(tableX.JobSiteId != null && tableY && tableY.DeliveryPostalAddress != 0)
{
reportTmp.DeliveryAddress = tableYlogisticsPostalAddress.Address;
}
else
{
reportTmp.DeliveryAddress = logisticsPostalAddress.Address;
}
reportTmp.PurchaseOrderNumber = table1.PurchOrderFormNum;
reportTmp.insert();
}
else
{
reportTmp.itemId = tableX.ItemId;
if(tableX.ItemIdAllocated != null)
{
reportTmp.Name = Item::find(tableX.ItemIdAllocated).Name;
}
else
{
reportTmp.Name = tableX.DeviceName;
}
reportTmp.CustAccount = table1.CustAccount;
reportTmp.DataArea = tableX.dataAreaId;
reportTmp.Id =tableX.Id ;
reportTmp.Device = tableX.DeviceId;
reportTmp.DeviceStatus = tableX.DeviceStatus;
reportTmp.PickupDate = tableX.PickupDate;
reportTmp.ReturnDate = tableX.ReturnDate;
reportTmp.Qty = tableX.Qty;
reportTmp.PickupLocationId = tableX.PickupLocationId;
reportTmp.CustName = table1.Name;
if(tableX.JobSiteId != null && tableY && tableY.DeliveryPostalAddress != 0)
{
reportTmp.DeliveryAddress = tableYlogisticsPostalAddress.Address;
}
else
{
reportTmp.DeliveryAddress = logisticsPostalAddress.Address;
}
reportTmp.PurchaseOrderNumber = table1.PurchOrderFormNum;
reportTmp.insert();
}
}
}
}
}
