Solved

SQL Query tuning

Posted on 2013-11-11
7
164 Views
Last Modified: 2013-11-17
The attached query is taking around 1 hr 20 min time to return 20,000 records and I have created index but still it was taking more time to give output.

In execution plan, stream aggregate (aggregate) cost is showing 71%. Please suggest how to reduce the attached query execution time (rewrite Joins, index creation..if any other options).
Query
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39638905
looks like this part of the SQL might be taking most part of the time... If you can remove the substring, replace functions and case statement from the where clause by getting the polished data columns that you need in a temp table or a CTE and using those (CTE/Temp table) columns in your where clause might help you increase the performance

something like we suggested in this post here

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28285583.html

FROM [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV

      WHERE SAP_ZVBRPINV.SO LIKE LTRIM(CAST(INV.[SO] as varchar(50)))+'%') as decimal (18,2)) AS VARCHAR(50)) AS [Invoice $ to date],

      CASE  WHEN COUNT(INV.SO)>0 THEN

      ''

      ELSE

      INV.[Billing Document] END AS [Invoice Number],

      CASE  WHEN COUNT(INV.SO)>0 THEN

      ''

      ELSE

      CAST (INV.[Billing Date] AS date) END AS [Invoice Date],

 

      /********************DATA 7***********************/

      CUST.[SO Net Value] - INV.[Line Net Value] AS [Invoice $ Delta],

      CONVERT(VARCHAR(2000),PRA.[comments]) As [Comments]

FROM 

		REACT.dbo.ps_requests_asr PRA WITH(NOLOCK)   

      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH(NOLOCK) ON REPLACE(REPLACE(CPON.Inquiries, '[', ''),']', '') = PRA.inq_num   

      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CPON.[PO Number]  = SUBSTRING(CUST.[Customer PO], 1, LEN(CPON.[PO Number]))

      LEFT JOIN [BCAMDB].dbo.BI_INVOICING INV WITH(NOLOCK) ON CUST.SO=INV.SO 

      LEFT JOIN [BCAMDB].dbo.SAP_ZVBAK BAK WITH(NOLOCK) ON CUST.SO=BAK.SO 

      LEFT JOIN [BCAMDB].dbo.SAP_ZVBRP BRP WITH(NOLOCK) ON INV.SO=BRP.SO

and ISNULL(PRA.inq_num,'') <> '' 

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39639145
I have tried with temp table but i didn't get any improvement in execution time.

How to reduce "stream aggregate (aggregate) " cost?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39640153
I don't see any query plan, so I can't follow what you're saying.

But, for any query, I can tell you that such a massive GROUP BY will destroy your performance.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:sqldba2013
ID: 39640958
Please find the attached query execution plan
execution-plan.sqlplan
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39647121
There are 2 correlated subqueries in the select clause.
Both use "TOP 1", removing the TOP 1 would help immediately.

Note as it stands these correlated subqueries are causing substantial "rebinds" and the top 1 is causing very expensive sorts.

Replacing both these inefficient subquery types with more efficient join (perhaps as subqueries) is a top prioriity.

-------------
These joins involving functions are inefficient. Is all that processing absolutely necessary?
      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH (NOLOCK)
            ON REPLACE(REPLACE(CPON.Inquiries, '[', ''), ']', '') = PRA.inq_num -- *1

      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH (NOLOCK)
            ON CPON.[PO Number] = SUBSTRING(CUST.[Customer PO], 1, LEN(CPON.[PO Number])) --*2
           
Ideally there is no need for such manipulation in joining tables.

If you cannot get rid of those manipulations can you minimize them?      
*1
REACT.dbo.ps_requests_asr PRA appears to be the smaller of the 2 tables
could you add '[' & ']' to  PRA.inq_num istead?

      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH (NOLOCK)
            ON RCPON.Inquiries = '[' + PRA.inq_num + ']'
           
*2
      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH (NOLOCK)
            ON  CUST.[Customer PO] LIKE CPON.[PO Number] + '%'
-----------
this line:
                  AND ISNULL(PRA.inq_num, '') <> ''
I think should be:
                  WHERE PRA.inq_num IS NOT NULL

right now it is being handled as part of a  left join, and that join appears redundant (next)
-------------

You join a table but then do not use it in select, group or order clauses
      LEFT JOIN [BCAMDB].dbo.SAP_ZVBRP BRP WITH (NOLOCK)
            ON INV.SO = BRP.SO

-----------
basically:

You have to find a better way to arrive at those summed values.

