Update with subquery performance

Hi,
I'm using MS SQL Server 2008 r2.

I'm working on developing a sproc for a report. I haven't been able to get the update quite right and it is taking ~8 minutes to complete.  I see that it is doing full scans on both tables. What might I try to speed this thing up?

There are ~300,000 records in the main table(@Temp).
There are ~13,000 records that will be the output of this sproc(@Results).

Here's an example of what the @Temp data looks like:
agin      loc                    palQty         slocPurp
4303510      02 12 28 6048 N      0      1
4303510      02 11 22 4371 N      0      1
4303510      02 11 26 7260 N      0      1
4303510      02 11 99 0163 C      141      2

And here's @Results:
agin      fillLoc                         rsrvLoc                   qtyRls      qtyFill      qtyRsrv      qtyRm5
4303510      02 12 28 6048 N      02 11 99 0163 C      0      0      141      0
4303510      02 11 22 4371 N      02 11 99 0163 C      0      0      141      0
4303510      02 11 26 7260 N      02 11 99 0163 C      0      0      141      0

And the Sproc:
      DECLARE @RESULTS TABLE( agin      varchar(10),
                                          fillLoc      varchar(20),
                                          rsrvLoc      varchar(20),
                                          qtyRls      float,
                                          qtyFill      float,
                                          qtyRsrv      float,
                                          qtyRm5      float,
                                          qtyTrain      float,
                                          qtyWhse      float,
                                          qtyInc      float,
                                          qtyST18      float
                                          )
                                          
      DECLARE @Temp TABLE( agin      varchar(10),
                                     loc      varchar(20),
                                     slocPurp varchar(5),
                                     palQty float
                                     )
      
      --Bring needed data from tbl_dc11 into a temp table for ease of access.
      INSERT INTO @Temp
      SELECT agin,loc,slocPurp,palQty
      FROM tbl_dc11 with (nolock)
      
      --Bring in a record for each Fill Location and the Fill Qty.                  
      INSERT INTO @RESULTS
      SELECT agin,loc,'',0,palQty,0,0,0,0,0,0
      FROM @Temp
      WHERE slocPurp = '01'

      --Populate the Reserve Location and Reserve Qty.
      UPDATE r            
      SET r.rsrvLoc = t.loc, r.qtyRsrv = t.palQty
      FROM @RESULTS r
      INNER JOIN (Select loc, agin, palQty From @Temp Where slocPurp = '02') t
      ON r.agin = t.agin
                  
      SELECT * FROM @RESULTS
coperations07Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
Can you try just one insert statement?
DECLARE @RESULTS TABLE( agin      varchar(10),
                                          fillLoc      varchar(20),
                                          rsrvLoc      varchar(20),
                                          qtyRls      float,
                                          qtyFill      float,
                                          qtyRsrv      float,
                                          qtyRm5      float,
                                          qtyTrain      float,
                                          qtyWhse      float,
                                          qtyInc      float,
                                          qtyST18      float
                                          )
                                          
      DECLARE @Temp TABLE( agin      varchar(10),
                                     loc      varchar(20),
                                     slocPurp varchar(5),
                                     palQty float
                                     )
      
      --Bring needed data from tbl_dc11 into a temp table for ease of access.
      INSERT INTO @Temp
      SELECT agin,loc,slocPurp,palQty
      FROM tbl_dc11 with (nolock)
      
      --Bring in a record for each Fill Location and the Fill Qty.                  
      INSERT INTO @RESULTS
      select t1.agin, t1.loc fillLoc, t2.loc rsrvLoc, 0 qtyRls, 0 qtyFill,t2.palQty qtyRsrv,0 qtyRm5
  from tbl_dc11 t1
  join tbl_dc11 t2
    on t1.agin = t2.agin and t1.slocPurp = 1 and t2.slocPurp = 2

Open in new window

0
Terry WoodsIT GuruCommented:
I'm not intimately familiar with SQL Server, but the issues are general enough to apply to any database I think.

The final update looks at records in @Temp where slocPurp = '02' and the insert to @RESULTS looks at records in @Temp where slocPurp = '01'. Are there rows with other slocPurp values you can exclude from being inserted into @temp in the first place? eg, Using this for the initial query might help reduce the work in the later queries:
INSERT INTO @Temp
      SELECT agin,loc,slocPurp,palQty
      FROM tbl_dc11 with (nolock)
