?
Solved

Update very slow

Posted on 2012-09-11
10
Medium Priority
?
453 Views
Last Modified: 2012-09-12
I have data in Tbl1 as follows:

ColPk   ColBK    ColVal    Flag    End_Date
1          A           10          N        '09-FEB-2009'
2          A           20          N        '08-JAN-2008'
3          A           30          Y         '31-DEC-2019'
4          B           10          N        '09-JAN-2005'
5          B           20          N        '08-FEB-2006'
6          B           30          N         '05-MAR-2009'
7          B           40          Y         '31-DEC-2019'

I need to delete the ColVal=40 row for ColBK=B and update the previous row's flag and date.

This is the code I wrote:

update Tbl1
set End_Date='31-DEC-2019', Flag='Y'
where (ColBK, ColVal) in
(
 select ColBK, max(ColVal)
    from Tbl1
where ColVal < 40
     and Flag = 'N'
     and ssno in (
                         select distinct ColBK
                            from Tbl1
                        where ColVal=40
                        )
   Group by ColBK
)

So for ColBKs for which there are no ColVal=40 records, nothing gets changed. After this UPDATE sql, I'll execute a SQL to delete ColVal=40 records from the table.

My problem: the SQL works but is very slow due to data volume. I tried the MERGE statement but it doesn't work because of ora-38104. Can anyone help me speed up this SQL?

Another option I have: I can also delete ColVal=40 records first and do the UPDATE if that helps performance.

Thanks.
0
Comment
Question by:soccerplayer
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38389492
>> the SQL works but is very slow due to data volume
How big is the volume?  How many record to you expect to be updated?

What indexes are available and what is the explain plan for the current SQL?
0
 
LVL 21

Expert Comment

by:Amitkumar P
ID: 38389541
You have used same table three times in the query. Also you are using the IN clause that is affecting the performance.

Yes, Index may decrease the performance and that's why Iwedwell asked about the index.
0
 
LVL 21

Expert Comment

by:Amitkumar P
ID: 38389553
is ssno field part of the table?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:soccerplayer
ID: 38389573
SSNO really should be ColBK.
0
 

Author Comment

by:soccerplayer
ID: 38389654
Here is the Explain Plan:

Explain Plan
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38389655
You say there are is a 'data volume' yet the costs and cardinality are tiny.  Do you have statistics gathered on the tables?
0
 

Author Comment

by:soccerplayer
ID: 38389665
May be statistics need to be analyzed. Let me let everyone know.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38390884
Also try using exists there instead of the select distinct.

update Tbl1
set End_Date='31-DEC-2019', Flag='Y'
where (ColBK, ColVal) in
(
	select a.ColBK, max(a.ColVal)
    	from Tbl1 a
	where 	a.ColVal < 40
     		and a.Flag = 'N'
		and exists(select 1 from tbl1 where ColVal=40 and ssno = a.ssno)
	Group by ColBK
)

Open in new window


Another option I have: I can also delete ColVal=40 records first and do the UPDATE if that helps performance

That will likely help because you will get rid of the extra condition there... In that case, I guess, it will be

update Tbl1
set End_Date='31-DEC-2019', Flag='Y'
where (ColBK, ColVal) in
(
	select a.ColBK, max(a.ColVal)
    	from Tbl1 a
	where 	a.ColVal < 40
     		and a.Flag = 'N'
	Group by ColBK
)

Open in new window

0
 

Author Closing Comment

by:soccerplayer
ID: 38392156
The table needed to be analyzed and stats updated.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38392176
Have you ever read my last comment? Your query can be improved as well... Anyhow
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

616 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