Solved

SQL Query tuning

Posted on 2013-11-11
7
160 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
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:ScottPletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

914 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

20 Experts available now in Live!

Get 1:1 Help Now