?
Solved

SQL questioner

Posted on 2011-05-11
15
Medium Priority
?
188 Views
Last Modified: 2012-05-11

I have this table:
create table test (
ITEM VARCHAR(50),
OPR  INT,
PREVOPR INT,
NEXTOPR INT
)

I need to update the fields PREVOPR and NEXTOPR.
PREVOPR = OPR from previeous operation (first = 0)
NEXTOPR = OPR from next operation (last = 0)


Example data before update.
ITEM, OPR, PREVOPR, NEXTOPR
A       5       0       0
A       25      0       0
A       45      0       0
A       75      0       0
B       10      0       0
B       20      0       0
B       30      0       0
B       40      0       0
C       5       0       0
C       20      0       0
C       45      0       0


Example data after update:
ITEM, OPR, PREVOPR, NEXTOPR
A       5       0       25
A       25      5       45
A       45      25      75
A       75      45      0
B       10      0       20
B       20      10      30
B       30      20      40
B       40      30      0
C       5       0       20
C       20      5       45
C       45      20      0

Does any of you have an idea how to code this SQL ???

The original table will have about 150.000 of theese operations
0
Comment
Question by:Vinum
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35737444
try this:

with c as (
select *, row_number() over (order by item, opr) rn
)
update c
set c.prevopr=cp.opr, c.netxopr=cn.opr
from c
left join c cp  on c.rn=cp.rn+1
left join c cn  on c.rn=cp.rn-1;

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35737501
Minor tweak to HainKurt's solution given you sample output:

with c as (
   select ITEM, OPR, PREVOPR, NEXTOPR
        , row_number() over (partition by ITEM order by OPR) rn
   from test
)
update c
set c.prevopr=coalesce(cp.opr, 0)
    , c.netxopr=coalesce(cn.opr, 0)
from c
left join c cp  on c.rn=cp.rn+1
left join c cn  on c.rn=cp.rn-1;
;
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35737511
some fixes ;) replace  "opr" on first line with your org table:

with c as (
select *, row_number() over (order by item, opr) rn from opr
)
update o
set o.prevopr=cp.opr, o.nextopr=cn.opr
from c o
left join c cp on o.rn=cp.rn+1 and o.item=cp.item
left join c cn on o.rn=cn.rn-1 and o.item=cn.item
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 10

Expert Comment

by:dwe761
ID: 35737523
WITH cte (ITEM, OPR, RowNum) AS
(
   SELECT
         ITEM,
         OPR,
         ROW_NUMBER() OVER (ORDER BY ITEM, OPR)
      FROM #Test
)
SELECT
      t1.ITEM,
      t1.OPR,
            t3.OPR PREVOPR,
      t2.OPR NEXTOPR
   FROM cte t1
      LEFT JOIN cte t2 ON t1.RowNum = t2.RowNum - 1
            LEFT JOIN cte t3 ON t1.RowNum = t3.RowNum + 1
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35737537
a minor fix (0 insteAD of nulls)


with c as (
select *, row_number() over (order by item, opr) rn from opr
)
update o
set o.prevopr=isnull(cp.opr,0), o.nextopr=isnull(cn.opr,0)
from c o
left join c cp on o.rn=cp.rn+1 and o.item=cp.item
left join c cn on o.rn=cn.rn-1 and o.item=cn.item

select * from opr

item	opr	prevopr	nextopr
A         	5	0	25
A         	25	5	45
A         	45	25	75
A         	75	45	0
B         	10	0	20
B         	20	10	30
B         	30	20	40
B         	40	30	0
C         	5	0	20
C         	20	5	45
C         	45	20	0

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35737562
mwvisa1, you are not taking ITEM into account... your cte query will give multiple records for a given rn... I guess it will not do what he wants...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35737658
I was trying to help tweak your query, so didn't catch that you didn't have ITEM in the JOIN.  PARTITION BY was added to account for ITEM. :)

