Announcement

Collapse
No announcement yet.

Grid application take too long time to load

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Grid application take too long time to load

    Hi There.
    I have a situation and do not know exactly where the problem is.
    I have two tables: client and client_addresses.
    The client has about 5000 records, and every client has in client_address one or two addresses.
    A client can have multiple addresses specified in the table client addresses, but one is the default.
    If I create a grid application that take data only from table client it's run ok (fast).
    If I create a grid application that show the client and the default addresses it's work very slow, it's take about 20 sec.
    I tested this in two way in SQL Select Statement.

    1. I use subselect query like
    SELECT
    id_client,
    name,
    (SELECT address FROM client_address WHERE id_client=cl.id_client AND default=1) AS address,
    email
    FROM
    client cl

    2. I use INNER JOIN like
    SELECT
    client.id_client,
    client.name,
    client_address.address,
    client.email
    FROM
    client
    LEFT OUTER JOIN client_address ON client.id_client = client_address.id_client AND client_address.default=1

    The interesting think is that I run this two query in other context, the data is load and show very fast.
    For example, I created in a blank application a simple table that is filled with data using the query mentioned above and it's loading very fast.
    So it's not a problem with speed of server.
    If I use exactly the same query but in grid application, the application it's load very slow, also when navigated from a page to another or when I clicked the advanced search or quick search.
    Can be this a bug in grid application ?
    If not, why in grid application work sow slow and otherwise work fast ?

    Thanks

  • #2
    I think your problem is that there is no LIMIT being generated in your sql.

    If that is true, then the query is not what is taking time, it is the entire 5000 records getting transferred to your browser.

    Change your "Pagination" from Total to Partial, and set a number of lines.

    If that is not the problem, then you need to check if your client.id_client, client_address.id_client, and client_address.default are all indexed.

    But the fact that it runs fast in a blank application (which is probably only displaying one record) makes me think it is not the indexing, but the transfer that is taking the time.

    Dave
    Last edited by daveprue; 12-12-2014, 06:03 AM.
    Dave Prue
    Code Whisperer
    Lahar International Corp
    www.lahar.net

    Comment


    • #3
      It is selected partial and the limit is 15 / page.
      Like I say above, it's working slow also when navigate.

      Comment


      • #4
        Can you give this a try:

        Code:
        SELECT
        c.id_client, c.name, d.address, c.email
        FROM
        client as c, client_address as d
        WHERE
        c.id_client = d.id_client
        AND
        d.default=1
        and I am assuming that client.id_client, client_address.id_client and client_address.default are all indexed?


        Dave
        Last edited by daveprue; 12-12-2014, 12:02 PM.
        Dave Prue
        Code Whisperer
        Lahar International Corp
        www.lahar.net

        Comment


        • #5
          Yes are indexed

          Using this

          SELECT
          c.id_client,
          c.name,
          d.address,
          c.email
          FROM
          client as c,
          client_address as d
          WHERE
          c.id_client = d.id_client
          AND
          d.default=1

          Grid application is loading fast, also navigation is fast but advanced search it's not working.
          When i push the advanced search, it's show that page is loading and nothing happen.
          if I select in sql query fewer fields, advanced search it's working but after a few tens of seconds.
          So using this method, grid work ok but advanced search not work or is very slow, may be it's a bug in scriptcase.

          Comment


          • #6
            Yes, it sounds like scriptcase bug.

            I always use equi-joins (like the one that I gave you) simply because they seem to make more sense to me than inner/outer/left/right.

            I can see no reason why the two queries would take dramatically different times to execute, if indeed phpmyadmin responds in approximately the same time for either query, then I have to assume that scriptcase is at fault.


            Dave
            Dave Prue
            Code Whisperer
            Lahar International Corp
            www.lahar.net

            Comment

            Working...
            X