locked
Writing a Query RRS feed

  • Question

  • Hello I'm new on sql server and I wonder if there's an specific order for writing a query's clauses (joins, and other conditions in where clause or even the order of the tables in the from clause), I mean, I have a query that takes 1 minute executing, but if I execute the same query after moving the tables in the from clause (changing the order) It takes 5 seconds, could you please tell me Why ??? and what's the best order from writing a query ?

    This is my specific query:

     

    /**************/
    This is the first one:
    ***************/
    SELECT TREP_VALOR_PRECIOS.CDPRECIO,
    TREP_VALOR_PRECIOS.NMANO,
    TREP_VALOR_PRECIOS.NMSEMANA,
    ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
    UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
    TREP_PRECIOS.CDTIPO_PRECIO,
    TREP_VALOR_PRECIOS.SNACTIVO,
    'N' SNACTIVAR
    FROM
    UN_TASASCAMBI,
    T2_CALENDARIO,
    UN_MONEDEXTRA,
    TREP_PRECIOS TREP_PRECIOS_BASE,
    TREP_VALOR_PRECIOS,
    TREP_PRECIOS
    WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
    AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
    AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
    AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
    AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
    AND T2_CALENDARIO.CALTIPOFRUTA = '01'
    AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
    AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
    AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
    AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
    AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
    AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
    AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'


    /*********************************************/
    This is the second one: This is much faster than the first one
    /*********************************************/

    SELECT TREP_VALOR_PRECIOS.CDPRECIO,
    TREP_VALOR_PRECIOS.NMANO,
    TREP_VALOR_PRECIOS.NMSEMANA,
    ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
    UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
    TREP_PRECIOS.CDTIPO_PRECIO,
    TREP_VALOR_PRECIOS.SNACTIVO,
    'N' SNACTIVAR
    FROM
    UN_MONEDEXTRA,
    TREP_PRECIOS TREP_PRECIOS_BASE,
    TREP_VALOR_PRECIOS,
    TREP_PRECIOS
    UN_TASASCAMBI, -- I just moved this one to the end of the from clause
    T2_CALENDARIO, -- I just moved this one to the end of the from clause
    WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
    AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
    AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
    AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
    AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
    AND T2_CALENDARIO.CALTIPOFRUTA = '01'
    AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
    AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
    AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
    AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
    AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
    AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
    AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'

     

     

    Tuesday, May 2, 2006 2:51 PM

Answers

  • Generally the order of the tables in the FROM clause should not matter since the query optimizer will evaluate various join strategies and pick the order in which the tables are joined. There are however cases where this may not work that great since optimizer could stop after evaluating few options. Try updating the stats on the tables involved in the query and see if it makes any difference.
    • Marked as answer by Kent Waldrop _ Wednesday, August 10, 2011 11:56 AM
    Wednesday, May 3, 2006 1:09 AM

All replies

  • hi luis,

    i think the order of the table does matter.

    parent table in a relationship

    must be querried first, then followed by the child.

    regards,

    Tuesday, May 2, 2006 4:14 PM
  • Generally the order of the tables in the FROM clause should not matter since the query optimizer will evaluate various join strategies and pick the order in which the tables are joined. There are however cases where this may not work that great since optimizer could stop after evaluating few options. Try updating the stats on the tables involved in the query and see if it makes any difference.
    • Marked as answer by Kent Waldrop _ Wednesday, August 10, 2011 11:56 AM
    Wednesday, May 3, 2006 1:09 AM