Professional Web Applications Themes

Slow mySQL select - mySQL/JDBC/CFMX issue? - Coldfusion Database Access

Hello! I have got two simple SQL statements in my CFMX app: SELECT companyname, uidnumber, description, telephone FROM companies WHERE entrykey = ? ; Query Parameter Value(s) - Parameter #1(cf_sql_varchar) = 2405961E-FE08-E3BE-059C648DC01198A9 SELECT LEFT(companyname, 250) AS companyname, LEFT(uidnumber, 100) AS uidnumber, LEFT(description, 250) AS description, LEFT(telephone, 100) AS telephone FROM companies WHERE entrykey = ? ; Query Parameter Value(s) - Parameter #1(cf_sql_varchar) = 2405961E-FE08-E3BE-059C648DC01198A9 Both return the same result, but the second one only take 1 msec, the first one 30 msec. The number of chars to return (using LEFT) is exactly the length of the field (no data is ...

Sponsored Links
  1. #1

    Default Slow mySQL select - mySQL/JDBC/CFMX issue?

    Hello!

    I have got two simple SQL statements in my CFMX app:

    SELECT
    companyname,
    uidnumber,
    description,
    telephone
    FROM
    companies
    WHERE
    entrykey = ?
    ;

    Query Parameter Value(s) -
    Parameter #1(cf_sql_varchar) = 2405961E-FE08-E3BE-059C648DC01198A9

    SELECT
    LEFT(companyname, 250) AS companyname,
    LEFT(uidnumber, 100) AS uidnumber,
    LEFT(description, 250) AS description,
    LEFT(telephone, 100) AS telephone
    FROM
    companies
    WHERE
    entrykey = ?
    ;

    Query Parameter Value(s) -
    Parameter #1(cf_sql_varchar) = 2405961E-FE08-E3BE-059C648DC01198A9

    Both return the same result, but the second one only take 1 msec, the first
    one 30 msec. The number of chars to return (using LEFT) is exactly the length
    of the field (no data is shortend). What could be the reason for that
    difference? Any methods to make query #1 as fast as query #2?

    system: JDBC 5.0 / myISAM tables with UTF-8 encoding / CFMX7 app server

    best regards,

    Hansjoerg

    Sponsored Links
    funkymusic_austria Guest

  2. #2

    Default Re: Slow mySQL select - mySQL/JDBC/CFMX issue?

    It could be that the second query made use of the connection that the first
    established. In fact, if MySQL sees the second query as identical to the first
    it will not run the second query. It will simply return the first result-set
    from cache.





    BKBK Guest

  3. #3

    Default Re: Slow mySQL select - mySQL/JDBC/CFMX issue?

    What happens if you run them in the opposite order? As BKBK said, one of the reasons the first one was slower may have been connection time.
    Dan Guest

  4. #4

    Default Re: Slow mySQL select - mySQL/JDBC/CFMX issue?

    And if entrykey is not identified as a primary key you might try creating an index on it to speed things up.
    Dina Guest

  5. #5

    Default Re: Slow mySQL select - mySQL/JDBC/CFMX issue?

    Hello!

    - Index exists, table is optimized (and currently has just 5 records).
    - I tried to change the order, no change. The version with the simple naming
    of the desired fields is always up to 10-100 times slower than the version with
    LEFT( ...).
    - I now went back to mysql-connector-java-3.0.17-ga-bin.jar from version
    mysql-connector-java-5.0.6 - now both queries have the same speed (about 1-3
    msec).

    So it seems to be a JDBC driver issue but I cannot find any information on the
    net why the newer version is sooo much slower.

    Best regards,

    Hansjoerg

    funkymusic_austria Guest

  6. #6

    Default Re: Slow mySQL select - mySQL/JDBC/CFMX issue?

    I have experienced similar performance issues with connector 5.0.6. Did you try connector 3.1.14?
    bilgehan Guest

Similar Threads

  1. CFMX 6.1 and MySQL 5.0.27 JDBC issues
    By carlosmojo in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 26th, 05:57 PM
  2. MySQL and JDBC 4.0
    By Steven Buroff in forum MySQL
    Replies: 1
    Last Post: August 9th, 05:46 PM
  3. CFMX7-jdbc-MySQL
    By dgaddis in forum Coldfusion Database Access
    Replies: 0
    Last Post: July 14th, 03:58 PM
  4. com.mysql.jdbc.Driver
    By Pekka in forum Coldfusion Database Access
    Replies: 1
    Last Post: February 21st, 08:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139