Solved

SQL Query tuning

Posted on 2013-11-11
7
157 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
7 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
--
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now