Only you will know enough about the data to know if you can make those joins more efficient - but I would be trying to ensure I had better ways to join the tables.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39647155
I really don't have any way of knowing just how far "off" this will be, but this is what I would be aiming at.
Joins without functions
smaller subqueries doing the summation of numbers
and, if I could get all the needed summarization done in smaller subqueries, then do away with the final big group by
SELECT

      /********************DATA 1********************/

      PRA.region                                                                    AS [Region]
    , PRA.market                                                                    AS [Market]
    , PRA.quote_name                                                                AS [Quote Name]
    , REPLACE(PRA.[Ship_To_No], '?', '')                                            AS [Ship To]
    , PRA.site_id                                                                   AS [Site ID]
    , PRA.caspr                                                                     AS [CASPR]
    , PRA.fa_loc                                                                    AS [FA Location]
    , PRA.acr                                                                       AS [ACR]
    , PRA.prg_cpm                                                                   AS [CPM]
    , PRA.csr                                                                       AS [CSR]
    , PRA.Quote_Requestor                                                           AS [Quote Requester]
    , PRA.Quote_Receivers                                                           AS [Quote Receivers]
    , PRA.created_by                                                                AS [Created By]
    , PRA.assigned_to                                                               AS [Assigned ASR]
    , PRA.Quote_Need_by_date                                                        AS [Quote Need by date]
    , PRA.quote_type                                                                AS [Type of Request]
    , PRA.New_Quote_Type                                                            AS [Type of Quote if New]
    , PRA.serv_type                                                                 AS [Service Type]
    , PRA.prog_type                                                                 AS [Program Type]
    , PRA.fire_code                                                                 AS [Fire Codes]
    , PRA.contract_num                                                              AS [MUS Contract #]
    , PRA.crm_id                                                                    AS [CRM ID]
    , PRA.is_urgent                                                                 AS [Urgent Request]
    , PRA.b_case                                                                    AS [Business Case]
    , PRA.scope_o_work                                                              AS [Scope of Work]

      /********************DATA 2********************/

    , PRA.[status]                                                                  AS [TAQOS Status]
    , PRA.hard_incl                                                                 AS [Hardware Included]
    , PRA.num_lines                                                                 AS [Total of Lines]
    , PRA.Quote_Value                                                               AS [Total Quote Value]
    , PRA.create_date                                                               AS [TAQOS Submitted Date]
    , PRA.Mail_Received                                                             AS [E-mail Submitted Date]


      /********************DATA 3********************/

    , PRA.REACT_Status                                                              AS [REACT Status]
    , PRA.REACT_Number                                                              AS [REACT Number]
    , PRA.REACT_Submitted_Date                                                      AS [REACT Submitted Date]
    , PRA.REACT_Release_Date                                                        AS [REACT Release Date]
    , PRA.inq_num                                                                   AS [Inquiry Number]


      /********************DATA 4********************/

    , CPON.[PO Number]                                                              AS [CPO Number]
    , CPON.Revision                                                                 AS [CPO Rev]
    , CPON.Amount                                                                   AS [CPO Value]
    , CPON.[CPOA Register date]                                                     AS [CPO date received in ONE]


      /********************DATA 5********************/

    , '$' + ' ' + CAST(CAST((
            BAK.[SO Net Value SUM]
          + '%'
      )
      AS decimal(18, 2)) AS nvarchar(50))                                           AS [Sales Order $ to date]
    , BAK.SO                                                                        AS [SO Number]
    , CAST(CUST.[SO Created On] AS date)                                            AS [SO Date]
    , CASE WHEN COUNT(CUST.SO) > 0 THEN '' ELSE CUST.Network END                    AS [Network #]


      /********************DATA 6********************/

    , '$' + ' ' + CAST(CAST((
            BRP.[Line Net Value SUM]
           + '%'
      )
      AS decimal(18, 2)) AS varchar(50))                                            AS [Invoice $ to date]
    , CASE WHEN COUNT(INV.SO) > 0 THEN '' ELSE INV.[Billing Document] END           AS [Invoice Number]
    , CASE WHEN COUNT(INV.SO) > 0 THEN '' ELSE CAST(INV.[Billing Date] AS date) END AS [Invoice Date]


      /********************DATA 7***********************/

    , CUST.[SO Net Value] - INV.[Line Net Value]                                    AS [Invoice $ Delta]
    , CONVERT(varchar(2000), PRA.[comments])                                        AS [Comments]

FROM REACT.dbo.ps_requests_asr PRA WITH (NOLOCK)

      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH (NOLOCK)
            ON PRA.inq_num = CPON.Inquiries           /* NB! assumes no functions needed */

      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH (NOLOCK)
            ON CPON.[PO Number] = CUST.[Customer PO]  /* NB! assumes no functions needed */

      LEFT JOIN [BCAMDB].dbo.BI_INVOICING INV WITH (NOLOCK)
            ON CUST.SO = INV.SO

      LEFT JOIN (
                 select
                        SO
                      , SUM([SO Net Value]) as [SO Net Value SUM]
                 from [BCAMDB].dbo.SAP_ZVBAK WITH (NOLOCK)
                 group by SO
                ) BAK
            ON CUST.SO = BAK.SO

      LEFT JOIN (
                 select
                        SO
                      , SUM([Line Net Value]) as [Line Net Value SUM]
                 from BCAMDB].dbo.SAP_ZVBRP WITH (NOLOCK)
                 group by SO
                ) BRP
            ON INV.SO = BRP.SO

WHERE PRA.inq_num IS NOT NULL

ORDER BY
      PRA.inq_num, CPON.Revision
;

Open in new window

0
 

Author Closing Comment

by:sqldba2013
ID: 39655455
--
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question