SSRS Report Rendering Horribly Slow versus SSMS execution - 5 minutes versus 60!
Good morning Team,
This is directly related to a recently answered question here on EE (https://www.experts-exchange.com/questions/29103154/SQL-2008R2-Cursor-in-Function-Very-Slow.html?anchorAnswerId=42593407#a42593407). Both Mark Wills and Scott Pletcher successfully resolved my issue related to a cursor in a scalar function however, the next level is now posing a bit of a challenge, as the scalar was converted to an inline table function and, SSRS report builder is prompting me to enter parameters BEFORE I try to run the report (meaning, before the actual page where a user would actually enter the parameters - it wants them once I change and save the query in the dataset). --- This part is fixed - Now, it's SSRS taking a very long time to render the report!
Previous Select Statement - no prompt to enter parameters - (line in bold letters is what has been replaced):
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
, dbo.fnt_Get_Customer_Prici
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id
NEW Select Statement (line in bold is the new inline with cross apply):
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
,CustPrice
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
cross apply (select Customer_Price from [dbo].[fnt_Get_Customer_Pr
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id
SSRS-Customer-Price-File-Test-Parame.png
This is directly related to a recently answered question here on EE (https://www.experts-exchange.com/questions/29103154/SQL-2008R2-Cursor-in-Function-Very-Slow.html?anchorAnswerId=42593407#a42593407). Both Mark Wills and Scott Pletcher successfully resolved my issue related to a cursor in a scalar function however, the next level is now posing a bit of a challenge, as the scalar was converted to an inline table function and, SSRS report builder is prompting me to enter parameters BEFORE I try to run the report (meaning, before the actual page where a user would actually enter the parameters - it wants them once I change and save the query in the dataset). --- This part is fixed - Now, it's SSRS taking a very long time to render the report!
Previous Select Statement - no prompt to enter parameters - (line in bold letters is what has been replaced):
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
, dbo.fnt_Get_Customer_Prici
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id
NEW Select Statement (line in bold is the new inline with cross apply):
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
,CustPrice
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
cross apply (select Customer_Price from [dbo].[fnt_Get_Customer_Pr
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id
SSRS-Customer-Price-File-Test-Parame.png
ASKER
I am able to run it for manufacturers that return less data (ran it for one with 7,000 rows; this one is a little over 41,000). Attached is timeouts screenshot - the translucent data in the background is from a different manufacturer code (FSR), not Belden Classics.
So close!
SSRS-Customer-Price-File-Test-Timeou.png
So close!
SSRS-Customer-Price-File-Test-Timeou.png
ASKER
I believe I have figured this out by using the query below. Report builder does NOT prompt me for the parameters now but, it takes forever to run. In report builder, it simply times out, but if I run in the report URL, where I have 'do not timeout' set site-wide, it takes nearly 60 minutes to render the report. @Mark Wills' ITVR works perfectly in Management studio (~5 minutes for 40,000 records; highest volume manufacturer + customer) so, I'm thinking this has to do with the parameters in Report builder. Should I open a new question?
New Query in Report builder that doesn't prompt for parameters:
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
,CustPrice
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
cross apply (select CustPrice from [dbo].[fnt_Get_Customer_Pr
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id
New Query in Report builder that doesn't prompt for parameters:
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
,CustPrice
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
cross apply (select CustPrice from [dbo].[fnt_Get_Customer_Pr
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id
ASKER
Attached is the RDL for the SSRS report.
Customer-Price-File-Report---TEST.rdl
Customer-Price-File-Report---TEST.rdl
Is SSRS running on the same server as SQL, or do you maybe have a slow connection between the report and the data? Are you doing a lot of calculations in the report ([this field] = [that field] + [that other field]) that you might be able to move to the query instead? Are you doing a lot of value-dependent formatting (IF this is < 1 THEN make the font green)? With 40k+ rows of data, any work you're making the front-end do is 40k+ the work for the front end. Also try running it in a different browser, just to make sure it isn't a weird browser rendering problem.
You should be more confident with your abilities. Just reading through this thread you have already overcome a few roadblocks but have worked it out :)
Well done :)
It is the way SSRS connects to the server and interacts. It is a pain, but it is also part of the terrain.
Timeouts are a common problem with SSRS, and Microsoft have published their tips : https://technet.microsoft.com/en-us/library/jj969577.aspx?f=255&MSPPError=-2147217396
The bit you will be interested in is https://technet.microsoft.com/en-us/library/jj969577.aspx?f=255&mspperror=-2147217396#Anchor_1
Well done :)
It is the way SSRS connects to the server and interacts. It is a pain, but it is also part of the terrain.
Timeouts are a common problem with SSRS, and Microsoft have published their tips : https://technet.microsoft.com/en-us/library/jj969577.aspx?f=255&MSPPError=-2147217396
The bit you will be interested in is https://technet.microsoft.com/en-us/library/jj969577.aspx?f=255&mspperror=-2147217396#Anchor_1
ASKER
Thank you @Mark! I'm reading now and definitely learning as I go. The report does finish but, such a let down as you got it down to 5 minutes in SSMS!!!
@Russell - thank you for the insight - the data resides on one server and the reports server is separate. That said, the query itself runs very efficiently in SSMS but bloats to 5x the runtime in SSRS. Not sure if it's the parameters or what but, it goes from 5 minutes to nearly an hour. Reading and researching.
And, if you guys spot anything in the RDL or need any additional info on the parameters in SSRS, please let me know!
Jeremy
@Russell - thank you for the insight - the data resides on one server and the reports server is separate. That said, the query itself runs very efficiently in SSMS but bloats to 5x the runtime in SSRS. Not sure if it's the parameters or what but, it goes from 5 minutes to nearly an hour. Reading and researching.
And, if you guys spot anything in the RDL or need any additional info on the parameters in SSRS, please let me know!
Jeremy
Try opening SSMS on the report server with a connection to the main server - if it's slow then you know it's the pipes between them (and you can stop banging your head against SSRS ;) ).
ASKER
@Russell - will definitely give this a try and then potentially use one of the suggested steps from @Mark in the tech net article!
The article (whilst dynamics centric) is also in MS docs : https://docs.microsoft.com/en-us/sql/reporting-services/report-server/setting-time-out-values-for-report-and-shared-dataset-processing-ssrs?view=sql-server-2017
Was looking for a timeout value in your RDL - it is possible when designing your report (on the page where you specify your query) on the bottom left of that window you should see a "Timeout in Seconds"
Images taken from : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/13c570e8-39b7-4eb0-ba42-9c10358f338a/report-timing-out-during-creation?forum=sqlreportingservices
Was looking for a timeout value in your RDL - it is possible when designing your report (on the page where you specify your query) on the bottom left of that window you should see a "Timeout in Seconds"
Images taken from : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/13c570e8-39b7-4eb0-ba42-9c10358f338a/report-timing-out-during-creation?forum=sqlreportingservices
ASKER
Hi @Mark - the report does get created and does not timeout. Would that setting somehow make it process faster?
Not really, it is the way SSRS communicates with the server.
Having said that, there are a few things you can do.... And this is really stretching the grey matter for everything I have read (so wont be able to quote sources)....
You can look at reducing the number of items being transferred - make sure the query is only producing the data needed for the report. Also make sure that the SSRS dataset matches exactly with what is being returned to SSRS.
Also, triple check your parameters, make sure they are the same type / size etc. A thing called Parameter Sniffing can be a reports worst enemy.
You can use a technique called Filtering. Normally used with multiple params, or reporting a subset, it also focuses attention on the parameters to help with parameter sniffing. see : https://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/ and scroll down to Using Filters with Multiple Value Parameters
You could use the query hint "OPTION (RECOMPILE)" at the end of your query (last line without the double quotes)
Could look at the function to return more information - seems the query and the function do have some overlapping information, or at least do join to the same tables...
And finally, there is creating a stored procedure where you can manage the parameters by assigning the report params to declaritives within the stored procedure to then perform the query using the procedures internal params.
Having said that, there are a few things you can do.... And this is really stretching the grey matter for everything I have read (so wont be able to quote sources)....
You can look at reducing the number of items being transferred - make sure the query is only producing the data needed for the report. Also make sure that the SSRS dataset matches exactly with what is being returned to SSRS.
Also, triple check your parameters, make sure they are the same type / size etc. A thing called Parameter Sniffing can be a reports worst enemy.
You can use a technique called Filtering. Normally used with multiple params, or reporting a subset, it also focuses attention on the parameters to help with parameter sniffing. see : https://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/ and scroll down to Using Filters with Multiple Value Parameters
You could use the query hint "OPTION (RECOMPILE)" at the end of your query (last line without the double quotes)
Could look at the function to return more information - seems the query and the function do have some overlapping information, or at least do join to the same tables...
And finally, there is creating a stored procedure where you can manage the parameters by assigning the report params to declaritives within the stored procedure to then perform the query using the procedures internal params.
ASKER
Hi @Mark - Apologies to all for the Houdini act - I have a customer who, for whatever reason, decided to have their onsite 'power user' transition their entire on-prem Exchange infrastructure to a colo using Zimbra with 3rd party intermediaries to sync email on clients' devices. Needless to say, it's a disaster. I'm going to swing back through here and read thoroughly but, to the point, I was never able to make it any more efficient on my own. Thanks to @Mark I was able to make it work - which is gigantic but, no longer nearly as efficient in SSRS as it is in SSMS. I'll be back.
ASKER
Sorry Guys - Yes, @Mark absolutely helped resolved the SSMS side of this equation and, the select statement runs 85% faster. However, in SSRS, the new statement is slower using the CROSS APPLY versus the previous function. As a matter of fact, the query will never finish in SSRS using the new statement, whereas the previous statement using the scalar function will, on occasion, finish in less than an hour (finishes most of the time in a little under 1.5 hours). I'm absolutely stumped as I have been hammering away on this, off and on, over the last few days to no avail. First it was the overall efficiency and now it is SSRS!
If anyone has any more advice or, I can actually get someone to look at this with me 'live' - that would be great.
*** To clarify - it never finishes using the customer and manufacturer with the most data to parse and render.
Again, thank you all for your time, dedication and, expertise!
If anyone has any more advice or, I can actually get someone to look at this with me 'live' - that would be great.
*** To clarify - it never finishes using the customer and manufacturer with the most data to parse and render.
Again, thank you all for your time, dedication and, expertise!
ASKER
Please don't close - I really do need this resolved and, I'm now in a position to be engaged!
Sorry, I thought you were pulled away for a while and I was just cleaning up. No objections to keeping it open!
ASKER
Thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good morning @Mark - First, thank you for extending your time with me on Independence Day - You're a good man. Second, I did think about using an SP however, I thought it wouldn't make much of a difference (shows what I know!) - I am following your lead and implementing as an SP (now) and will test in SSRS once complete.
Again, THANK YOU for all of your help on this - I've never had such a complex inefficiency to deal with in SSRS in all my years. I really can't say enough about the help you've provided and, the knowledge I've gained reading your expert comments.
Jeremy
Again, THANK YOU for all of your help on this - I've never had such a complex inefficiency to deal with in SSRS in all my years. I really can't say enough about the help you've provided and, the knowledge I've gained reading your expert comments.
Jeremy
No worries, happy to be here.
Dont get too caught up in the fineries of the report, just start as a very raw, basic (new) report so we can go step by step. Not much point getting too carried away if the SP approach isnt going to offer some advantages....
We want to try and identify the problem areas.
Dont get too caught up in the fineries of the report, just start as a very raw, basic (new) report so we can go step by step. Not much point getting too carried away if the SP approach isnt going to offer some advantages....
We want to try and identify the problem areas.
ASKER
@Mark - Absolutely unbelievable...Created the SP, added to SSRS, tested....completes within 4 minutes. I ran this against our top 10 customers + mfrs and it is amazing. I'm bedraggled!
ASKER
@Mark is simply phenomenal. Not only the expertise (which is in the 99th percentile) but, the time, dedication, and patience to help someone like me, in a situation as muddied as this - Just fantastic.
Fantastic - very happy to hear it worked for you. Such a relief....
ASKER
Yes - Definitely a weight off of our collective shoulders. Please, enjoy your day, and I'm truly appreciative of your time and help.
If you don't need it in SSRS, cut it out.
Wanted to throw this in just as a caveat. I run several reports from stored procedures. Two of these run from the same stored proc but the front end is a bit different. I recently had a programmer here replace a long-running proc that figured a lot of costing info and other things I didn't need for these particular reports.
He cut execution of the actual procedure down from 2-5 minutes to ~1 second. However, when I used this proc for a dataset in SSRS, it still took about 2 minutes to render the report. I deleted an old dataset which I had left in the report data view of the reports, and I had one field being returned by the stored proc that I wasn't using in the report. I deleted this field from the dataset view. By cutting everything out that I wasn't using I can produce a 34 page report (~1700 rows) in under 5 seconds.
Just dropping this note in case someone runs across this issue.
Wanted to throw this in just as a caveat. I run several reports from stored procedures. Two of these run from the same stored proc but the front end is a bit different. I recently had a programmer here replace a long-running proc that figured a lot of costing info and other things I didn't need for these particular reports.
He cut execution of the actual procedure down from 2-5 minutes to ~1 second. However, when I used this proc for a dataset in SSRS, it still took about 2 minutes to render the report. I deleted an old dataset which I had left in the report data view of the reports, and I had one field being returned by the stored proc that I wasn't using in the report. I deleted this field from the dataset view. By cutting everything out that I wasn't using I can produce a 34 page report (~1700 rows) in under 5 seconds.
Just dropping this note in case someone runs across this issue.
ASKER
select
Manf.vendor_name as Manufacturer
,convert(varchar(10),getda
,Mast.Item_id
,Mast.Item_Desc
,Mast.sales_pricing_unit
,Mast.sales_pricing_unit_s
/*,Mast.supplier_part_no*/
,supp.supplier_part_no
,case when sales_pricing_unit_size <> purchase_pricing_unit_size
mast.List_Price * sales_pricing_unit_size
else mast.List_Price
end as export_List_Price
,mast.inv_mast_uid
,CustPrice
, 'N' as Serialized
, @vendorNo as VendorNum
from vHerman_inv_Mast as Mast
cross apply (select CustPrice from [dbo].[fnt_Get_Customer_Pr
inner join p21_view_inv_loc loc on mast.inv_mast_uid = loc.inv_mast_uid
INNER JOIN p21_view_inventory_supplie
INNER JOIN inventory_supplier_x_loc xloc ON supp.inventory_supplier_ui
INNER JOIN (Select Supplier.vendor_name, Scheduler.Manufacturer, supplier_id
from Editranslator..WizardGener
inner join Editranslator..WizardGener
inner join vHerman_Supplier_Vendor as Supplier (nolock) on Main.supplierid = Supplier.supplier_id
) Manf ON Manf.Manufacturer = left(Mast.item_id,3)
where left(Mast.item_id,3) IN (@manufacturerCode)
/*and Business_Type='B & P'*/
and loc.location_id = '100002'
and Mast.item_id not like '%NLA'
and loc.discontinued != 'Y'
and xloc.primary_supplier = 'Y'
and xloc.location_id = '100002'
and isnull(supp.supplier_part_
order by Mast.item_id