Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Speeding up complex SQL Query

    I have an extremley complex (in my opinion) SQL Query which works fine locally but because of the amount of data on my live SQL database does not execute,

    Code:
    SELECT     current_fl_advisor AS Advisor, COUNT(orders) AS Orders, SUM(Total) AS Lines, CONVERT(DECIMAL(18, 2), AVG(ISNULL(Completion, 0))) 
                          AS Completion
    FROM         (SELECT     orders, Expr2 + Expr3 + Expr4 + Expr5 + Expr6 + Expr7 + Expr8 AS Total, CONVERT(DECIMAL(18, 2), NULLIF (CompleteTotal, 0) * 100) 
                                                  / (Expr2 + Expr3 + Expr4 + Expr5 + Expr6 + Expr7 + Expr8) AS Completion, current_fl_advisor
                           FROM          (SELECT     fllog_ref AS orders, customer_name, fl_advisor_status, current_fl_advisor,
                                                                              (SELECT     COUNT(*) AS Expr2
                                                                                FROM          tblFixedLine_cps
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr2,
                                                                              (SELECT     COUNT(*) AS Expr3
                                                                                FROM          tblFixedLine_call
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr3,
                                                                              (SELECT     COUNT(*) AS Expr4
                                                                                FROM          tblFixedLine_call_isdn
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr4,
                                                                              (SELECT     COUNT(*) AS Expr5
                                                                                FROM          tblFixedLine_orderdetails_broadband
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr5,
                                                                              (SELECT     COUNT(*) AS Expr6
                                                                                FROM          tblFixedLine_nongeo
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr6,
                                                                              (SELECT     COUNT(*) AS Expr7
                                                                                FROM          tblFixedLine_private
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr7,
                                                                              (SELECT     COUNT(*) AS Expr8
                                                                                FROM          tblFixedLine_other
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref)) AS Expr8,
                                                                              (SELECT     COUNT(*) AS Expr9
                                                                                FROM          tblFixedLine_cps AS tblFixedLine_cps_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr10
                                                                                FROM          tblFixedLine_call AS tblFixedLine_call_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr11
                                                                                FROM          tblFixedLine_call_isdn AS tblFixedLine_isdn_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr12
                                                                                FROM          tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr13
                                                                                FROM          tblFixedLine_nongeo AS tblFixedLine_nongeo_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr14
                                                                                FROM          tblFixedLine_private AS tblFixedLine_private_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr15
                                                                                FROM          tblFixedLine_other AS tblFixedLine_other_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Order Complete')) +
                                                                              (SELECT     COUNT(*) AS Expr16
                                                                                FROM          tblFixedLine_cps AS tblFixedLine_cps_2
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) +
                                                                              (SELECT     COUNT(*) AS Expr17
                                                                                FROM          tblFixedLine_call AS tblFixedLine_call_2
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) +
                                                                              (SELECT     COUNT(*) AS Expr18
                                                                                FROM          tblFixedLine_call_isdn AS tblFixedLine_isdn_2
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) +
                                                                              (SELECT     COUNT(*) AS Expr19
                                                                                FROM          tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband_1
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) +
                                                                              (SELECT     COUNT(*) AS Expr20
                                                                                FROM          tblFixedLine_nongeo AS tblFixedLine_nongeo_2
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) +
                                                                              (SELECT     COUNT(*) AS Expr21
                                                                                FROM          tblFixedLine_private AS tblFixedLine_private_2
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) +
                                                                              (SELECT     COUNT(*) AS Expr22
                                                                                FROM          tblFixedLine_other AS tblFixedLine_other_2
                                                                                WHERE      (fllog_ref = tblFixedLine_order_summary_1.fllog_ref) AND (status = 'Cancelled')) AS CompleteTotal
                                                   FROM          tblFixedLine_order_summary AS tblFixedLine_order_summary_1
                                                   WHERE      (status IS NULL) AND (current_fl_advisor IS NOT NULL)) AS derivedtbl_2) AS derivedtbl_1
    GROUP BY current_fl_advisor
    ORDER BY LInes DESC
    I have set this up as a stored procedure but still it doesnt execute and my ASP.net page times out before it is able to render.

    Does anyone have any friendly advise as to how i can simplify the above and speed up its execution time?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have the appropriate indexes defined on all those tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    32
    I am not sure what you mean by appropriate but each table has a primary key which is an Identity Int.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What are the three tables with the most rows and how many rows are there in them?

    Rudy is correct, however we should check just how many tables (it might well be all yet!) will benefit from indexes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stephena View Post
    I am not sure what you mean by appropriate but each table has a primary key which is an Identity Int.
    that won't be enough

    each of the subqueries joins a different table to the main one

    these tables should all have an index on the join column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well most of the time depedning on density and tipping points.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus View Post
    well most of the time depedning on density and tipping points.
    Agreed. Covering the query with the Status column would round this problem off if it is indeed a problem.

    Of course, since the OP doesn't appear to know too much about indexes, we should point out that there is an overhead for inserts and possibly updates to maintain these.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Your inner-most query could be reduced by doing the following. You'll have to change the way you calculate your Complete Total value, but this reduces the number of your table scans from 24 to 8.

    Code:
    SELECT	fllog_ref AS orders,
    		customer_name,
    		fl_advisor_status,
    		current_fl_advisor,
    		(
    		SELECT	COUNT(*) AS Expr2,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr9,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr16
    		FROM	tblFixedLine_cps
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr2,
    		(
    		SELECT	COUNT(*) AS Expr3,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr10,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr17
    		FROM	tblFixedLine_call
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr3,
    		(
    		SELECT	COUNT(*) AS Expr4,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr11,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr18
    		FROM	tblFixedLine_call_isdn
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr4,
    		(
    		SELECT	COUNT(*) AS Expr5,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr12,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr19
    		FROM	tblFixedLine_orderdetails_broadband
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr5,
    		(
    		SELECT	COUNT(*) AS Expr6,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr13,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr20
    		FROM	tblFixedLine_nongeo
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr6,
    		(
    		SELECT	COUNT(*) AS Expr7,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr14,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr21
    		FROM	tblFixedLine_private
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr7,
    		(
    		SELECT	COUNT(*) AS Expr8,
    				COUNT(
    					case status
    						when 'Order Complete' then 1
    						else 0
    					end
    				) AS Expr15,
    				COUNT(
    					case status
    						when 'Cancelled' then 1
    						else 0
    					end
    				) AS Expr22
    		FROM	tblFixedLine_other
    		WHERE	fllog_ref = tblFixedLine_order_summary_1.fllog_ref
    		) AS Expr8
    FROM	tblFixedLine_order_summary AS tblFixedLine_order_summary_1
    WHERE	status IS NULL
    		AND current_fl_advisor IS NOT NULL
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    The code I just submitted will not work--sorry bout that.

    But there is a solid germ of an idea there.

    Making each of those eight subqueries grouping on [fllog_ref] will allow you to use the idea and only make eight passes on the tables, versus 24.
    Last edited by PracticalProgram; 02-15-10 at 13:14.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Further refinement....

    Code:
    SELECT	current_fl_advisor AS Advisor,
    		COUNT(orders) AS Orders,
    		SUM(Total) AS Lines,
    		CONVERT(DECIMAL(18, 2),
    		AVG(ISNULL(Completion, 0))) AS Completion
    FROM	(
    		SELECT	orders,
    				Expr2 + Expr3 + Expr4 + Expr5 + Expr6 + Expr7 + Expr8 AS Total,
    				CONVERT(DECIMAL(18, 2),
    				NULLIF (CompleteTotal, 0) * 100) / (Expr2 + Expr3 + Expr4 + Expr5 + Expr6 + Expr7 + Expr8) AS Completion,
    				current_fl_advisor
    		FROM	(
    				SELECT	fllog_ref AS orders,
    						customer_name,
    						fl_advisor_status,
    						current_fl_advisor,
    						Query1.Expr2,
    						Query2.Expr3,
    						Query3.Expr4,
    						Query4.Expr5,
    						Query5.Expr6,
    						Query6.Expr7,
    						Query7.Expr8,
    						Query1.Expr9 + Query2.Expr10 + Query3.Expr11 + Query4.Expr12 + Query5.Expr13 + Query6.Expr14 + Query7.Expr15 + Query1.Expr16 + Query2.Expr17 + Query3.Expr18 + Query4.Expr19 + Query5.Expr20 + Query6.Expr21 + Query7.Expr22 AS CompleteTotal
    				FROM	tblFixedLine_order_summary AS tblFixedLine_order_summary_1
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr2,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr9,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr16
    						FROM	tblFixedLine_cps
    						GROUP
    						BY		fllog_ref
    						) AS Query1 on
    							Query1.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr3,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr10,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr17
    						FROM	tblFixedLine_call
    						GROUP
    						BY		fllog_ref
    						) AS Query2 on
    							Query2.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr4,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr11,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr18
    						FROM	tblFixedLine_call_isdn
    						GROUP
    						BY		fllog_ref
    						) AS Query3 on
    							Query3.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr5,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr12,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr19
    						FROM	tblFixedLine_orderdetails_broadband
    						GROUP
    						BY		fllog_ref
    						) AS Query4 on
    							Query4.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr6,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr13,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr20
    						FROM	tblFixedLine_nongeo
    						GROUP
    						BY		fllog_ref
    						) AS Query5 on
    							Query5.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr7,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr14,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr21
    						FROM	tblFixedLine_private
    						GROUP
    						BY		fllog_ref
    						) AS Query6 on
    							Query6.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				inner
    				join	(
    						SELECT	fllog_ref,
    								COUNT(*) AS Expr8,
    								COUNT(
    									case status
    										when 'Order Complete' then 1
    										else 0
    									end
    								) AS Expr15,
    								COUNT(
    									case status
    										when 'Cancelled' then 1
    										else 0
    									end
    								) AS Expr22
    						FROM	tblFixedLine_other
    						GROUP
    						BY		fllog_ref
    						) AS Query7 on
    							Query1.fllog_ref=tblFixedLine_order_summary_1.fllog_ref
    				WHERE	status IS NULL
    						AND current_fl_advisor IS NOT NULL
    				) AS derivedtbl_2
    		) AS derivedtbl_1
    GROUP
    BY		current_fl_advisor
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good spot Ken - with all those tables I missed repeats. That should be first stop before adding indexes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2009
    Posts
    32
    Quote Originally Posted by r937 View Post
    that won't be enough

    each of the subqueries joins a different table to the main one

    these tables should all have an index on the join column
    Could you give me an example of this in practice?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stephena View Post
    Could you give me an example of this in practice?
    sure

    CREATE INDEX nongeo_fllog_ref_ix ON tblFixedLine_nongeo ( fllog_ref )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2009
    Posts
    32
    Thanks Ken i just ran your query through SQL management Studio 2005 and got

    Msg 209, Level 16, State 1, Line 1
    Ambiguous column name 'fllog_ref'.

  15. #15
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Hate to say, but you are going to have to debug that one. I don't have a copy of your database so I was just editing your code in notepad.

    I suspect the problem is with the fllog_ref reference in the following section of the code. Basically, the seven subqueries all output a column with that name and SQL is asking 'which of the seven do you want me to refer-to?'

    Code:
    				current_fl_advisor
    		FROM	(
    				SELECT	fllog_ref AS orders,
    						customer_name,
    						fl_advisor_status,
    						current_fl_advisor,
    						Query1.Expr2,
    						Query2.Expr3,
    						Query3.Expr4,
    						Query4.Expr5,
    						Query5.Expr6,
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •