Results 1 to 7 of 7

Thread: Run Faster

  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Unanswered: Run Faster

    Hi Guys,

    I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...

    Many Thanks,

    Regards,
    Shaffiq

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    post the query.let us see ur query
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    I running it on Coldfusion program :

    SELECT ID,
    ORDERREFERENCE,
    CUSTOMER,
    TRANSACTIONTYPE,
    FIRSTNAME,
    LASTNAME,
    COMPANY,
    ADDRESS,
    CITY,
    STATE,
    ZIP,
    COUNTRY,
    EMAIL,
    PHONE,
    SHIPFIRSTNAME,
    SHIPLASTNAME,
    SHIPADDRESS,
    SHIPCITY,
    SHIPSTATE,
    SHIPZIP,
    SHIPCOUNTRY,
    COMMENT1,
    COMMENT2
    FROM GTRANSACTIONRECORD
    WHERE 0 = 0



    AND TRANSACTIONDATETIME BETWEEN <CFQUERYPARAM VALUE="2005-02-17 00:00:00" CFSQLTYPE="CF_SQL_DATE">
    AND <CFQUERYPARAM VALUE="2005-02-18 00:00:00" CFSQLTYPE="CF_SQL_DATE">


    ORDER BY ORDERREFERENCE ASC

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you do not need the "0 = 0" and I would try creating a nonclustered index on TRANSACTIONDATETIME.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, drop the 0=0, though that is not what is causing the query to run slowly.
    Do you have indexes on TRANSACTIONDATETIME and ORDERREFERENCE?
    Do you absolutely need the ORDER BY clause? I'm guessing that is where the slowdown is occuring, and recordset ordering is usually a presentation issue that should be handled by the interface or reporting tool.

    Have you tried running this code in Query Analyzer with the SHOW EXECUTION PLAN option set?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jun 2003
    Posts
    269
    I think he using '0=0' for constructing where clause dynamically in application,so he dont have to check whether its first filter case or second case.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That would be my guess too, or it was left behind by a previous coder...
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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