with c as (
   select ITEM, OPR, PREVOPR, NEXTOPR
        , row_number() over (partition by ITEM order by OPR) rn
   from test
)
update c
set c.prevopr=coalesce(cp.opr, 0)
    , c.netxopr=coalesce(cn.opr, 0)
from c
left join c cp  on c.ITEM = cp.ITEM and c.rn=cp.rn+1
left join c cn  on c.ITEM = cn.ITEM and c.rn=cp.rn-1;
;

Your way now works because you are generating row numbers for everything, but then binding to ITEM.  You were not before and so would have resulting in ITEM not being taken into account as well.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35737699
And FYI, that is why I said minor tweak to your solution.  I was simply highlighting the errors, so glad you corrected them in your new post.
0
 

Author Comment

by:Vinum
ID: 35737740
To mwvisa1:
I tried your solution ID: 35737658
It only updates PREVOPR, not NEXTOPR


To HainKurt:
I tried your solution ID: 35737537
It seems like it does the job (only correctet ... "rn from opr" to "rn from test"

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35737795
Personally, I like the CTE approach using the ROW_NUMBER() windowing function, which is why I support HainKurt's solution; however, here is an alternative for completeness of the thread -- will work on systems where CTE/ROW_NUMBER() are not available:

UPDATE tto
SET tto.PREVOPR = 
        COALESCE((
           SELECT MAX(OPR) 
           FROM test tfr 
           WHERE tfr.ITEM = tto.ITEM 
           AND tfr.OPR < tto.OPR
        ), 0)
   , tto.NEXTOPR = 
        COALESCE((
           SELECT MIN(OPR) 
           FROM test tfr 
           WHERE tfr.ITEM = tto.ITEM 
           AND tfr.OPR > tto.OPR
        ), 0)
FROM test tto
;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35737829
@Vinum, that wasn't meant to be my solution as I was pointing out to HainKurt.  I merely posted to advise you of corrections to his original query.  Therefore, those are type-o's from HainKurt's original post.  If you look cp.rn-1 should be cn.rn-1 and should be nextopr instead of netxopr.  At any rate, I wasn't looking for points there.  My reason for posting was if you ran the original query as posted, it would not have worked properly...

Best regards and happy coding,

Kevin
0
 

Author Comment

by:Vinum
ID: 35744898
Some info, maybe of interest

I tried the 2 solutions on a copy of the real DB's.
It consist of 3 tables:
Prod_order
Prod_opr (join Prod_order on fk_Prod_orderID)
Prod_opr_detail (join prod_opr on fk_Prod_oprID)

I have to use both Prod_opr and Prod_opr_detail in the update, as the uniqie key (fk_prod_orderid) is on Prod_opr (copied to Test_opr and Test_opr_detail)
Prod_opr and Prod_opr_detail is one-to-one match - about 328.000 records in each.

When updating with HainKurt's solution ("with...") it takes about 25 seconds.
When updating with mwvisa1's solution (traditional update), I don't know how long it takes - it never finished.
When running on a copy with only 1.000 records, it took about 10 seconds.
When running on a copy with 10.000 records, it had not finished after 5 minutes.


with c as (
select 
	rd.id as rdid, 
	rd.opr_no as rdopr_no,
	rd.prev_opr as rdprev_opr,
	rd.next_opr as rdnext_opr,
	ro.fk_prod_orderid as rofk_prod_orderid,
 	row_number() over (order by fk_prod_orderid, rd.id, rd.opr_no) rn 
from test_opr_detail rd 
	left join test_opr ro on rd.fk_prod_oprid = ro.id
)
update o
set o.rdprev_opr=isnull(cp.rdopr_no,0), o.rdnext_opr=isnull(cn.rdopr_no,0)
from c o
left join c cp on o.rn=cp.rn+1 and o.rofk_prod_orderid=cp.rofk_prod_orderid
left join c cn on o.rn=cn.rn-1 and o.rofk_prod_orderid=cn.rofk_prod_orderid

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35746920
You probably have some indexing not in properly in place why the non-CTE approach took too long.  It is going to inefficient due to subqueries in both directions, which is why in a system where CTE is available I would use that.  As far as the CTE approach taking 25 seconds that might be an indication of poor indexing also.  Though 25 seconds isn't extremely horrible.
Anyway, thanks for the information.  It would have been good to see what the other query you tried that didn't finish though since it appears you rewrote this a bit.
0
 

Author Comment

by:Vinum
ID: 35746993
Hi mwvisa1

Below is the code. I am quite new in SQL programming, I am an old COBOL mainframe programmer, so it may not be the most professional programming in SQL :-)
UPDATE tto
SET tto.PREV_OPR = 
        COALESCE((
           SELECT MAX(tfr.OPR_NO) 
           FROM test_opr_detail tfr
				left join test_opr tfo on tfr.fk_prod_oprid = tfo.id 
           WHERE tfo.fk_prod_orderid = ttop.fk_prod_orderid 
           AND tfr.OPR_NO < tto.OPR_no
        ), 0)
   , tto.NEXT_OPR = 
        COALESCE((
           SELECT MIN(tfr.OPR_no) 
           FROM test_opr_detail tfr 
				left join test_opr tfo on tfr.fk_prod_oprid = tfo.id
           WHERE tfo.fk_prod_orderid = ttop.fk_prod_orderid
           AND tfr.OPR_NO > tto.OPR_NO
        ), 0)
