Link to home
Start Free TrialLog in
Avatar of sqldba2013
sqldba2013

asked on

SQL query

The below sql query is taking very long time (20 minutes for 600 records) and I have created required indexes on tables and stil it was taking long time for execution.

Please advise how to optimize/speed-up below query.

UPDATE tempHW 
SET [SO Number]= CUST.[SO] 
	FROM tempHW INNER JOIN [BCDB].dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'    
	INNER JOIN [BCDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE tempHW.[CPO Number] + '%'

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'  
In order for that index to be used, it has to be a Search Argument (SARG)-able value, which afaik a text concatenation is not.

>ON CUST.[Customer PO] LIKE tempHW.[CPO Number] + '%'
Also, any wildcard will be slow, and I'm guessing a wildcard in a JOIN will really be slow.
JimHorns points are valid, but unless your table has a few million records I wouldn't expect it to take 20mins even with string concatination and wildcard queries.

Sometimes an inner join can be the slower way to solve the problem. Instead you can do a left join and then in the where clause exclude any where the joined table are null (which is in effect an inner join).

How are you creating table tempHW? It appears to be a temporary table, therefore you should be able to create the column '[' + tempHW.[Inquiry Number] + ']' as a natural column which will avoid having to transfer it for the join.

The last thing to do is display the expected execution plan as that should give you a tip as to where the time is being spent and how to optimise it.
Avatar of sqldba2013
sqldba2013

ASKER

Thanks for suggestions.

Yes, tempHW is temp table.

I have modified query with inner join (removed like operator) and i got the output for [SO number] as a NULL (output is wrong).

UPDATE tempHW 
SET [SO Number]= CUST.[SO] 
	FROM tempHW INNER JOIN [BCDB].dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'    
	INNER JOIN [BCDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] = tempHW.[CPO Number] + '%'

Open in new window


Pls advise.
You can't remove the like operator without removing the '%' as well. I didn't have any suggestions for how to change that particular clause.
Execution plan for above query:

Index seek (Non-Clustered)
BI_Custmon (_dta_index_BI_CUSTMON_8_565577053__K22_1)
Cost 56%
Hmmm, that looks like the problem then. Is there a lot of data in that table? Looks like you need an index on [Customer PO].

Why were you using a like statement anyway? Do you really want to match [Customer PO] with [CPO Number] + '%' i.e. followed by anything? Shouldn't it be more exact that that?
I have already created index on [Customer PO].

CREATE NONCLUSTERED INDEX [_dta_index_BI_CUSTMON_8_565577053__K22_1] ON [dbo].[BI_CUSTMON]
(
	[Customer PO] ASC
)
INCLUDE ([SO]) 

Open in new window

I have to match [Customer PO] with [CPO Number] because DB is not having proper data.
Yeah, as JimHorn said, it won't use the index while you are using a like compare.

You must have a massive amount of data in your table for it to take that long. I can't think of anything else to try sorry - its the sort of problem where I need the real data so I can try various things which come to mind - but I don't have any further ideas from what I know so far.
Pls find the sample records

Inquiries            CPO Number      Customer PO
=======================================
[10492511]      4415867             4989289 10050042
[10499517]      4415869             4989289 10050042
[10499454]      4415870             4989289 10050042
[10492511]      4415873             4989289 10050042
[10492517]      4415874             4989289 10050042
[10492512]      4415876             4990579 10110787
[10492513]      4415881             4990579 10110787
[10492519]      4415882             4990579 10110787
[10492520]      4415895             4990579 10110787
[10492511]      4415896             4990579 10110787
Sample records from which table(s)? Nothing seems to match there?
CPOA: Inquiries,[CPO Number]
bi_custmon: [customer po]
tempHW : temp table
Sorry I don't understand. Unfortunately I don't have any other ideas.
Would you post?:

all index definitions on all tables;

the xml version of the query plan;

the approx. number of rows in each table; for example, using this t-sql:
    select count(*) from tempHW with (nolock)
    select object_name(object_id, DB_ID('BCDB')), row_count
    from BCDB.sys.dm_db_partition_stats dps
    where
        object_id in ( object_id('BCDB.dbo.CPOA'), object_id('BCDB.dbo.BI_CUSTMON') )
Hi ScottPletcher,

I am herewith attaching entire query with execution plan. Please review the query and advise me and we want to reduce the execution time.

#tempSVC : it returns 1825 rows in 5 seconds
#tempHW : it returns 220000 rows in 25 minutes
Union operator : It returns 600 rows in 1 hr 40 min

Thanks in advance.
SQLQuery.sql
Plan.sqlplan
Very helpful, but I still need the table and index definitions for the permanent tables, including at least:

[BCDB].dbo.CPOA CPON
[BCDB].dbo.BI_CUSTMON

and perhaps:

REACT.dbo.[MicroWave DB]

just to be more complete.
Please find the attached index definitions.
BI-CUSTMON.sql
CPOA.sql
MicroWave-DB.sql
If you can, send sqls to reproduce the issue. Include sql to populate tables.
Add the query and the expected output.

For a new contributing expert, he needs to understand the issue without going through all the discussions.
I would start by finding out the bottleneck: is it finding the records, or updating them. If you run
select 1
FROM tempHW 
INNER JOIN [BCDB].dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'    
INNER JOIN [BCDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE tempHW.[CPO Number] + '%'

Open in new window

is it as slow as UPDATE?
As i said above,
#tempSVC : it returns 1825 rows in 5 seconds
#tempHW : it returns 220000 rows in 25 minutes
Union operator : It returns 600 rows in 1 hr 40 min

I believe, #tempHW table or Union operator is causing the performance issue.

I am herewith attaching entire query and please advise me on this issue (how to reduce the execution time).
SQLQuery.sql
At first glance a select from a temp table with 220k rows taking 25 min is rediculous. That's usually the server out of ram and swapping to disk causing a chain reaction of slowness.
@arrontomosky is imho right.
To test it, run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to start with cold buffers. Run your query two or three times. How does execution time changes? See SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer. Caveat: This should not be done on production servers under load.
Also use SET STATISTICS IO ON; to see whether we have too much scans and physical reads.

@jimhorn is right about the non-sargeable nature of your JOIN's on your temp table. To use indices here, create an additional inquery column which includes the square brackets. Create an index on this column and use it in the subsequent JOIN's. This should speed up the bunch of 9% queries. Also creating the index after filling the table is normally faster.

CREATE TABLE #tempSVC
(
	[Inquiry Number] VARCHAR(255), [CPO Number] VARCHAR(50),
	InquiryNumberWithSquaredBrackets VARCHAR(257),
	[ID1] float,[Market] VARCHAR(150),[Location] VARCHAR(50),[Request Date] DATETIME,Requestor VARCHAR(510),[HW Quote Generated By] VARCHAR(510),
	[NOVA] VARCHAR(510),[Quote Name] VARCHAR(150),[Type1] VARCHAR(510),[Service Type] VARCHAR(510),[Quote Type] VARCHAR(510),
	[Site Description] VARCHAR(510),[CASPR] VARCHAR(50),[CASPR Target] VARCHAR(50),[FA Code]  VARCHAR(510),Cabinet VARCHAR(510),
	[CRM ID] VARCHAR(255),[Contract #] VARCHAR(510),NOTES VARCHAR(510),BATCH float(8),[Final Quote Submitted] DATETIME,
	[Quote Need by date] DATETIME,[Urgent Request] bit,[Business Case] VARCHAR(300),[Assigned ASR] VARCHAR(300),[TAQOS Status] VARCHAR(50),
	[Total of Lines]  FLOAT,[Total Quote Value] MONEY,[TAQOS Submitted Date] DATETIME,[E-mail Submitted Date]  DATETIME,[REACT Status]  VARCHAR(255),
	[REACT Number] VARCHAR(50),[REACT Submitted Date] DATETIME,[REACT Release Date] DATETIME,[Quote Value in REACT] money,
	[CPO Rev] VARCHAR(50),[CPO Value] float,[CPO date received in ONE] DATETIME,[SO Number] VARCHAR(50),
	[Sales Order $ to date] DECIMAL(18,2),[SO Date] DATETIME,[Network #] VARCHAR(250),MS VARCHAR(510),[Invoice Number] VARCHAR(50),
	[Invoice $ to date] decimal(18,2),[Invoice Date] DATETIME,[Invoice $ Delta] DECIMAL(18,2),[Comments] VARCHAR(2000),	
	SalesOrderCount INT,InvoiceOrderCount INT
);

INSERT INTO #tempSVC ([Inquiry Number], InquiryNumberWithSquaredBrackets, [CPO Number] )
SELECT	MWDB.[Inquiry Number],
        '[' + MWDB.[Inquiry Number] + ']',
	[PO Number]
FROM	REACT.dbo.[MicroWave DB] AS MWDB WITH ( NOLOCK ) 
	LEFT JOIN bcamdb.dbo.CPOA WITH ( NOLOCK ) ON Inquiries = '[' + MWDB.[Inquiry Number] + ']' 
WHERE	MWDB.[Quote Type] = 'SVC'
	AND cpoa.[sales org] = 'AT&T'
	AND NOT MWDB.[Inquiry Number] IS NULL;
	
CREATE NONCLUSTERED INDEX ix_InqNum ON #tempSVC ( [Inquiry Number] ) INCLUDE ( [CPO Number] );
CREATE NONCLUSTERED INDEX ix_InqNum2 ON #tempSVC ( InquiryNumberWithSquaredBrackets ) INCLUDE ( [CPO Number] );

Open in new window


And last but not least: The mixture of inquriy# stored with and without brackets is a bad handling. You should stick to one usage only.
Hi ste5an,

Thanks for your suggestion.

I have modified script as per your suggestion and there is no change in execution time.

Pls review the attached script with execution plan.
temphw.sql
temphw-plan.sqlplan
I think you've got a lot redundant updates. Also using RTRIM(LTRIM()) repeatedly as a non-sargable condition is not that good.

Check this one carefully:
INSERT  INTO #tempHW
        ( [Inquiry Number] ,
          InquiryNumberWithSquaredBrackets ,
          [CPO Number] 
        )
        SELECT  MWDB.[Inquiry Number] ,
                '[' + MWDB.[Inquiry Number] + ']' ,
                [PO Number]
        FROM    REACT.dbo.[MicroWave DB] AS MWDB WITH ( NOLOCK )
                LEFT JOIN bcamdb.dbo.CPOA WITH ( NOLOCK ) ON Inquiries = '[' + MWDB.[Inquiry Number] + ']'
        WHERE   MWDB.[Quote Type] = 'HW'
                AND NOT MWDB.[Inquiry Number] IS NULL;

CREATE NONCLUSTERED INDEX ix_InqNum ON #tempHW ( [Inquiry Number] ) INCLUDE ( [CPO Number] );
CREATE NONCLUSTERED INDEX ix_InqNum2 ON #tempHW ( InquiryNumberWithSquaredBrackets ) INCLUDE ( [CPO Number] );

UPDATE  #tempHW
SET     ID1 = MWDB.ID1 ,
        [Location] = MWDB.Location ,
        [Market] = MWDB.market ,
        [Quote Name] = MWDB.[quote name] ,
        [CASPR] = MWDB.caspr ,
        [CASPR Target] = MWDB.[CASPR Target] ,
        [Quote Type] = MWDB.[Quote Type] ,
        [CRM ID] = MWDB.[crm id] ,
        [Urgent Request] = MWDB.[Urgent Request] ,
        [Business Case] = MWDB.[Business Case] ,
        [TAQOS Status] = MWDB.[TAQOS Status] ,
        [Total of Lines] = MWDB.[Total of Lines] ,
        [Total Quote Value] = MWDB.[Total Quote Value] ,
        [TAQOS Submitted Date] = MWDB.Created ,
        [E-mail Submitted Date] = MWDB.[Mail Date] ,
        [REACT Status] = MWDB.[REACT Status] ,
        [REACT Number] = MWDB.[REACT Number] ,
        [REACT Submitted Date] = MWDB.[REACT Submitted Date] ,
        [REACT Release Date] = MWDB.[REACT Release Date] ,
        [Request Date] = MWDB.[Request Date] ,
        [HW Quote Generated By] = MWDB.[HW Quote Generated By] ,
        [NOVA] = MWDB.[NOVA] ,
        [Type1] = MWDB.[Type1] ,
        [Site Description] = MWDB.[Site Description] ,
        [FA Code] = MWDB.[FA Code] ,
        Cabinet = MWDB.Cabinet ,
        NOTES = MWDB.NOTES ,
        BATCH = MWDB.BATCH ,
        [Final Quote Submitted] = MWDB.[Final Quote Submitted] ,
        [Quote Need by Date] = MWDB.[Quote Needed by Date] ,
        Requestor = MWDB.Requestor ,
        [Service Type] = MWDB.[Service Type] ,
        [Contract #] = MWDB.[Contract #] ,
        [Assigned ASR] = [UserInfo].[Title] ,
        MS = MWDB.MS ,
        Comments = MWDB.Comments
FROM    #tempHW WITH ( NOLOCK )
        INNER JOIN REACT.dbo.[MicroWave DB] MWDB WITH ( NOLOCK ) ON #tempHW.[Inquiry Number] = MWDB.[Inquiry Number]
        LEFT JOIN react.dbo.[UserInfo] WITH ( NOLOCK ) ON MWDB.[Assigned ASR] = [UserInfo].[ID];

WITH    temp
          AS ( SELECT   Inquiries ,
                        [PO Number] ,
                        MAX(Revision) AS MaxRevision
               FROM     bcamdb.dbo.CPOA CPON WITH ( NOLOCK )
                        INNER JOIN #tempHW WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
                                                              AND CPON.[PO Number] = #tempHW.[CPO Number]
               GROUP BY Inquiries ,
                        [PO Number]
             )
    UPDATE  #tempHW
    SET     [CPO Rev] = temp.MaxRevision
    FROM    temp
    WHERE   temp.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
            AND [PO Number] = #tempHW.[CPO Number];

WITH    temp
          AS ( SELECT   Inquiries ,
                        [PO Number] ,
                        MAX(Amount) AS CPOAmount
               FROM     bcamdb.dbo.CPOA CPON WITH ( NOLOCK )
                        INNER JOIN #tempHW WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
                                                              AND CPON.[PO Number] = #tempHW.[CPO Number]
               GROUP BY Inquiries ,
                        [PO Number]
             )
    UPDATE  #tempHW
    SET     [CPO Value] = temp.CPOAmount
    FROM    temp
    WHERE   temp.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
            AND [PO Number] = #tempHW.[CPO Number];

WITH    temp
          AS ( SELECT   Inquiries ,
                        [PO Number] ,
                        MAX([Received Date]) AS ReceivedDate
               FROM     bcamdb.dbo.CPOA CPON WITH ( NOLOCK )
                        INNER JOIN #tempHW WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
                                                              AND CPON.[PO Number] = #tempHW.[CPO Number]
               GROUP BY Inquiries ,
                        [PO Number]
             )
    UPDATE  #tempHW
    SET     [CPO date received in ONE] = temp.ReceivedDate
    FROM    temp
    WHERE   temp.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
            AND [PO Number] = #tempHW.[CPO Number];
	
UPDATE  #tempHW
SET     [SO Number] = RTRIM(LTRIM(CUST.[SO])) ,
        [SO Date] = CAST(CUST.[SO Created On] AS DATE) ,
        [Network #] = CUST.Network
FROM    #tempHW WITH ( NOLOCK )
        INNER JOIN [BCAMDB].dbo.CPOA CPON WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
        INNER JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH ( NOLOCK ) ON CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%';
	 	   
CREATE NONCLUSTERED INDEX ix_InqNum3 ON #tempHW ( [SO Number] );

WITH    temp
          AS ( SELECT   [SO Number] ,
                        COUNT([SO Net Value]) AS SOTOTAL
               FROM     bcamdb.dbo.SAP_ZVBAK BAKSales WITH ( NOLOCK )
                        INNER JOIN #tempHW WITH ( NOLOCK ) ON BAKSales.[SO] = #tempHW.[SO Number]
               GROUP BY [SO Number]
             )
    UPDATE  #tempHW
    SET     [Sales Order $ to date] = temp.SOTOTAL ,
            SalesOrderCount = temp.SOTOTAL
    FROM    temp
    WHERE   temp.[SO Number] = #tempHW.[SO Number];

WITH    temp
          AS ( SELECT   [SO Number] ,
                        SUM([Net Value]) AS SOTOTAL
               FROM     [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV WITH ( NOLOCK )
                        INNER JOIN #tempHW WITH ( NOLOCK ) ON SAP_ZVBRPINV.SO = #tempHW.[SO Number]
               GROUP BY [SO Number]
             )
    UPDATE  #tempHW
    SET     [Invoice $ to date] = temp.SOTOTAL ,
            InvoiceOrderCount = temp.SOTOTAL
    FROM    temp
    WHERE   temp.[SO Number] = #tempHW.[SO Number];
		
UPDATE  #tempHW
SET     [Invoice Number] = [Billing Document] ,
        [Invoice Date] = [Created On]
FROM    [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV WITH ( NOLOCK )
        INNER JOIN #tempHW WITH ( NOLOCK ) ON SAP_ZVBRPINV.SO = #tempHW.[SO Number];

UPDATE  #tempHW
SET     [Invoice $ Delta] = [Sales Order $ to date] - [Invoice $ to date];
		
UPDATE  #tempHW
SET     [SO Number] = '' ,
        [SO Date] = '' ,
        [Network #] = ''
WHERE   SalesOrderCount > 1;
	 
UPDATE  #tempHW
SET     [Invoice Number] = '' ,
        [Invoice Date] = ''
WHERE   InvoiceOrderCount > 1;

UPDATE  #tempHW
SET     [Request Date] = CASE WHEN [Request Date] <= '1900-01-01 00:00:00.000' THEN NULL
                              ELSE [Request Date]
                         END ,
        [Final Quote Submitted] = CASE WHEN [Final Quote Submitted] <= '1900-01-01 00:00:00.000' THEN NULL
                                       ELSE [Final Quote Submitted]
                                  END ,
        [Quote Need by date] = CASE WHEN [Quote Need by date] <= '1900-01-01 00:00:00.000' THEN NULL
                                    ELSE [Quote Need by date]
                               END ,
        [TAQOS Submitted Date] = CASE WHEN [TAQOS Submitted Date] <= '1900-01-01 00:00:00.000' THEN NULL
                                      ELSE [TAQOS Submitted Date]
                                 END ,
        [E-mail Submitted Date] = CASE WHEN [E-mail Submitted Date] <= '1900-01-01 00:00:00.000' THEN NULL
                                       ELSE [E-mail Submitted Date]
                                  END ,
        [REACT Submitted Date] = CASE WHEN [REACT Submitted Date] <= '1900-01-01 00:00:00.000' THEN NULL
                                      ELSE [REACT Submitted Date]
                                 END ,
        [REACT Release Date] = CASE WHEN [REACT Release Date] <= '1900-01-01 00:00:00.000' THEN NULL
                                    ELSE [REACT Release Date]
                               END ,
        [CPO date received in ONE] = CASE WHEN [CPO date received in ONE] <= '1900-01-01 00:00:00.000' THEN NULL
                                          ELSE [CPO date received in ONE]
                                     END ,
        [SO DAte] = CASE WHEN [SO DAte] <= '1900-01-01 00:00:00.000' THEN NULL
                         ELSE [SO DAte]
                    END ,
        [Invoice Date] = CASE WHEN [Invoice Date] <= '1900-01-01 00:00:00.000' THEN NULL
                              ELSE [Invoice Date]
                         END;

UPDATE  #tempHW
SET     [REACT Status] = PR.[status] ,
        [REACT Number] = PR.Req_id ,
        [REACT Submitted Date] = PR.submitted_date ,
        [REACT Release Date] = PR.req_del_date ,
        [Inquiry Number] = PR.mus_inq_num
FROM    #tempHW WITH ( NOLOCK )
        INNER JOIN REACT.dbo.ps_requests PR WITH ( NOLOCK ) ON PR.mus_inq_num = #tempHW.InquiryNumberWithSquaredBrackets;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for rewriting script.

I tried with above query and now it was taking 1 hr 20 minutes for 220000 rows

Old script execution time is 25 minutes for 220000 rows.

Any other suggestions?
Then @arrontomosky is right, this system seems to have enormous bottlenecks. The row numbers you have told as are not big.

User generated image
The insert cost in the temp table is, well, not normal.

On what hardware or VM is this system running? How many concurrent users? You should tell us more about this aspect.
Id Start with using resource monitor and watch ram and disk queue. Some SQL tracing things were already listed above.
--