March 20, 2019 at 5:37 am
Eirikur Eiriksson - Wednesday, March 20, 2019 5:32 AMJeff Moden - Wednesday, March 20, 2019 5:21 AMJonathan AC Roberts - Tuesday, March 19, 2019 4:02 PMcubangt - Tuesday, March 19, 2019 3:26 PMJonathan AC Roberts - Monday, March 18, 2019 8:28 PMIt's not going to make much difference to the performance butand SS.QTY_RECEIVED is not nullis not required becauseand SS.QTY_RECEIVED <> 0will 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😉
Since you have an opportunity that most will never have, it would be interesting to see what the performance differences will be. Rumor has it that unless you're into premium packages, the performance of Azure leaves quite a bit to be desired.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2019 at 5:40 am
Jonathan AC Roberts - Wednesday, March 20, 2019 5:34 AMJeff Moden - Wednesday, March 20, 2019 5:21 AMJonathan AC Roberts - Tuesday, March 19, 2019 4:02 PMcubangt - Tuesday, March 19, 2019 3:26 PMJonathan AC Roberts - Monday, March 18, 2019 8:28 PMIt's not going to make much difference to the performance butand SS.QTY_RECEIVED is not nullis not required becauseand SS.QTY_RECEIVED <> 0will 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
Interesting... thanks, Jonathan.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2019 at 6:14 am
Jeff Moden - Wednesday, March 20, 2019 5:37 AMEirikur Eiriksson - Wednesday, March 20, 2019 5:32 AMJeff Moden - Wednesday, March 20, 2019 5:21 AMJonathan AC Roberts - Tuesday, March 19, 2019 4:02 PMcubangt - Tuesday, March 19, 2019 3:26 PMJonathan AC Roberts - Monday, March 18, 2019 8:28 PMIt's not going to make much difference to the performance butand SS.QTY_RECEIVED is not nullis not required becauseand SS.QTY_RECEIVED <> 0will 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😉Since you have an opportunity that most will never have, it would be interesting to see what the performance differences will be. Rumor has it that unless you're into premium packages, the performance of Azure leaves quite a bit to be desired.
Azure is like the Missuses hair colour, changes every day 😀
😎
The latest generation of Azure SQL Data Warehouse performs very well and cost wise, it is very competitive.
March 20, 2019 at 6:43 am
Eirikur Eiriksson - Wednesday, March 20, 2019 6:14 AMJeff Moden - Wednesday, March 20, 2019 5:37 AMEirikur Eiriksson - Wednesday, March 20, 2019 5:32 AMJeff Moden - Wednesday, March 20, 2019 5:21 AMJonathan AC Roberts - Tuesday, March 19, 2019 4:02 PMcubangt - Tuesday, March 19, 2019 3:26 PMJonathan AC Roberts - Monday, March 18, 2019 8:28 PMIt's not going to make much difference to the performance butand SS.QTY_RECEIVED is not nullis not required becauseand SS.QTY_RECEIVED <> 0will 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😉Since you have an opportunity that most will never have, it would be interesting to see what the performance differences will be. Rumor has it that unless you're into premium packages, the performance of Azure leaves quite a bit to be desired.
Azure is like the Missuses hair colour, changes every day 😀
😎
The latest generation of Azure SQL Data Warehouse performs very well and cost wise, it is very competitive.
Cool feedback. Thanks, Eirikur.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2019 at 7:10 am
Your SQL query itself is straightforward. Other than a SQL interface, Netezza is very different architecturally from SQL Server, more similar to Azure SQL Warehouse. It uses a distributed file system similar to HDFS, and from what I've read, it doesn't support b-tree indexes but something like materialized views and statistics. Since what you're struggling with is performance, you'll get more useful advice from a Netezza specific user forum or the vendor.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 21, 2019 at 9:57 am
I would ask a dB admin.
You are picking above a certain shipment number for some business reason.
It is not clear if there are multiple items on the same shipment.
And you ignore negative qty although depending on how the table is structured and corrections are handled may not be giving you the expected results.
It might be as simple as creating an inner query without the max date, where an outer query selects the max date for item shipment from the sub query.
Be sure to have a clear business description of what the query is intended to do.
And if the results of this would lead to some other questions, describe those.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply