Solved

SQL query

Posted on 2013-12-16
30
702 Views
Last Modified: 2014-01-07
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

0
Comment
Question by:sqldba2013
  • 11
  • 6
  • 3
  • +5
30 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39723130
>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.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39723135
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.
0
 

Author Comment

by:sqldba2013
ID: 39723156
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.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39723160
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.
0
 

Author Comment

by:sqldba2013
ID: 39723163
Execution plan for above query:

Index seek (Non-Clustered)
BI_Custmon (_dta_index_BI_CUSTMON_8_565577053__K22_1)
Cost 56%
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39723173
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?
0
 

Author Comment

by:sqldba2013
ID: 39723186
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.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39723207
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.
0
 

Author Comment

by:sqldba2013
ID: 39723218
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
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39723223
Sample records from which table(s)? Nothing seems to match there?
0
 

Author Comment

by:sqldba2013
ID: 39723227
CPOA: Inquiries,[CPO Number]
bi_custmon: [customer po]
tempHW : temp table
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39723237
Sorry I don't understand. Unfortunately I don't have any other ideas.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39725020
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') )
0
 

Author Comment

by:sqldba2013
ID: 39726000
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39727245
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.
0
 

Author Comment

by:sqldba2013
ID: 39728450
Please find the attached index definitions.
BI-CUSTMON.sql
CPOA.sql
MicroWave-DB.sql
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39733499
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.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39733514
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?
0
 

Author Comment

by:sqldba2013
ID: 39733545
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
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 39733687
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.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39734569
@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.
0
 

Author Comment

by:sqldba2013
ID: 39735792
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
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39735815
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

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 39736621
Done some rewrites on some of the code.  

Please see below -- you may want to run a tool to compare this text to the original script to see all the changes.

The big one is a CLUSTERED index on #tempHW.



IF object_id('tempdb..#tempHW') IS NOT NULL
BEGIN
  DROP TABLE #tempHW
END

CREATE TABLE #tempHW
(
      [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] AS CAST([Sales Order $ to date]-[Invoice $ to date] AS DECIMAL(18,2)), --$CHANGE!!
      [Comments] VARCHAR(2000),
      SalesOrderCount INT,InvoiceOrderCount INT
)
--$I

CREATE CLUSTERED INDEX ic_InqNum ON #tempHW ( [Inquiry Number] ) WITH ( FILLFACTOR = 99 );

;


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;

--$d 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]=(SELECT [UserInfo].[Title] FROM react.dbo.[UserInfo] with(nolock) WHERE MWDB.[Assigned ASR] = [UserInfo].[ID]),
            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]

