All Forums Database
littldo 7 posts Joined 07/04
19 Jun 2009
varchar length performance implications

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 differencesTable A Table BColumnName Type Length ColumnName Type Lengthtrip_type VARCHAR 4 trip_type VARCHAR 128srch_hotel_text VARCHAR 200 srch_hotel_text VARCHAR 1,800source_of_traffic DECIMAL 1 source_of_traffic DECIMAL 8agency_code VARCHAR 40 agency_code VARCHAR 128row length 821 2640Any thoughts on why? I want to keep the longer lengths, but not at 4x performance decrease.queryEXPLAIN SELECT x.SearchOptionsEventTimeID,x.SearchOptionsEventTime,x. VisitID,x.VisitorID,x.source_of_traffic,x.lasthit, ch_crusie_name,x.trip_type,x.srch_hotel_text,x.AgencyC ode,MAX(CASE WHEN x.Ranking = 1 THEN x.srch_hotel_chain ELSE NULL END ) SearchOption1,MAX(CASE WHEN x.Ranking = 2 THEN x.srch_hotel_chain ELSE NULL END ) SearchOption2,MAX(CASE WHEN x.Ranking = 3 THEN x.srch_hotel_chain ELSE NULL END ) SearchOption3 FROM (SELECT SearchOptionsEventID,SearchOptionsEventTimeID,SearchOp tionsEventTime,SearchOptionsEventTimeGMT,VisitID,Visit orID,source_of_traffic,lasthit,agency_code,srch_crusie _name,srch_hotel_chain,trip_type,srch_hotel_text,Agenc yCode,RANK() OVER(PARTITION BY SearchOptionsEventTime,VisitID,visitorid, source_of_traffic,lasthit,srch_crusie_name,trip_type,s rch_hotel_text, AgencyCode ORDER BY SearchOptionsEventID) Ranking FROM da_test.STG_WTCE17_SearchOptEvt_test) x GROUP BY 1,2,3,4,5,6,7,8,9,10

robpaller 159 posts Joined 05/09
29 Jun 2009

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.

You must sign in to leave a comment.