Results 1 to 8 of 8

Thread: Writing a Query

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Writing a Query

    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 the query I was talking about:


    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, -- 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
    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'

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The order of the clauses makes no difference in the query plan. Queries frequently run faster the second time you execute them, as a plan has already been generated and much of the data may already be in cache.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...BUT...you should use JOIN syntax instead of linking your tables in the WHERE clause. Re-read the section on SELECT statements in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Posts
    294
    Hey thank you so much !!!!

  5. #5
    Join Date
    Jun 2003
    Posts
    294
    I took stats, and I still have the same problem, The first query is really slow and the second one is too fast,

    does any body knows why ???

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm sorry. Did you say the second one was "too fast"?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The order of tables is aesthetic only except when join hints have been used. Would you be able to post the two new queries in their entirety?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2003
    Posts
    294
    /**************/
    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'

Posting Permissions

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