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

Question

Post reply

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

cubangt

SSC Enthusiast

Points: 189
More actions
March 15, 2019 at 8:45 am

#380052

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.

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

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

yelouati SSC Enthusiast Points: 108 More actions · Answer 1

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: 182911 More actions · Answer 2

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: 1004321 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cubangt SSC Enthusiast Points: 189 More actions · Answer 4

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: 189 More actions · Answer 5

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 More actions · Answer 6

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: 18354 More actions · Answer 7

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: 1004321 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cubangt SSC Enthusiast Points: 189 More actions · Answer 9

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: 18354 More actions · Answer 10

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 SSCoach Points: 16154 More actions · Answer 11

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: 1004321 More actions · Answer 12

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 13

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: 18354 More actions · Answer 14

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