--update #tempHW with summary info derived from CPOA--------------------------------------------------------------------
            ;WITH temp AS (
            SELECT CPON.Inquiries,CPON.[PO Number],
                   max(CPON.Revision) AS MaxRevision, max(CPON.Amount) AS CPOAmount, max(CPON.[Received Date]) AS ReceivedDate
            FROM bcamdb.dbo.CPOA CPON WITH(NOLOCK)            
            INNER JOIN #tempHW WITH(NOLOCK) on CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets --$c
            and  CPON.[PO Number]=#tempHW.[CPO Number] GROUP by Inquiries,[PO Number])
UPDATE  #tempHW
            SET [CPO Rev] = temp.MaxRevision,
                [CPO Value] = temp.CPOAmount,
            [CPO date received in ONE] = temp.ReceivedDate                
            FROM temp WHERE temp.Inquiries= '[' + #tempHW.[Inquiry Number] + ']' and [PO Number]=#tempHW.[CPO Number]

--update #tempHW with selected CUST data--------------------------------------------------------------------------------

UPDATE      #tempHW SET [SO Number]= LTRIM(RTRIM(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 --$c
            INNER JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%'

--update #tempHW with selected BAKSALES data----------------------------------------------------------------------------
      
            ;WITH      temp AS (
            SELECT BAKSales.[SO],
                sum(BAKSales.[SO Net Value]) AS SOTOTAL_SUM,
                count(BAKSales.[SO Net Value]) AS SOTOTAL_COUNT                
            FROM bcamdb.dbo.SAP_ZVBAK BAKSales WITH(NOLOCK)
            INNER JOIN #tempHW WITH(NOLOCK) on BAKSales.[SO] = #tempHW.[SO Number] GROUP BY BAKSales.[SO])

UPDATE  #tempHW
            SET [Sales Order $ to date] = temp.SOTOTAL_SUM,
                SalesOrderCount = temp.SOTOTAL_COUNT
            FROM temp WHERE temp.[SO Number]= #tempHW.[SO Number]

--update #tempHW with selected SAP_ZVBRP data---------------------------------------------------------------------------
            
            ;WITH temp AS (
            SELECT  SAP_ZVBRPINV.[SO],
                sum(SAP_ZVBRPINV.[Net Value]) AS SOTOTAL_SUM,
                count(SAP_ZVBRPINV.[Net Value]) AS SOTOTAL_COUNT,
                max(SAP_ZVBRPINV.[Billing Document]) AS [Billing Document],
                max(SAP_ZVBRPINV.[Created On]) AS [Created On]
            FROM [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV WITH(NOLOCK)
              INNER JOIN #tempHW WITH(NOLOCK) on SAP_ZVBRPINV.[SO] = #tempHW.[SO Number] GROUP BY SAP_ZVBRPINV.[SO])

UPDATE  #tempHW
            SET [Invoice $ to date] = temp.SOTOTAL_SUM,
                InvoiceOrderCount = temp.SOTOTAL_COUNT,
                [Invoice Number] = temp.[Billing Document],
                [Invoice Date]= temp.[Created On]
            FROM temp WHERE temp.[SO Number]= #tempHW.[SO Number]

--update #tempHW itself-------------------------------------------------------------------------------------------------

UPDATE #tempHW
            SET [SO Number] = CASE WHEN SalesOrderCount > 1 THEN '' ELSE [SO Number] END,
            [SO Date] = CASE WHEN SalesOrderCount > 1 THEN '' ELSE [SO Date] END,
            [Network #] = CASE WHEN SalesOrderCount > 1 THEN '' ELSE [Network #] END,
            [Invoice Number] = CASE WHEN InvoiceOrderCount > 1 THEN '' ELSE [Invoice Number] END,
            [Invoice Date] = CASE WHEN InvoiceOrderCount > 1 THEN '' ELSE [Invoice Date] END,
            [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 with selected requests data----------------------------------------------------------------------------

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]


------------------------------------------------------------------------------------------------------------------------

SELECT
            ID1,Market,Location,[Request Date],Requestor,[HW Quote Generated By],NOVA,[Quote Name],Type1,[Service Type],
            [Quote Type],[Site Description],CASPR,[CASPR Target],[FA Code],Cabinet,[CRM ID],[Contract #],Notes,Batch,
            [Final Quote Submitted],[Quote Need by Date],[Urgent Request],[Business Case],[Assigned ASR],[TAQOS Status],
            [Total of Lines],[Total Quote Value],[TAQOS Submitted Date],[E-mail Submitted Date],[REACT Status],[REACT Number],
            [REACT Submitted Date],[REACT Release Date],[Total Quote Value] as [Quote Value in REACT],[Inquiry Number],
            [CPO Number],[CPO Rev],[CPO value],[CPO date received in ONE],[SO Number],[Sales Order $ to date] as [Sales Order Value to Date],
            [SO Date],[Network #],MS,[Invoice Number],[Invoice $ to date] as[Invoice Value to Date], [Invoice Date],
            [Invoice $ Delta],[Comments]
FROM #tempHW
0
 

Author Comment

by:sqldba2013
ID: 39737343
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?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39737836
Then @arrontomosky is right, this system seems to have enormous bottlenecks. The row numbers you have told as are not big.

query plan
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.
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 39738254
Id Start with using resource monitor and watch ram and disk queue. Some SQL tracing things were already listed above.
0
 

Author Closing Comment

by:sqldba2013
ID: 39764082
--
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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

14 Experts available now in Live!

Get 1:1 Help Now