Sql Report Extremely Slow To Display Results

[ready2drum]

After modifying a SQL report to display any user(s) that have an MR status of 'canceled', I am noticing that when the report is executed on the server, it's taking so long to display that it has timed out on several occasions. I've been trying to speed the process of viewing the report, but without any success.See code below for details....

USE [sql_reporting]GO/****** Object:  StoredProcedure [dbo].[pp_Material_Request_CANCELED_byUser]    Script Date: 10/27/2009 12:32:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ==================================================-- Author:		<me>-- Modified:	<me>-- Create date:          <7/30/2008>-- Description:	[pp_Material_Request_CANCELED_byUser]-- ==================================================ALTER PROCEDURE [dbo].[pp_Material_Request_CANCELED_byUser](		@facility varchar(10), @USER_VAL varchar(5000)		 )ASSET NOCOUNT ONSET ANSI_NULLS ONDECLARE @connection_string As varchar(50)SET @connection_string = dbo.linked_server(@facility)--use the @connection_string variable in your calls for--openquery('+@connection_string+',SET @facility = dbo.change_BRE_to_BR(@facility)-- end linesSET @USER_VAL = dbo.format_string(@USER_VAL)DECLARE @SQL VARCHAR(8000)		SET @SQL = '	SELECT tidmrmst.material_request, tidmrmst.matl_request_type,       tidmrmst.mr_status, tidmrmst.need_date_matl, tidmrmst.status_date,       tidmrmst.resp_id, tidmrmst.job_type, tidmrmst.reference_type,       tidmrmst.reference_nbr, tidmrmst.reference_sub_nbr, tidmrmst.title,       lower(ltrim(rtrim(tidprefs.preference_value))) ||''@''||       lower(ltrim(rtrim(tidprefs1.preference_value))) As email,       rtrim(tidemplo.first_name) || '' '' || ltrim(tidemplo.last_name) As name  FROM cb10.tidmrmst, cb10.tidprefs, cb10.tidprefs tidprefs1, tidemplo WHERE (    (tidmrmst.resp_id IN ('+@USER_VAL+'))   AND (tidmrmst.facility = '''+@facility+''')   AND ((tidmrmst.mr_status = ''CANCELED''))   AND ((tidmrmst.resp_id = tidemplo.passport))   AND ((tidmrmst.resp_id = tidprefs.pref_qual_value (+)))   AND ((tidmrmst.resp_id = tidprefs1.pref_qual_value (+)))   AND ((tidprefs.preference_name = ''EMAIL USER ID''))   AND ((tidprefs1.preference_name = ''EMAIL USER NETADDR''))   ) ORDER BY tidmrmst.resp_id'SET @SQL = 'select * from openquery('+@connection_string+',''' + REPLACE(@SQL,'''','''''') + ''')'EXEC (@SQL)SET NOCOUNT OFF

Any help and or suggetions is greatly appreciated! Thanks!

[justsomeguy]

What are these lines doing:SET @connection_string = dbo.linked_server(@facility)SET @facility = dbo.change_BRE_to_BR(@facility)Is this query running on a separate server from the one the SP is defined on?The main reason this is taking a while is because you're doing an inner join on 4 tables at once. That's not very efficient, it's better to join each table individually and specify the criteria for joining when you do so. To see why this is a problem, a join like you're doing creates a cartesian product of the two tables being joined, or every possible combination of rows. So assume that each of your 4 tables has 100 rows in it. If the tables are called A, B, C, and D, when you do this:... FROM A, B, C, D WHERE ...What that does is first create a cartesian product of A and B, so since each table has 100 rows the joined table has 10,000 rows. Then it joins that table with C, so it creates another cartesian product and the result has 1,000,000 rows. Then it joins that with D, and the result has 100,000,000 rows. So you end up with a table with one hundred million rows after joining 4 tables with 100 rows each. Think about what happens if those tables start with 10,000 rows each, or 100,000 rows. After it does all of those joins, then it applies the WHERE conditions to filter the result set (which also takes time).Instead of doing that, join each table individually and say which columns it's joining on, then instead of creating a cartesian product of the entire table it will create a temp table with only the rows that matched your join conditions.

[ready2drum]

Ouch! I had no idea it was doing all that...I really appreciate your feedback and guidance on this issue. It makes a huge difference to know these kinds of things.I'll definitely make the needed change to the code, now that I understand what's happening behind the scenes.Thanks again for your help!

[ready2drum]

Yes, the query is running on the same server that the SP is on.....it's still running slow, but not as slow any longer. Question, for a report that utilizes alot of fields and 7 tables...such as the following:

'SELECT DISTINCT 		tidcafac.facility, tidcafac.catalog_id, tidcamst.catalog_desc, 		tidcafac.catalog_status, tidcafac.stock_type, 		tidcafac.auto_reorder_ind, tidcafac.pre_capital_ind,		tidcamst.unit_of_issue, tidcafac.target_maximum, 		tidcawhs.qty_in_whse, tidcafac.avg_unit_price, 		tidcawhs.loc_zone, tidcawhs.loc_row, tidcawhs.loc_section,		tidcawhs.loc_tier, tidcawhs.loc_bin, tidcamfr.manufacturer_code,		tidcamfr.manuf_part_number, tidcapur.vendor_code,		tidcapur.vendor_suffix,	MAX(CAST(tidrclin.rec_line_date As integer)) AS rec_line_date,		tidcawhs.last_updated_date, tidcafac.lead_time_order,		tidcafac.lead_time_vendor, tidcafac.leadtime_putaway,		tidcafac.repairable_ind, tidcafac.work_order_nbr,		tidcafac.auto_requisition, tidcapur.auto_award, 		tidcapur.bpo_candidate, tidcafac.expensed_item_ind, 		tidcamst.commodity_cat_code, tidcamst.commodity_type_cod,		tidcamst.commodity_name_cod, tidcafac.cycle_count_code,		tidcafac.abc_analysis_code, tidacdst.cost_center_charge, 		tidacdst.activity_id, tidacdst.account_nbr, tidacdst.sub_account_nbr	FROM 		cb10.tidcafac,		cb10.tidcamfr,		cb10.tidcamst,		cb10.tidcapur,		cb10.tidcawhs,		cb10.tidrclin,		cb10.tidacdst		WHERE (		(tidcafac.catalog_id = tidcamst.catalog_id_master)		AND (tidcafac.catalog_id = tidcamfr.catalog_id)		AND (tidcafac.q_level = tidcamfr.q_level)		AND (tidcafac.facility = tidcapur.facility)		AND (tidcafac.catalog_id = tidcapur.catalog_id)		AND (tidcafac.q_level = tidcapur.q_level)		AND (tidcafac.facility = tidcawhs.facility(+))		AND (tidcafac.catalog_id = tidcawhs.catalog_id(+))		AND (tidcafac.q_level = tidcawhs.q_level(+))		AND (tidcafac.facility = tidrclin.facility(+))		AND (tidcafac.catalog_id = tidrclin.catalog_id(+))		AND (tidcafac.q_level = tidrclin.q_level(+))		AND ((tidcafac.facility = '''+@facility+'''))		AND ((tidcamst.commodity_cat_code IN ('+@commodity_cat_code+')))		)	GROUP BY 		tidcafac.facility, tidcafac.catalog_id, tidcamst.catalog_desc, tidcafac.catalog_status,		tidcafac.stock_type, tidcafac.auto_reorder_ind, tidcafac.pre_capital_ind,		tidcamst.unit_of_issue, tidcafac.target_maximum, tidcawhs.qty_in_whse,		tidcafac.avg_unit_price, tidcawhs.loc_zone, tidcawhs.loc_row, tidcawhs.loc_section,		tidcawhs.loc_tier, tidcawhs.loc_bin, tidcamfr.manufacturer_code,		tidcamfr.manuf_part_number, tidcapur.vendor_code, tidcapur.vendor_suffix,		tidcawhs.last_updated_date, tidcafac.lead_time_order, tidcafac.lead_time_vendor,		tidcafac.leadtime_putaway, tidcafac.repairable_ind, tidcafac.work_order_nbr,		tidcafac.auto_requisition, tidcapur.auto_award, tidcapur.bpo_candidate, 		tidcafac.expensed_item_ind, tidcamst.commodity_cat_code, tidcamst.commodity_type_cod,		tidcamst.commodity_name_cod, tidcafac.cycle_count_code, tidcafac.abc_analysis_code,		tidacdst.cost_center_charge, tidacdst.activity_id, tidacdst.account_nbr, tidacdst.sub_account_nbr	ORDER BY 		tidcamst.commodity_cat_code, tidcamst.commodity_type_cod, tidcamst.commodity_name_cod ';

Can you give an example of how to start breaking this query down so that it doesn't hang up the system for an eternity?Thanks in advance!

[justsomeguy]

I'm not that great at optimizing SQL, there are entire books devoted to doing that which I haven't had a chance to read yet. For one though, the same issue applies to this that applies to the last, you're doing a cartesian product join on all 7 tables instead of joining them individually. The WHERE conditions should only be used to filter the result set, that's not the place to put the join conditions. Other than that, the next best thing you can do quickly is to make sure that all of the tables have good indexes defined. Too many indexes will slow down inserts, updates, and deletes, but if you have indexes on all of the columns that you're using in the join or WHERE conditions that will also speed things up.