Loading ...
Sorry, an error occurred while loading the content.

RE: Direct SQL with MSSQL 2005 and a large table & result set

Expand Messages
  • Brookes, Alan
    Hi Mark. ... This would mean a change in behaviour. If by Generate you mean Ctrl+G, Magic will use the first defined index, not necessarily the PK. Indeed,
    Message 1 of 14 , Jul 1 12:05 AM
    • 0 Attachment
      Hi Mark.

      >>(because of course Generate uses the PK).

      This would mean a change in behaviour. If by "Generate" you mean Ctrl+G,
      Magic will use the first defined index, not necessarily the PK. Indeed,
      almost none of my tables have PKs.

      I've never known the optimizer to get much wrong. Are there any joins in
      your query?


      Alan


      -----Original Message-----
      From: Mark Henderson [mailto:thefarseeker@...]
      Sent: Tuesday, July 01, 2008 1:15 AM
      To: magicu-l@yahoogroups.com
      Subject: Direct SQL with MSSQL 2005 and a large table & result set

      Hi All,

      Thanks for your responses.

      We're usin the Microsoft SQL Gateway. We did a Show Plan, and its
      putting 85% of processing time down to "Sort", which is very odd
      because we are ranging on key segments (We got rid of the Order By
      and replaced it with an Index Hint which helped, but not by much).

      The reason it's returning such a huge amount of data is because the
      user doesn't always know exactly what (or rather, whome) they are
      looking for, so it returns a wide result set which then then
      locate/range on. Not ideal I know.

      The other odd thing that we found after posting yesterday is that if
      we range on the Primary Key it's almost instant! Which makes me think
      it's an MSSQL Optimisation Problem, not a Magic problem (because of
      course Generate uses the PK).

      Regards,

      Mark


      --- In magicu-l@yahoogroups.com, "kdwolf" <kdwolf@...> wrote:
      >
      > In addition to Mike's reply,
      >
      > Do you use ODBC or MicrosoftSQL getway? Hope the second one.
      > Any chance you have in your Settings > DBMS > MicrosoftSQL > Ctrl+P
      >
      > Log Level turned on? If yes - it is a reason for the slow
      > performance, if not - did you try to change it once to Developer
      > level to understand what is going on there? Can you do it once to
      get
      > the picture of your SQL statement?
      >
      > Of course it is a good question why do you need all 500K rows, but
      > even if yes :-D, it still takes too much time if you use the
      correct
      > segments...
      >
      >
      > --- In magicu-l@yahoogroups.com, "Mike Bannon" <mikeb@> wrote:
      > >
      > > Mark
      > >
      > > Try turning off the use of cursors for the table: go the table,
      > select
      > > Properties, SQL tab, set Cursor = No.
      > >
      > > Until your last point, that it worked ok with a Ctrl-G, I was
      > confident that
      > > this was the problem - now I'm not so sure, but it's worth a try.
      > It has
      > > made a remarkable difference for me in some similar circumstances.
      > >
      > > One question, though, what benefit does a user get from browsing
      a
      > data set
      > > of 100k to 500k records? Are they going to look at each one?
      > (rhetorical
      > > question ;-) ). They are going to have to additionally filter
      this
      > data
      > > somehow, so couldn't you instead offer additional range criteria
      > for the use
      > > to enter before getting the result set?
      > >
      > > HTH
      > >
      > > Mike Bannon
      > >
      > >
      > >
      > > www.dataformation.co.uk <http://www.dataformation.co.uk/>
      > >
      > > Mobile : 07855 496166
      > > Office : 01524 840100
      > >
      > >
      > >
      > > _____
      > >
      > > From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com]
      On
      > Behalf
      > > Of Mark Henderson
      > > Sent: 30 June 2008 07:26
      > > To: magicu-l@yahoogroups.com
      > > Subject: [SPAM][magicu-l] Direct SQL with MSSQL 2005 and a large
      > table &
      > > result set
      > >
      > >
      > >
      > > Hi All,
      > >
      > > Just asking for your advice in a situation that we have. We're
      > running
      > > Magic 9.4 SP7a
      > >
      > > We have a query for an online browse screen that returns over 100
      > > columns and anywhere between 100,000 and 500,000 records.
      > >
      > > When we design the screen in a normal Magic application (no
      Direct
      > SQL,
      > > just LOJ's and Link Queries) it's hideously slow to page up/down
      > and
      > > takes many seconds for the window to load.
      > >
      > > When we design a Direct SQL query it's... wait for it... even
      > SLOWER
      > > because Magic waits (honestly) 12 minutes whilst it does I don't
      > know
      > > what, and an SQL record counter appears - but it's REALLY fast
      when
      > > it's loaded (Result Database is Pervasive, we figured its
      probably
      > too
      > > much for a Memory table).
      > >
      > > When we open the table with a Generate from the Table Repository
      > (with
      > > requiste LOJ's as non-database FKs) it's instant, the data is
      there
      > and
      > > completely browseable without waiting for all 750,000 records to
      > > populate.
      > >
      > > Does anyone have any suggestions on what the root cause might be
      > and
      > > how we can have the awesome performance of the generate screen
      but
      > the
      > > functionality of a full online?
      Sent by E2V TECHNOLOGIES PLC or a member of the E2V group of companies. A
      company registered in England and Wales.
      Company number; 04439718.
      Registered address; 106 Waterhouse Lane, Chelmsford, Essex, CM1 2QU, UK.

      ______________________________________________________________________
      This email has been scanned by the MessageLabs Email Security System.
      For more information please visit http://www.messagelabs.com/email
      ______________________________________________________________________
    • Mike Bannon
      Mark I think there may be some confusion here. You say that generate ranges on the primary key. When working with a SQL database the key you specify in Magic
      Message 2 of 14 , Jul 1 3:04 AM
      • 0 Attachment
        Mark

        I think there may be some confusion here. You say that generate 'ranges' on
        the primary key. When working with a SQL database the key you specify in
        Magic is only used to sort the data, by adding an Order By clause to the
        query sent to the SQL server. The actual indexes used are determined by the
        SQL server's optimiser.

        I'm assuming, therefore, that in your 1st paragraph when you say you are
        ranging on key segments, that you mean you are specifying a Magic index
        which maps to physical key segments, but not the primary key, as opposed to
        when using Ctrl-G, when Magic uses the primary key. In both cases, this only
        specifies the sort order. If I have understood this correctly, this implies
        that the server sorts the result set more quickly when sorting by the
        primary key. This is probably to be expected for a number of reasons, index
        clustering being one of the main ones. I assume, however, that for your
        application you need to sort on something other than the primary key, so
        this doesn't help.

        Can I suggest you try the following: turn on logging for the SQL DBMS (in
        DBMS properties). You will need to exit Magic for this to take effect.
        Re-enter Magic and run your query as a pure Magic task - i.e. not using
        direct SQL. Note how long it takes. Exit Magic - oh yes, turn off DBMS
        logging first, unless you want to keep it on for development - and open the
        log file in notepad. Copy the SQL statement which Magic issued and run that
        in Query Analyser. If this runs considerably faster, then it could be
        because of Magic's use of the cursor. Try setting Cursor=No in the table's
        properties in Magic. Then try it again and see if it improves things.

        If this doesn't help, I think you need to re-address you programme design.
        The root cause of the problem is the size of the data set you are returning.
        Given that the users are subsequently going to be ranging or locating within
        this data set, I suggest that you give them a set of virtuals in which to
        enter their range criteria before running the task in question, and add
        these criteria to the task's range, using CNDRANGE where necessary. If you
        give them the options of pre-entering ranges on all the fields they may
        require, this should considerably reduce the size of the result set.

        Regards

        Mike Bannon



        www.dataformation.co.uk <http://www.dataformation.co.uk/>

        Mobile : 07855 496166
        Office : 01524 840100



        _____

        From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On Behalf
        Of Mark Henderson
        Sent: 01 July 2008 01:15
        To: magicu-l@yahoogroups.com
        Subject: [SPAM]Re: [SPAM][magicu-l] Direct SQL with MSSQL 2005 and a large
        table & result set



        Hi All,

        Thanks for your responses.

        We're usin the Microsoft SQL Gateway. We did a Show Plan, and its
        putting 85% of processing time down to "Sort", which is very odd
        because we are ranging on key segments (We got rid of the Order By
        and replaced it with an Index Hint which helped, but not by much).

        The reason it's returning such a huge amount of data is because the
        user doesn't always know exactly what (or rather, whome) they are
        looking for, so it returns a wide result set which then then
        locate/range on. Not ideal I know.

        The other odd thing that we found after posting yesterday is that if
        we range on the Primary Key it's almost instant! Which makes me think
        it's an MSSQL Optimisation Problem, not a Magic problem (because of
        course Generate uses the PK).

        Regards,

        Mark

        --- In magicu-l@yahoogroup <mailto:magicu-l%40yahoogroups.com> s.com,
        "kdwolf" <kdwolf@...> wrote:
        >
        > In addition to Mike's reply,
        >
        > Do you use ODBC or MicrosoftSQL getway? Hope the second one.
        > Any chance you have in your Settings > DBMS > MicrosoftSQL > Ctrl+P
        >
        > Log Level turned on? If yes - it is a reason for the slow
        > performance, if not - did you try to change it once to Developer
        > level to understand what is going on there? Can you do it once to
        get
        > the picture of your SQL statement?
        >
        > Of course it is a good question why do you need all 500K rows, but
        > even if yes :-D, it still takes too much time if you use the
        correct
        > segments...
        >
        >
        > --- In magicu-l@yahoogroup <mailto:magicu-l%40yahoogroups.com> s.com,
        "Mike Bannon" <mikeb@> wrote:
        > >
        > > Mark
        > >
        > > Try turning off the use of cursors for the table: go the table,
        > select
        > > Properties, SQL tab, set Cursor = No.
        > >
        > > Until your last point, that it worked ok with a Ctrl-G, I was
        > confident that
        > > this was the problem - now I'm not so sure, but it's worth a try.
        > It has
        > > made a remarkable difference for me in some similar circumstances.
        > >
        > > One question, though, what benefit does a user get from browsing
        a
        > data set
        > > of 100k to 500k records? Are they going to look at each one?
        > (rhetorical
        > > question ;-) ). They are going to have to additionally filter
        this
        > data
        > > somehow, so couldn't you instead offer additional range criteria
        > for the use
        > > to enter before getting the result set?
        > >
        > > HTH
        > >
        > > Mike Bannon
        > >
        > >
        > >
        > > www.dataformation.co.uk <http://www.dataform
        <http://www.dataformation.co.uk/> ation.co.uk/>
        > >
        > > Mobile : 07855 496166
        > > Office : 01524 840100
        > >
        > >
        > >
        > > _____
        > >
        > > From: magicu-l@yahoogroup <mailto:magicu-l%40yahoogroups.com> s.com
        [mailto:magicu-l@yahoogroup <mailto:magicu-l%40yahoogroups.com> s.com]
        On
        > Behalf
        > > Of Mark Henderson
        > > Sent: 30 June 2008 07:26
        > > To: magicu-l@yahoogroup <mailto:magicu-l%40yahoogroups.com> s.com
        > > Subject: [SPAM][magicu-l] Direct SQL with MSSQL 2005 and a large
        > table &
        > > result set
        > >
        > >
        > >
        > > Hi All,
        > >
        > > Just asking for your advice in a situation that we have. We're
        > running
        > > Magic 9.4 SP7a
        > >
        > > We have a query for an online browse screen that returns over 100
        > > columns and anywhere between 100,000 and 500,000 records.
        > >
        > > When we design the screen in a normal Magic application (no
        Direct
        > SQL,
        > > just LOJ's and Link Queries) it's hideously slow to page up/down
        > and
        > > takes many seconds for the window to load.
        > >
        > > When we design a Direct SQL query it's... wait for it... even
        > SLOWER
        > > because Magic waits (honestly) 12 minutes whilst it does I don't
        > know
        > > what, and an SQL record counter appears - but it's REALLY fast
        when
        > > it's loaded (Result Database is Pervasive, we figured its
        probably
        > too
        > > much for a Memory table).
        > >
        > > When we open the table with a Generate from the Table Repository
        > (with
        > > requiste LOJ's as non-database FKs) it's instant, the data is
        there
        > and
        > > completely browseable without waiting for all 750,000 records to
        > > populate.
        > >
        > > Does anyone have any suggestions on what the root cause might be
        > and
        > > how we can have the awesome performance of the generate screen
        but
        > the
        > > functionality of a full online?
        > >
        > >
        > >
        > >
        > >
        > >
        > > [Non-text portions of this message have been removed]
        > >
        >






        [Non-text portions of this message have been removed]
      Your message has been successfully submitted and would be delivered to recipients shortly.