Hello, I have 2 tables populated with the same data (2.7m rows), the only difference being the max length of the varchar columns and decimal columns. given the data is the same, seems to me that aggregation performance should be the similar, but I am seeing wildly different runtimes 5m vs 21min. tablesize is 658m vs 636m, skew is same for both tables.
I've collected/refreshed stats of the same columns from the tables.
table differences Table A Table B ColumnName Type Length ColumnName Type Length trip_type VARCHAR 4 trip_type VARCHAR 128 srch_hotel_text VARCHAR 200 srch_hotel_text VARCHAR 1,800 source_of_traffic DECIMAL 1 source_of_traffic DECIMAL 8 agency_code VARCHAR 40 agency_code VARCHAR 128 row length 821 2640
Any thoughts on why? I want to keep the longer lengths, but not at 4x performance decrease.
If the field 'srch_hotel_text' in TableB contains expanded values (lengths exceeding 200 characters) from TableA the fact that it is included in the Partition By of the RANK() function is the likely culprit for your performance difference.