Custom SSRS SO Acknowledgement Report taking 2 minutes

[Briser (Brian Sandison)]

If someone can spot the issue and help with the resolution I would be very grateful.

I have created a custom OrderAck to include some customised fields of the various Serial Numbers we assign to parts of the trailers we build and it seems to be working but it takes 2 minutes for the PDF to appear.

I am guessing I have a logic error which is causing it, in order to link the SerialNo table to the OrderHed I am having to create a few Data Source and Relationship entries in the RDD, there is probably a better way for sure but my limited experience is holding me back a bit.

the excerpt below contains the added code:

 FROM OrderHed_" + Parameters!TableGuid.Value  + "  T1 
	LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + "  T2 
	ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum 
 	LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value  + " T3 	
	ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
 	LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value   + "  T4  
	ON T1.RptLanguageID = T4.RptLanguageID
 	LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value   + "  T5  
 	ON T1.Company = T5.Company AND T1.OrderNum = T5.OrderNum
		LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value   + "  T6
		ON T5.Company = T6.Company AND T5.JobNum = T6.JobNum
			LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value   + "  T7  
			ON T6.Company = T7.Company AND T6.JobNum = T7.JobNum
				LEFT OUTER JOIN SerialNo_" + Parameters!TableGuid.Value   + "  T8  
				ON T7.Company = T8.Company AND T7.JobNum = T8.JobNum"

thanks.

[Hally (Simon Hall)]

based on the fact you don’t event select T1 and t2, I would change it to something like

   erp.OrderRel T3
--LEFT OUTER JOIN ---didn't have rpt labeles when I was testing but change T1 to T3
--    erp.RptLabels_" + Parameters!TableGuid.Value + " T4
--    ON T1.RptLanguageID = T4.RptLanguageID
LEFT OUTER JOIN 
   erp.JobProd T5
   ON T3.Company = T5.Company AND T3.OrderNum = T5.OrderNum and t3.OrderLine = t5.orderline and t3.OrderRelNum = t5.OrderRelNum
LEFT OUTER JOIN 
   erp.JobHead T6
   ON T5.Company = T6.Company AND T5.JobNum = T6.JobNum
LEFT OUTER JOIN 
   erp.JobAsmbl T7
   ON T6.Company = T7.Company AND T6.JobNum = T7.JobNum
LEFT OUTER JOIN 
   erp.SerialNo T8
   ON T7.Company = T8.Company AND T7.JobNum = T8.JobNum and t8.ordernum = t3.ordernum and t8.OrderLine = t3.orderline and t8.OrderRelNum = t3.OrderRelNum    

By the way if diesplaying code even aql use the three ticks at the start and end of your code to make a code block refer to here:

Formatting Code block

For all of you that are confused about formatting code blocks and other types of markup that can be done on this forum, please look at this link and walk through the tutorial. It does a really nice job of introducing you to how you can format your posts. I know I am a bit OCD about this stuff, but it really does make everything easier to read. https://commonmark.org/help/

[Briser (Brian Sandison)]

Thanks Simon, I have been trying your code but it won’t run the report, it fails with a syntax error.
I have un-excluded the extra fields from the various tables.
I am not sure about the erp. suffix but even if I revert back to the original format it still errs whilst attempting compiling.

I noticed in the last line you have fields from T8 and T3 but I do not have a relationship between these two tables in the RDD and cannot make one as both are already childs in different relationships.

[Hally (Simon Hall)]

My example was just something I put together using SSMS. So to use in the report query you will have to update the joins where appropriate, compared to your original query.

[Briser (Brian Sandison)]

Aye, I have been looking into the existing Relationships and it needs a bit of planning.
Maybe even starting with a fresh copy of the OrderAck DD if I can’t get it to rationalise down

[Hally (Simon Hall)]

With the copy of the RDD, add one relationship at time and run the report. That will help you narrow down where the speed issue is, in the actual server side generating and storing the data in the reports database or querying that data to put render in the SSRS report itself.

When I ran your query original against the the Demo DB I was seeing a lot of rows being returned, that’s why I changed the joins

[Briser (Brian Sandison)]

Cheers Simon. Maybe I added too much into it at once.

[Briser (Brian Sandison)]

I have managed to reduce this down to about 15 seconds - the issue in this case was I needed to join just OrderRel to SerialNo with the newly created custom field CsgSerialNo_c mapped to SerialNo.

I then removed all the tables/relationships and fields I no longer needed.

Thanks for your help Simon.