Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    9

    Angry Unanswered: The strangest thing of all time (query issue)

    I have a query stack that uses local variables that I declare & set at the top. That stack takes a minute & runs the processor at 100%. If I substitute actual values in place of the variables the stack runs in 10 seconds! Why!?.. I need it to run that fast with variables!

    -Brian Flynn

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's a "query stack"?

    Is that like flap jacks?

    Post the "stack"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Posts
    9

    "query stack"

    A query stack = multiple queries. In this situation there's multiple steps that dump their results into temp tables then get used in the last step.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you give us a sample of one of the predicates that slows down?

    They're datetime values...yes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Posts
    9

    examples

    Technically... No... they're not datetime fields but I kind of wish they were!... The fields are char(8) but do hold date data... I have avoided casting them into datetime fields as there's a tremendous # or rows that would then have to go through the conversion & in my initial tests that slowed it down much worse as could be expected.

    -------------------------------------------------------

    select trd.acct_no , trd.sub_no , trd.trade_cymd , trd.asof_cymd , trd.settle_cymd , trd.rep , trd.purch_or_sale , trd.sec_no , trd.symbol , trd.net_amt , trd.big_qty , trd.total_ord_qty , trd.big_prx , trd.concession_amt , trd.handle_fee_amt , trd.odd_lot_dif , trd.postage_amt , trd.sec_fee_amt
    from DATABASENAME.dbo.trd trd
    inner join mfnav_Householding02 h on h.acct_no = trd.acct_no
    where h.hh_acct_no in ( select hh_acct_no from #candidates2 )
    and trd.purch_or_sale in ( 'S' , 'A' )
    and trd.sec_type = 'F'
    and trd.sec_class <> 'FOM'
    and trd.trade_cymd >= @FromDate_str -- '20030606' --
    and trd.trade_cymd <= @ToDate_str -- '20031203' --

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    INNER JOIN #candidates2 just like the other tables, and create another temp table that would hold 'S' and 'A' and INNER JOIN that table too. As per data values, - you can use the same approach, - create another table and INNER JOIN that one...INNER JOIN EVERYTHING!!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    May 2004
    Posts
    6
    Another suggestion, use the Table variable type, rather than creating a temporary table if you can, table variables are much faster than temporary tables.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In general, yes, but if Brian is using this temporary dataset several times during the execution of his "stack", then he might benefit from the extra indexing that can be applied to temporary tables and that is not available for table variables.

    Brian, what indexes do you have on trd, mfnav_Householding02, and #candidates2?
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    and trd.purch_or_sale in ( 'S' , 'A' )

    is this column indexed.
    if it is not it will cause scanning instead of seeking

Posting Permissions

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