SQL (Netezza) Query Performance

Question

I have a table that has 26 columns and over 10^8 rows and contains some stock market data. I am performing a pretty basic query but it isn't as performant as I'd expect:

select * from tableA where (cusip_id, mkt_close_dt) in (
               select cusip_id, max(mkt_close_dt) from tableA
               where mkt_close_dt >= '2012-12-25' and mkt_close_dt <= '2013-01-01'
               and cusip_id in ('025081753', '04314H709', '115291833', 
               '086233103', '119530202', '22544R305', '256206103', 
               '256219106', '471023887', '476313101', '471023598', 
               '66537V336', '592905103', '665162400', '779547108', 
               '741481105', '77956H864', '693391682', '779557107', 
               '77957P105', '693390700', '77956H104', '780905709', 
               '784924789', '880208400', '885215566', '779919109', 
               '00141A545', '09256H328', '31429A105', '471023762', 
               '04315J860', '543495840', '592905848', '592905509', 
               '693390130', '921937793', '78464A516', '73935S105', 
               '464287465', '464288281', '464288612', '464288869', 
               '464287630', '464287648', '464287481', '78464A417', 
               '464288588', '464288273', '464287457', '464287176', 
               '922908553', '922908512', '922908744', '922908736', 
               '922042858', '464287226', '921937835', '902641679', 
               '06738C778', '78464A854', '78464A847', '33734K109', 
               '33735B108', '33734Y109', '464288596', '464287507', 
               '464287804', '464287150', '464287200', '464287622', 
               '464287655', '464287499', '464287689', '464287846', 
               '464287127', '464288208', '464288505', '78464A649', 
               '72201R304', '78467Y107', '921910873', '464287135', 
               '464287101', '73935X450', '73935X443', '73935X435', 
               '78355W106', '78463X863', '808524607', '808524102', 
               '808524201', '78464A813', '78462F103', '72201R205', 
               '78464A664', '464287432', '78464A805', '922908751', 
               '921908844', '922908629', '922908413', '922908769', 
               '922908637', '922908652', '18383M498', '78464A490', 
               '78355W205', '921937827', '464288422', '81369Y407', 
               '73935X195', '81369Y605', '81369Y100', '81369Y308', 
               '81369Y506', '81369Y886', '81369Y209', '81369Y704', 
               '81369Y803', '92206C664') group by cusip_id

Note that I will be creating the list of cusip_id dynamically (i.e., it will be constantly changing) and the mkt_close_dt will also vary. Finally, I don't want to use a temp table either. Given these constraint, how can I possibly improve performance?

Answer 1

It looks like you are trying to get the records from the maximum date for a given cusip_id given those conditions.

I would recommend window functions:

select t.*
from (select a.*,
             max(mkt_close_dt) over (partition by cusip_id) as maxdt
      from tableA a
      where mkt_close_dt >= '2012-12-25' and mkt_close_dt <= '2013-01-01' and
            cusip_id in ('025081753', '04314H709', '115291833', 
               '086233103', '119530202', '22544R305', '256206103', 
               '256219106', '471023887', '476313101', '471023598', 
               '66537V336', '592905103', '665162400', '779547108', 
               '741481105', '77956H864', '693391682', '779557107', 
               '77957P105', '693390700', '77956H104', '780905709', 
               '784924789', '880208400', '885215566', '779919109', 
               '00141A545', '09256H328', '31429A105', '471023762', 
               '04315J860', '543495840', '592905848', '592905509', 
               '693390130', '921937793', '78464A516', '73935S105', 
               '464287465', '464288281', '464288612', '464288869', 
               '464287630', '464287648', '464287481', '78464A417', 
               '464288588', '464288273', '464287457', '464287176', 
               '922908553', '922908512', '922908744', '922908736', 
               '922042858', '464287226', '921937835', '902641679', 
               '06738C778', '78464A854', '78464A847', '33734K109', 
               '33735B108', '33734Y109', '464288596', '464287507', 
               '464287804', '464287150', '464287200', '464287622', 
               '464287655', '464287499', '464287689', '464287846', 
               '464287127', '464288208', '464288505', '78464A649', 
               '72201R304', '78467Y107', '921910873', '464287135', 
               '464287101', '73935X450', '73935X443', '73935X435', 
               '78355W106', '78463X863', '808524607', '808524102', 
               '808524201', '78464A813', '78462F103', '72201R205', 
               '78464A664', '464287432', '78464A805', '922908751', 
               '921908844', '922908629', '922908413', '922908769', 
               '922908637', '922908652', '18383M498', '78464A490', 
               '78355W205', '921937827', '464288422', '81369Y407', 
               '73935X195', '81369Y605', '81369Y100', '81369Y308', 
               '81369Y506', '81369Y886', '81369Y209', '81369Y704', 
               '81369Y803', '92206C664')
     ) t
where mkt_close_dt = maxdt;