Professional Web Applications Themes

The Joys of the Jet Engine - Informix

Informix Dynamic Server Version 7.31.UD5 DYNIX/ptx 4.0 V4.4.10 i386 SunOS 5.8 Generic_108528-20 sun4u sparc SUNW,Sun-Fire-480R Access 97 ODBC Driver INFORMIX 3.80 32 BIT I unfortunately have a business user created MSAccess group of databases that defies belief, but, guess who has to try and make some sense of the structure and queries. I have been attempting to create some of this summarised data on the Sun, extracted from the DYNIX (UmaQ), linking these tables into the MSAccess environment. We are not getting the performance we would hope for, although, not totally unexpected. The business are refusing to give up MSAccess ...

  1. #1

    Default The Joys of the Jet Engine


    Informix Dynamic Server Version 7.31.UD5
    DYNIX/ptx 4.0 V4.4.10 i386
    SunOS 5.8 Generic_108528-20 sun4u sparc SUNW,Sun-Fire-480R

    Access 97
    ODBC Driver INFORMIX 3.80 32 BIT

    I unfortunately have a business user created MSAccess group of databases
    that defies belief, but, guess who has to try and make some sense of the
    structure and queries. I have been attempting to create some of this
    summarised data on the Sun, extracted from the DYNIX (UmaQ), linking these
    tables into the MSAccess environment. We are not getting the performance we
    would hope for, although, not totally unexpected.

    The business are refusing to give up MSAccess as they wish to continue to
    produce a multitude of adhoc queries, which only make the whole position
    worse. The databases are creaking at the seems and they need compacting
    every week to avoid the size limitations. We have already moved tables into
    their own database and with the growth continuing they will cease to
    function within a short timescale.

    Has anyone experience using large tables with complex queries in MSAccess
    linked to Informix tables? Would you be prepared to impart your knowledge to
    guide me in this? In the long term we will have to provide a back-end
    application with some client-side FE, but, initially I must try and stop the
    environment colapsing. I do have some linked tables that appear to perform
    reasonably well but the more complicated enquiries just fail to perform.

    TIA
    Geoff


    ************************************************** ********************************
    This message is sent in strict confidence for the addressee only. It may
    contain legally privileged information. The contents are not to be disclosed
    to anyone other than the addressee. Unauthorised recipients are requested
    to preserve this confidentiality and to advise the sender immediately of any
    error in transmission.
    This footnote also confirms that this email message has been swept for the
    presence of computer viruses, however we cannot guarantee that this message
    is free from such problems.
    ************************************************** ********************************

    sending to informix-list
    Roff, Guest

  2. #2

    Default Re: The Joys of the Jet Engine

    "Roff, Geoff" <biz> writes: 
    .... 

    Access is single threaded. Access 97 has some *serious* bugs.
    Have you tried upgrading to Access 200[023]?

    There may not be a solution here as the business has clearly waited
    too long if they're spending so much on continued maintenance of their
    legacy system that they cannot spend any on R&D for their next system
    and migration (which it sounds like they are in desperate need of).

    --
    Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
    Ronald Cole <com> Phone: (760) 499-9142
    President, CEO Fax: (760) 499-9152
    My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B
    Ronald Guest

  3. #3

    Default Re: The Joys of the Jet Engine

    Geoff,

    If you attach two Informix tables separately and run a query using both of them
    in Access, it downloads the two entire tables before joining them. The solution
    is to create Informix views containing all the conditions and joins, and
    returning only the columns you need, so that you have optimised queries with the
    amount of data passed over the network minimised.

    If you still have some queries taking a long time, you need to increase the ODBC
    timeout, as I mentioned in another thread in November as listed below.

    Regards,
    Doug Lawry
    www.douglawry.webhop.org


    I recommend increasing the timeout both in the ODBC driver and the Access
    query. If you are using Windows XP, enable the Administrative Tools menu
    first:

    * Right click on the task bar and choose "Properties"
    * Choose "Customize" on the "Start Menu" tab
    * Turn on the "Administrative Tools" menu

    To increase the ODBC driver timeout:

    * Select Programs / Administrative Tools / Data Sources (ODBC)
    * Click on the "Connection Pooling" tab
    * Double click on "IBM INFORMIX 3.82 32 BIT"
    * Enable "Pool Connections to this driver"
    * Set the timeout you require

    A search for "timeout" in the online help facility in Access 2002 returns:

    "Set multiuser query options

    Specify how long before a query displays an ODBC time-out error

    A query that uses an ODBC database as its underlying source of data might
    encounter delays due to network traffic or heavy use of the ODBC server.

    1. Open a query in Design view.
    2. Select the query by clicking anywhere in query Design view outside the
    design grid and the field lists.
    3. Click Properties on the toolbar to display the query's property sheet.
    4. Set the ODBCTimeout property to the number of seconds that the query
    waits before displaying a time-out error. The default is 60 seconds.

    Note: If you set the property to zero, no time-out error occurs."


    "Roff, Geoff" <biz> wrote in message
    news:brs93u$87s$xmission.com... 


    Doug Guest

Similar Threads

  1. Search Engine Help
    By Talson webforumsuser@macromedia.com in forum HTML & CSS
    Replies: 6
    Last Post: November 29th, 10:50 PM
  2. Search engine
    By Jeremy in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 15
    Last Post: September 15th, 03:18 PM
  3. THe joys of owning a Canon DSLR
    By Douglas MacDonald in forum Photography
    Replies: 5
    Last Post: November 18th, 02:13 PM
  4. A new 3D engine?
    By cheyennecloud webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 20
    Last Post: January 14th, 01:44 AM
  5. ASP Search Engine
    By Michael in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 25th, 06:54 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