Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

optimizing a sql syntax

Posted on 2011-03-06
5
257 Views
Last Modified: 2012-05-11
I have few tables under the database: CRMDB.
The table books is very huge, almost 13712821 rows [aprox].
I feel that the following sysntax would be very costly:
delete from CRMDB.books where custId not in (select custId from CRMDB.customers);

Is there any way to overcome the costly not in command??
0
Comment
Question by:pvinodp
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 200 total points
ID: 35053747
Backup database and try these alternative queries
DELETE b.*
FROM    CRMDB.books b
        LEFT JOIN CRMDB.customers c ON b.custId = c.custId
WHERE   c.custId IS NULL

Open in new window


DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT *
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId ) ;

Open in new window

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 300 total points
ID: 35053806
Instead of * you can go with 1 in the sub-query.
DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT 1
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId );

Open in new window

0
 

Author Comment

by:pvinodp
ID: 35054112

I tried and getting this error:

delete from omcr.HistoricalAlarmNotification WHERE   NOT EXISTS ( select 1 from omcr.HistoricalAlarmLifecycle where omcr.HistoricalAlarmNotification.halcid omcr.HistoricalAlarmLifecycle.halcid );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'omcr.HistoricalAlarmLifecycle.halcid )' at line 1
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35054155
you missed =
DELETE FROM omcr.HistoricalAlarmNotification 
      WHERE NOT EXISTS (SELECT 1 
                          FROM omcr.HistoricalAlarmLifecycle 
                         WHERE omcr.HistoricalAlarmNotification.halcid = omcr.HistoricalAlarmLifecycle.halcid);

Open in new window

0
 

Author Closing Comment

by:pvinodp
ID: 35125840
I thank you all for your inputs
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 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