WHERE slocPurp in ('01','02');

Open in new window

If there's no index on slocPurp in the tbl_dc11 table, it would be worth adding one to further speed that up.

Your insert to @RESULTS would run faster if there was an index on the slocPurp column. However, building an index also costs time, so this may or may not help the overall execution time. The Update query looks at slocPurp and agin, so an index on (slocPurp, agin) may help.

However, the update query may need to be restructured to take advantage of that. I think this would give the same result (backup your data first, and test to ensure I'm correct!):
UPDATE r            
      SET r.rsrvLoc = t.loc, r.qtyRsrv = t.palQty
      FROM @RESULTS r
      INNER JOIN @Temp t ON r.agin = t.agin
Where t.slocPurp = '02'

Open in new window

0
coperations07Author Commented:
Thanks guys!

Sharath_123,
   That single insert definately runs faster.  There are agin's that only have a record where the slocPurp = 1. These records aren't being returned with your example. I tried making it a left join, but that still isn't it yet.  For final result I want a record for each slocPurp = 1 record in the table.

TerryAtOpus,
  There are slocPurp values that are NULL that can be excluded.  Overall there are 5 slocPurps. I need to return a record for each slocPurp = 1 and populate the quantities based on the other slocPurps for the agin.  I assumed I could use the same logic to update the 3,4,5 slocPurps if the update for slocPurp 2 got ironed out.  I will try out your suggestions now.

Thanks,
Dave
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

SharathData EngineerCommented:
try this.
DECLARE @RESULTS TABLE( agin      varchar(10),
                                          fillLoc      varchar(20),
                                          rsrvLoc      varchar(20),
                                          qtyRls      float,
                                          qtyFill      float,
                                          qtyRsrv      float,
                                          qtyRm5      float,
                                          qtyTrain      float,
                                          qtyWhse      float,
                                          qtyInc      float,
                                          qtyST18      float
                                          )
                                          
      --Bring in a record for each Fill Location and the Fill Qty.                  
      INSERT INTO @RESULTS
      select t1.agin, t1.loc fillLoc, t2.loc rsrvLoc, 0 qtyRls, 0 qtyFill,isnull(t2.palQty,0) qtyRsrv,0 qtyRm5
  from tbl_dc11 t1
  left join tbl_dc11 t2
    on t1.agin = t2.agin and t2.slocPurp = 2
    where t1.slocPurp = 1 

Open in new window

0
SharathData EngineerCommented:
<< Overall there are 5 slocPurps. I need to return a record for each slocPurp = 1 and populate the quantities based on the other slocPurps for the agin.  I assumed I could use the same logic to update the 3,4,5 slocPurps if the update for slocPurp 2 got ironed out.>>

Can you provide some more sample data for other slocPurps with expected result.
0
coperations07Author Commented:
Here's more data. If there are multiple records for the same agin/slocpurp then they are summed. **Had some issues with the formatting so I attached a spreadsheet with the data.

agin      loc                      palQty      slocPurp                                    
5114773      00 00 12 9686 3      1048      5                                    
5114773      02 21 46 2549 A      0      1                                    
5114773      02 21 45 2549 A      1265      2                                    
5114773      02 09 AZ 0004 A      4830      3                                    
5115012      00 00 12 9686 1      1260      5                                    
5115012      00 00 12 9686 1      1260      5                                    
5115012      00 00 12 9686 1      378      5                                    
5115012      02 06 60 8234 A      0      1                                    
5115012      02 09 AZ 0051 A      1260      3                                    
5115012      02 09 AA 0088 A      1260      3                                    
5115012      02 05 PJ 0004 Z      1260      4                                    
                                                      
agin      fillLoc                         rsrvLoc  Fill    Rsrv   Rm5  Train  Whse      Inc      qtyST18
5114773      0221462549A  0221452549 A      0       1265      0     0           4830       0      1048

5115012      0206608234A            0         0          0       0            2520      1260      2898
Book2.xlsx
0
SharathData EngineerCommented:
How many number of slocPurp do you have?
How do you want to fill qtyRm5,qtyTrain fields?

From your sample data and expected result, I assume
qtyFill = total palQty when slocPurp = 1
qtyRsrv = total palQty when slocPurp = 2
qtyRm5      = ??
qtyTrain = ??      
qtyWhse       = total palQty when slocPurp = 3
qtyInc      = total palQty when slocPurp = 4
qtyST18 = total palQty when slocPurp = 5

Let me know if my understanding is wrong.
0
coperations07Author Commented:
5 Total slocPurp.  Looks like your understanding is right on.

The qtyRm5 and qtyTrain are slocPurp = 4, but I was planning on setting all slocPurp = 4 to the qtyInc which is what most are, then updating to the other qty if the location is a certain type.

The qtyRm5 is where slocPurp = 4 and the loc contains '02 05' as first 5 char.
The qtyTrain is where slocPurp = 4 and the loc contains '02 40 TR' as first 8 char.
0
SharathData EngineerCommented:
try this query
INSERT INTO @RESULTS
SELECT agin, 
       MAX(CASE 
             WHEN slocPurp = 1 THEN loc 
           END) fillLoc, 
       MAX(CASE 
             WHEN slocPurp = 2 THEN loc 
           END) rsrvLoc, 
       SUM(CASE 
             WHEN slocPurp = 1 THEN palQty 
             ELSE 0 
           END) qtyFill, 
       SUM(CASE 
             WHEN slocPurp = 2 THEN palQty 
             ELSE 0 
           END) qtyRsrv, 
       SUM(CASE 
             WHEN loc LIKE '02 05%' THEN palQty 
             ELSE 0 
           END) qtyRm5, 
       SUM(CASE 
             WHEN loc LIKE '02 40 TR%' THEN palQty 
             ELSE 0 
           END) qtyTrain, 
       SUM(CASE 
             WHEN slocPurp = 3 THEN palQty 
             ELSE 0 
           END) qtyWhse, 
       SUM(CASE 
             WHEN slocPurp = 4 THEN palQty 
             ELSE 0 
           END) qtyInc, 
       SUM(CASE 
             WHEN slocPurp = 5 THEN palQty 
             ELSE 0 
           END) qtyST18 
  FROM tbl_dc11 
 GROUP BY agin 

Open in new window

Here is a tested sample: http://sqlfiddle.com/#!3/0720df/21
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coperations07Author Commented:
I'm getting this error message.
Msg 213, Level 16, State 1, Procedure p_rpt_dc11_inv_sum, Line 39
Column name or number of supplied values does not match table definition.

It looks like all the columns are accounted for, but I haven't used Cases much in SQL. I'm working on debugging.  Here's what I've got.

      DECLARE @RESULTS TABLE( agin      varchar(10),
                                          fillLoc      varchar(20),
                                          rsrvLoc      varchar(20),
                                          qtyRls      float,
                                          qtyFill      float,
                                          qtyRsrv      float,
                                          qtyRm5      float,
                                          qtyTrain      float,
                                          qtyWhse      float,
                                          qtyInc      float,
                                          qtyST18      float
                                          )


      INSERT INTO @RESULTS
SELECT agin,
       MAX(CASE
             WHEN slocPurp = 1 THEN loc
           END) fillLoc,
       MAX(CASE
             WHEN slocPurp = 2 THEN loc
           END) rsrvLoc,
       SUM(CASE
             WHEN slocPurp = 1 THEN palQty
             ELSE 0
           END) qtyFill,
       SUM(CASE
             WHEN slocPurp = 2 THEN palQty
             ELSE 0
           END) qtyRsrv,
       SUM(CASE
             WHEN loc LIKE '02 05%' THEN palQty
             ELSE 0
           END) qtyRm5,
       SUM(CASE
             WHEN loc LIKE '02 40 TR%' THEN palQty
             ELSE 0
           END) qtyTrain,
       SUM(CASE
             WHEN slocPurp = 3 THEN palQty
             ELSE 0
           END) qtyWhse,
       SUM(CASE
             WHEN slocPurp = 4 THEN palQty
             ELSE 0
           END) qtyInc,
       SUM(CASE
             WHEN slocPurp = 5 THEN palQty
             ELSE 0
           END) qtyST18
  FROM tbl_dc11
 GROUP BY agin


      SELECT agin,fillLoc,rsrvLoc,qtyFill,qtyRsrv,qtyRm5,qtyTrain,qtyWhse,qtyInc,qtyST18
      FROM @RESULTS
      ORDER BY rsrvLoc
0
coperations07Author Commented:
This is working great. I had an extra column in my temp table that was causing the error. I got pulled off from working on this and just got back to it. Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.