FROM test_opr_detail tto
	left join test_opr ttop on tto.fk_prod_oprid = ttop.id

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35747423
*smile* no worries, we are all having to constantly change and keep up with technology and as such are always learning.

Is it safe to say that fk_prod_oprid and fk_prod_orderid are dependent, i.e., if you have a unique match to fk_prod_orderid is it true that the fk_prod_oprid is same unique match always.  If so, you may get away with not joining in the test_opr table since you don't actually need any of the values in that table.  You really are just needing a column on which to group data ... would the fk_prod_oprid not work?
UPDATE tto
SET tto.PREV_OPR = 
        COALESCE((
           SELECT MAX(tfr.OPR_NO) 
           FROM test_opr_detail tfr
           WHERE tfr.fk_prod_oprid = tto.fk_prod_oprid
           AND tfr.OPR_NO < tto.OPR_no
        ), 0)
   , tto.NEXT_OPR = 
        COALESCE((
           SELECT MIN(tfr.OPR_no) 
           FROM test_opr_detail tfr 
           WHERE tfr.fk_prod_oprid = tto.fk_prod_oprid
           AND tfr.OPR_NO > tto.OPR_NO
        ), 0)
FROM test_opr_detail tto
;

Open in new window


If you do need the JOINs, I would consider an INNER JOIN versus LEFT OUTER JOIN there.

To simulate the situation, I created test_opr_detail table with 360448 rows and get a 10 second response using CTE and 24 seconds using subqueries.

Creating an index similar to this:
create nonclustered index idx_fk_prod_oprid on test_opr_detail(fk_prod_oprid) include(opr_no);

Open in new window


The CTE approach is still a bit faster at 9 seconds and the subqueries at 14 seconds.

In your environment you might find a greater improvement since you are seeing no response or minutes.  I would use the ESTIMATED EXECUTION PLAN avaialble in SQL Management Studio to see where the highest cost is within your query.   Again, I suspect it is indexing.

Moral, though, is the subqueries approach will be a bit slower.  The CTE approach I would expect faster, but depends on system resources.  I am running SQL Express edition on a laptop that is of decent power, so my getting 9-10 seconds versus your 24 isn't really unconceivable.

Anyway, thanks for sharing.  Intriguing to see the challenges folks face and see why something performs differently and determine the cause.  Helps me learn as well.  As I said, we are *all* still learning. *smile*
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

809 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