?
Solved

SQL Query tuning

Posted on 2013-11-11
7
Medium Priority
?
167 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

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 49

Accepted Solution

by:
PortletPaul earned 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 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