Results 1 to 3 of 3

Thread: Optimize Query

  1. #1
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Unanswered: Optimize Query

    Hi,

    I have the following query :
    SELECT COUNT(DSAV.DSAV_ID) FROM DSAV
    WHERE DSAV.AECH_ID IN (SELECT AECH.AECH_ID FROM AECH
    WHERE AECH.AECH_N_IMEI = '449281961941171')
    OR DSAV.DSAV_N_IMEI='449281961941171'
    OR DSAV.DSAV_N_IMEI2='449281961941171';

    My question is : How to avoid 'OR' clause which produce very poor performance ?

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Location
    Norway
    Posts
    3

    Re: Optimize Query

    Hi

    It depends on what indexes you have.

    An IN statement is also often slower than the use of EXISTS
    This example can help, but check indexes on DSAV_N_IMEI and DSAV_N_IMEI2.

    SELECT COUNT(DSAV.DSAV_ID) FROM DSAV
    WHERE
    EXISTS ( SELECT AECH.AECH_ID FROM AECH
    WHERE AECH.AECH_N_IMEI = '449281961941171'
    and DSAV.AECH_ID=AECH.AECH_ID)
    OR DSAV.DSAV_N_IMEI='449281961941171'
    OR DSAV.DSAV_N_IMEI2='449281961941171';

    - Sindre

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Optimize Query

    The IN Clause don't seem to be very slower that EXISTS. I have found that it's the OR clause which are slow.

    Originally posted by Sindre.g
    Hi

    It depends on what indexes you have.

    An IN statement is also often slower than the use of EXISTS
    This example can help, but check indexes on DSAV_N_IMEI and DSAV_N_IMEI2.

    SELECT COUNT(DSAV.DSAV_ID) FROM DSAV
    WHERE
    EXISTS ( SELECT AECH.AECH_ID FROM AECH
    WHERE AECH.AECH_N_IMEI = '449281961941171'
    and DSAV.AECH_ID=AECH.AECH_ID)
    OR DSAV.DSAV_N_IMEI='449281961941171'
    OR DSAV.DSAV_N_IMEI2='449281961941171';

    - Sindre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •