Netezza Query takes forever and eventually times out, can this be re-written to run quicker?

  • cubangt

    SSC Enthusiast

    Points: 129

    SELECT
                  SS.ITEM,SS.SHIPMENT, MAX(SS.PROCESS_DATE) PROCESS_DATE
    FROM SHIPMINTS SS
            where SS.shipment > 14959039
            and SS.QTY_RECEIVED is not null
            and SS.QTY_RECEIVED <> 0
    GROUP BY SS.ITEM,SS.SHIPMENT

    I believe its the MAX around the date that is causing this query to run really long and time out.

  • yelouati

    SSC Enthusiast

    Points: 108

    You might get some luck from the following index:

    create index IDX_... on SHIPMINTS
    (SHIPMENT,ITEM,Process_Date)
    INCLUDE
    (QTY_Received)

    I assume that QTY_Received is not selective.

    Best of luck

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    cubangt - Friday, March 15, 2019 8:45 AM

    SELECT
                  SS.ITEM,SS.SHIPMENT, MAX(SS.PROCESS_DATE) PROCESS_DATE
    FROM SHIPMINTS SS
            where SS.shipment > 14959039
            and SS.QTY_RECEIVED is not null
            and SS.QTY_RECEIVED <> 0
    GROUP BY SS.ITEM,SS.SHIPMENT

    I believe its the MAX around the date that is causing this query to run really long and time out.

    There are several things which could cause this, suggest you dig into the logs and try to find the cause there.
    😎

  • Jeff Moden

    SSC Guru

    Points: 993644

    cubangt - Friday, March 15, 2019 8:45 AM

    SELECT
                  SS.ITEM,SS.SHIPMENT, MAX(SS.PROCESS_DATE) PROCESS_DATE
    FROM SHIPMINTS SS
            where SS.shipment > 14959039
            and SS.QTY_RECEIVED is not null
            and SS.QTY_RECEIVED <> 0
    GROUP BY SS.ITEM,SS.SHIPMENT

    I believe its the MAX around the date that is causing this query to run really long and time out.

    Can the QTY_RECEIVED ever be less than zero?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • cubangt

    SSC Enthusiast

    Points: 129

    yelouati - Friday, March 15, 2019 9:54 PM

    You might get some luck from the following index:

    create index IDX_... on SHIPMINTS
    (SHIPMENT,ITEM,Process_Date)
    INCLUDE
    (QTY_Received)

    I assume that QTY_Received is not selective.

    Best of luck

    We are not DB admins, but we are allowed access to query the tables. so would we be able to create the indexes? If not then we wouldnt be able to execute the above.

  • cubangt

    SSC Enthusiast

    Points: 129

    Jeff Moden - Saturday, March 16, 2019 2:54 PM

    cubangt - Friday, March 15, 2019 8:45 AM

    SELECT
                  SS.ITEM,SS.SHIPMENT, MAX(SS.PROCESS_DATE) PROCESS_DATE
    FROM SHIPMINTS SS
            where SS.shipment > 14959039
            and SS.QTY_RECEIVED is not null
            and SS.QTY_RECEIVED <> 0
    GROUP BY SS.ITEM,SS.SHIPMENT

    I believe its the MAX around the date that is causing this query to run really long and time out.

    Can the QTY_RECEIVED ever be less than zero?

    Yes that qty_received can be negative until corrections are made.

  • yelouati

    SSC Enthusiast

    Points: 108

    A query rewrite may be possible but we'd need to know how the table is setup (partioned or not, what partition), and what indexes are on the table. Otherwise the query is simple and clear.

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
    and SS.QTY_RECEIVED <> 0 will filter out the nulls.

    This index will increase the speed a lot:
    CREATE NONCLUSTERED INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_INC_ITEM_PROCESS_DATE
         ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED)
    INCLUDE (ITEM,PROCESS_DATE)

  • Jeff Moden

    SSC Guru

    Points: 993644

    I've not worked with Netezza before.  To be honest, I've heard of it before but never dug in to find what it was all about.  It turns out to be (what some claim) to be the very first "MPP Appliance" system.  I bring that up because my understanding is that tables design and queries need to be written in a certain fashion (not known by me) in order to achieve performance.  I wonder if that's the issue here?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • cubangt

    SSC Enthusiast

    Points: 129

    Jonathan AC Roberts - Monday, March 18, 2019 8:28 PM

    It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
    and SS.QTY_RECEIVED <> 0 will filter out the nulls.

    This index will increase the speed a lot:
    CREATE NONCLUSTERED INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_INC_ITEM_PROCESS_DATE
         ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED)
    INCLUDE (ITEM,PROCESS_DATE)

    When running the create index we get this error
    I get the error that indexes are not available in this release.
    After a little research found that Netezza does not use indexes like regular SQL.

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    cubangt - Tuesday, March 19, 2019 3:26 PM

    Jonathan AC Roberts - Monday, March 18, 2019 8:28 PM

    It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
    and SS.QTY_RECEIVED <> 0 will filter out the nulls.

    This index will increase the speed a lot:
    CREATE NONCLUSTERED INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_INC_ITEM_PROCESS_DATE
         ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED)
    INCLUDE (ITEM,PROCESS_DATE)

    When running the create index we get this error
    I get the error that indexes are not available in this release.
    After a little research found that Netezza does not use indexes like regular SQL.

    Yes, I don't think Netezza uses SQL Server.
    They have a CREATE INDEX statement that doesn't have an INCLUDE. So you would need to create an index like this:

    CREATE INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_ITEM_PROCESS_DATE
        ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED, ITEM, PROCESS_DATE)

  • frederico_fonseca

    SSChampion

    Points: 14001

    Netezza optimization is a bit different than other db's.

    I did find this article Analyze and Optimize Netezza Query Performance[/url] which is probably worthwhile reading

  • Jeff Moden

    SSC Guru

    Points: 993644

    Jonathan AC Roberts - Tuesday, March 19, 2019 4:02 PM

    cubangt - Tuesday, March 19, 2019 3:26 PM

    Jonathan AC Roberts - Monday, March 18, 2019 8:28 PM

    It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
    and SS.QTY_RECEIVED <> 0 will filter out the nulls.

    This index will increase the speed a lot:
    CREATE NONCLUSTERED INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_INC_ITEM_PROCESS_DATE
         ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED)
    INCLUDE (ITEM,PROCESS_DATE)

    When running the create index we get this error
    I get the error that indexes are not available in this release.
    After a little research found that Netezza does not use indexes like regular SQL.

    Yes, I don't think Netezza uses SQL Server.
    They have a CREATE INDEX statement that doesn't have an INCLUDE. So you would need to create an index like this:

    CREATE INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_ITEM_PROCESS_DATE
        ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED, ITEM, PROCESS_DATE)

    Again, I'm no expert on it but I believe they refer to it as "Netezza SQL"... it appears to have some commonality with ANSI SQL but a whole lot that's apparently necessarily proprietary.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Jeff Moden - Wednesday, March 20, 2019 5:21 AM

    Jonathan AC Roberts - Tuesday, March 19, 2019 4:02 PM

    cubangt - Tuesday, March 19, 2019 3:26 PM

    Jonathan AC Roberts - Monday, March 18, 2019 8:28 PM

    It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
    and SS.QTY_RECEIVED <> 0 will filter out the nulls.

    This index will increase the speed a lot:
    CREATE NONCLUSTERED INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_INC_ITEM_PROCESS_DATE
         ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED)
    INCLUDE (ITEM,PROCESS_DATE)

    When running the create index we get this error
    I get the error that indexes are not available in this release.
    After a little research found that Netezza does not use indexes like regular SQL.

    Yes, I don't think Netezza uses SQL Server.
    They have a CREATE INDEX statement that doesn't have an INCLUDE. So you would need to create an index like this:

    CREATE INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_ITEM_PROCESS_DATE
        ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED, ITEM, PROCESS_DATE)

    Again, I'm no expert on it but I believe they refer to it as "Netezza SQL"... it appears to have some commonality with ANSI SQL but a whole lot that's apparently necessarily proprietary.

    Netezza SQL is very similar to the T-SQL on Azure Data Warehouse for the DML, only a handful of NTSQL functions that do not exist in T-SQL, slight difference on the DDL but not much.
    😎
    Currently migrating a large Netezza estate to Azure so I had to look a bit into this😉

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    Jeff Moden - Wednesday, March 20, 2019 5:21 AM

    Jonathan AC Roberts - Tuesday, March 19, 2019 4:02 PM

    cubangt - Tuesday, March 19, 2019 3:26 PM

    Jonathan AC Roberts - Monday, March 18, 2019 8:28 PM

    It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
    and SS.QTY_RECEIVED <> 0 will filter out the nulls.

    This index will increase the speed a lot:
    CREATE NONCLUSTERED INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_INC_ITEM_PROCESS_DATE
         ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED)
    INCLUDE (ITEM,PROCESS_DATE)

    When running the create index we get this error
    I get the error that indexes are not available in this release.
    After a little research found that Netezza does not use indexes like regular SQL.

    Yes, I don't think Netezza uses SQL Server.
    They have a CREATE INDEX statement that doesn't have an INCLUDE. So you would need to create an index like this:

    CREATE INDEX IX_SHIPMENT_SHIPMENT_QTY_RECEIVED_ITEM_PROCESS_DATE
        ON [dbo].[SHIPMENT] (SHIPMENT, QTY_RECEIVED, ITEM, PROCESS_DATE)

    Again, I'm no expert on it but I believe they refer to it as "Netezza SQL"... it appears to have some commonality with ANSI SQL but a whole lot that's apparently necessarily proprietary.

    I'd never actually heard of Netezza until I saw this question but apparently it's based on PostgreSQL but has forked off since then. 
    https://en.wikipedia.org/wiki/Netezza

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply