Professional Web Applications Themes

SQL Problems - can this be right? - Coldfusion Database Access

We are trying to a solve a SQL read problem since moving to CF MX and MSSQL 2003. It now takes 30 seconds to read our databases, while on the prevision CF 5, MSSQL2000 it was taking 1/10 of a second. Our web host support team advised us as follows: "CFMX and CF5 has different CFQUERY statement cache behavior; in CF5, the query returns only the first 100 rows to the client (MAXROWS=100), however, in CFMX it returns the entire row (140K records) and then displays only the first 100. The advantage of the second method can be that you ...

  1. #1

    Default SQL Problems - can this be right?

    We are trying to a solve a SQL read problem since moving to CF MX and MSSQL
    2003.

    It now takes 30 seconds to read our databases, while on the prevision CF 5,
    MSSQL2000 it was taking 1/10 of a second.

    Our web host support team advised us as follows:

    "CFMX and CF5 has different CFQUERY statement cache behavior;

    in CF5, the query returns only the first 100 rows to the client
    (MAXROWS=100), however, in CFMX it returns the entire row (140K records)
    and then displays only the first 100.

    The advantage of the second method can be that you can continue to work
    with the cached results in follow-on queries; however, in this case it
    is causing tremendous traffic."

    In effect, each time you read a SQL database in CFMX, it reads and returns the
    entire database? Even if you are looking for just one record, or a limited
    record set?

    So on our large database, we can expect 30 seconds each time we read any part
    of the database for any reason?

    Surely, this can not be the case.

    Is there anyway to resolve the problem (other than go back to CF5)?

    Desperate for a solution,

    Bill

    bmyers Guest

  2. #2

    Default Re: SQL Problems - can this be right?

    > In effect, each time you read a SQL database in CFMX, it reads and returns
    the entire database? Even if you are looking for just one record, or a limited
    record set?

    No. It returns the entire results of the query. If your query is a "select *
    from table", then yes, it will return that whole table.

    Kronin555 Guest

  3. #3

    Default Re: SQL Problems - can this be right?

    So basically it means you can no longer rely on Cold Fusion (MX or later) for
    sites that have large databases?

    As a result of 'upgrading' to MX, our super fast site has now been reduced to
    a crawl, with 30 second read times each time the database is accessed.

    Same database takes 1000 times longer (literally) to find and read a few
    records than it did with CF5.

    See anything wrong with the code below?

    <CFQUERY name="searchcontent" datasource="#MGDatabase#" maxrows='100'
    blockfactor="100" username="*****" password="*****">
    SELECT page_number, headline, byline,
    department,html,alternate_link,show_rss,active_dat e,image1,access,align1,active_
    date
    FROM content
    WHERE (page_number > 2 And (status = 'a') AND (portal_show = 'y' or
    portal_show = '1' or portal_show = '2') AND (purge_date >
    #CreateODBCDate(check_today)#))
    ORDER BY active_date DESC, content_rank ASC, Page_number DESC
    </CFQUERY>

    This has worked flawlessly and fast for years. But no more.

    If this is the case, what's the point of upgrading (or continuing to use Cold
    Fusion)?

    Is there any logical reason that CFMX does this?

    Bill

    bmyers Guest

  4. #4

    Default Re: SQL Problems - can this be right?

    Have you run this query directly in MSSQL and ensured it's executing in a
    timely manner? that is, are you sure it isn't a database issue?

    Your code looks fine, although specifying a maxrows of 100 kinda defeats the
    purpose of having a blockfactor of 100...

    Regarding blockfactor:
    [url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b19.htm#wp1102316[/url]
    "Might not be supported by some database systems."

    Are you all updated on CFMX with all the latest hotfixes and patches? Are you
    on 6.1 or 7? What database driver are you using?

    Kronin555 Guest

  5. #5

    Default Re: SQL Problems - can this be right?

    The site is on a dedicated server at intermedia.net.

    The control panel shows that it is running CF 6.1.0.0. The support team at
    intermedia says it is CF 6,1,0,83762.

    The server is on a Windows 2003 box, and the database is MSSQL (2003?).

    How can I test the sql server with a SQL query? And what could cause a MSSQL
    2000 database that ran perfectly on the older server, run slowly on the new one?

    Would there be any database design changes required whem moving from CF5 to
    CFMX?

    Thanks for your help.

    Bill

    bmyers Guest

  6. #6

    Default Re: SQL Problems - can this be right?

    If you have direct access to your db, you can run your query in Query yser
    (if you have it installed)

    Your query will probably run a bit faster if you took away some of the
    brackets in your where clause and replace this
    AND (portal_show = 'y' or portal_show = '1' or portal_show = '2')
    with this
    AND portal_show in (y', '1', '2')

    Something else to check, since you are on a newer version of mssql, is that
    your new database has all the indexes the old database had. This could be the
    reason for the drastic increase in query response time.



    Originally posted by: bmyers
    The site is on a dedicated server at intermedia.net.

    The control panel shows that it is running CF 6.1.0.0. The support team at
    intermedia says it is CF 6,1,0,83762.

    The server is on a Windows 2003 box, and the database is MSSQL (2003?).

    How can I test the sql server with a SQL query? And what could cause a MSSQL
    2000 database that ran perfectly on the older server, run slowly on the new one?

    Would there be any database design changes required whem moving from CF5 to
    CFMX?

    Thanks for your help.

    Bill



    Dan Bracuk Guest

  7. #7

    Default Re: SQL Problems - can this be right?

    BMyers,

    I don't see this issue using the 3.5 JDBC SQL Server driver (return all rows
    to CF and show only "maxrows" rows) when i run a trace.

    Are you or your ISP using the ODBC driver? Or an older JDBC driver with 6.1?

    There is a quirk that can cause this behavior or "all rows" (disregarding
    maxrows in the fetch) with some drivers but again, I don't see it with SQL
    Server JDBC and [url]http://www.macromedia.com/go/42dcb10a[/url].

    We're fixing this as we speak so all drivers work but due to the way we
    process the CFQUERY today, the DataDirect JDBC SQL Server driver acts as you'd
    expect - it fetches only the # rows in "maxrows". Let me know if this is
    enough or if I have to talk to someone at Intermedia.net.

    At the very least, the provider could have suggested several workarounds in
    the CFQUERY maxrows rather than saying "that's the way it is".

    Ex: (this worked for me too)

    Use SET ROWCOUNT
    <CFQUERY>
    SET ROWCOUNT 100
    Select * from table ...
    </cfquery>

    Another thought. If they installed CF 6.1 and migrated the CF5 settings, the
    migration incorrectly sets "String Format" to 0 in the neo-query.xml (rather
    than false). This still shows up in the admin as "unchecked" but it's really
    as if it were checked. This can cause a table scan for varchar cols in WHERE
    clauses on the backend (the long wait) but the fetch is still doing what you'd
    expect (bringing back only 100 rows - this is what I saw in the 3.5 driver).
    Have them check neo-query.xml for that datasource and/or CHECK/SUBMIT +
    UNCHECK/SUBMIT the datasource for the "String Format" field to the setting gets
    correctly written in the URL to "false" (sendStringParametersAsUnicode)

    Stephen Dupre
    Macromedia QA


    sdupre Guest

Similar Threads

  1. PDF problems
    By Doug in forum Macromedia Freehand
    Replies: 6
    Last Post: April 21st, 09:41 PM
  2. MD5 problems
    By Rudy in forum ASP.NET Security
    Replies: 2
    Last Post: March 14th, 05:11 AM
  3. 2 problems
    By pBarrelas in forum Macromedia Flash Sitedesign
    Replies: 4
    Last Post: December 4th, 09:35 PM
  4. Replies: 0
    Last Post: September 22nd, 02:18 PM
  5. scp problems - again
    By Dr. David Kirkby in forum AIX
    Replies: 7
    Last Post: August 1st, 02:00 AM

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