Slow query that needs improvement on DISTINCT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow query that needs improvement on DISTINCT

Author

Topic

rkruis
Starting Member

28 Posts

Posted - 2012-05-09 : 16:39:44

Can anyone help give me suggestions on how to improve this select statement?
In the execution plan it shows the Sort(Distinct Sort) is taking 99% cost.

And thank you ahead of time.


SELECT DISTINCT 
VCDB_BaseVehicle.YearId, 
VCDB_Year.YearId, 
VCDB_BaseVehicle.MakeId, 
RTRIM(VCDB_Make.MakeName), 
VCDB_BaseVehicle.ModelId, 
RTRIM(VCDB_Model.ModelName), 
VCDB_DormanManufacturer.DormanManufacturerID, 
RTRIM(VCDB_DormanManufacturer.DormanManufacturerName), 
RTRIM([ID_26900_LANG_1].Field1), 
RTRIM([ID_26600_LANG_1].FIELD2), 
RTRIM([ID_26700_LANG_1].Field3), 
RTRIM([ID_26700_LANG_1].Field5), 
RTRIM([ID_26700_LANG_1].Field4), 
RTRIM( '' + CASE WHEN ( [ID_26900_LANG_1].Field3 > 0) THEN 'Vehicle:' + RTRIM( [ID_26900_LANG_1].Field3) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field8 > 0) THEN 'VehicleType:' + RTRIM( [ID_26900_LANG_1].Field8) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field9 > 0) THEN 'Region:' + RTRIM( [ID_26900_LANG_1].Field9) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field10 > 0) THEN 'SubModel:' + RTRIM( [ID_26900_LANG_1].Field10) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field11 > 0) THEN 'EngineConfig:' + RTRIM( [ID_26900_LANG_1].Field11) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field12 > 0) THEN 'EngineDesignation:' + RTRIM( [ID_26900_LANG_1].Field12) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field13 > 0) THEN 'EngineVIN:' + RTRIM( [ID_26900_LANG_1].Field13) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field14 > 0) THEN 'ValvesPerEngine:' + RTRIM( [ID_26900_LANG_1].Field14) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field15 > 0) THEN 'EngineBase:' + RTRIM( [ID_26900_LANG_1].Field15) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field16 > 0) THEN 'FuelDeliveryConfig:' + RTRIM( [ID_26900_LANG_1].Field16) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field17 > 0) THEN 'FuelDeliveryType:' + RTRIM( [ID_26900_LANG_1].Field17) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field18 > 0) THEN 'FuelDeliverySubType:' + RTRIM( [ID_26900_LANG_1].Field18) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field19 > 0) THEN 'FuelSystemControlType:' + RTRIM( [ID_26900_LANG_1].Field19) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field20 > 0) THEN 'FuelSystemDesign:' + RTRIM( [ID_26900_LANG_1].Field20) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field21 > 0) THEN 'Aspiration:' + RTRIM( [ID_26900_LANG_1].Field21) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field22 > 0) THEN 'CylinderHeadType:' + RTRIM( [ID_26900_LANG_1].Field22) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field23 > 0) THEN 'FuelType:' + RTRIM( [ID_26900_LANG_1].Field23) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field24 > 0) THEN 'IgnitionSystemType:' + RTRIM( [ID_26900_LANG_1].Field24) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field25 > 0) THEN 'EngineMfr:' + RTRIM( [ID_26900_LANG_1].Field25) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field26 > 0) THEN 'EngineVersion:' + RTRIM( [ID_26900_LANG_1].Field26) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field27 > 0) THEN 'MfrBodyCode:' + RTRIM( [ID_26900_LANG_1].Field27) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field28 > 0) THEN 'BedConfig:' + RTRIM( [ID_26900_LANG_1].Field28) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field29 > 0) THEN 'BedLength:' + RTRIM( [ID_26900_LANG_1].Field29) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field30 > 0) THEN 'BedType:' + RTRIM( [ID_26900_LANG_1].Field30) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field31 > 0) THEN 'BodyStyleConfig:' + RTRIM( [ID_26900_LANG_1].Field31) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field32 > 0) THEN 'BodyNumDoors:' + RTRIM( [ID_26900_LANG_1].Field32) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field33 > 0) THEN 'BodyType:' + RTRIM( [ID_26900_LANG_1].Field33) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field34 > 0) THEN 'SpringTypeConfig:' + RTRIM( [ID_26900_LANG_1].Field34) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field35 > 0) THEN 'FrontSpringType:' + RTRIM( [ID_26900_LANG_1].Field35) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field36 > 0) THEN 'RearSpringType:' + RTRIM( [ID_26900_LANG_1].Field36) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field37 > 0) THEN 'SteeringConfig:' + RTRIM( [ID_26900_LANG_1].Field37) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field38 > 0) THEN 'SteeringType:' + RTRIM( [ID_26900_LANG_1].Field38) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field39 > 0) THEN 'SteeringSystem:' + RTRIM( [ID_26900_LANG_1].Field39) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field40 > 0) THEN 'BrakeConfig:' + RTRIM( [ID_26900_LANG_1].Field40) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field41 > 0) THEN 'FrontBrakeType:' + RTRIM( [ID_26900_LANG_1].Field41) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field42 > 0) THEN 'RearBrakeType:' + RTRIM( [ID_26900_LANG_1].Field42) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field43 > 0) THEN 'TransElecControlled:' + RTRIM( [ID_26900_LANG_1].Field43) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field44 > 0) THEN 'BrakeSystem:' + RTRIM( [ID_26900_LANG_1].Field44) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field45 > 0) THEN 'BrakeABS:' + RTRIM( [ID_26900_LANG_1].Field45) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field46 > 0) THEN 'Transmission:' + RTRIM( [ID_26900_LANG_1].Field46) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field47 > 0) THEN 'TransmissionBase:' + RTRIM( [ID_26900_LANG_1].Field47) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field48 > 0) THEN 'TransmissionType:' + RTRIM( [ID_26900_LANG_1].Field48) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field49 > 0) THEN 'TransmissionNumSpeeds:' + RTRIM( [ID_26900_LANG_1].Field49) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field50 > 0) THEN 'TransmissionControlType:' + RTRIM( [ID_26900_LANG_1].Field50) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field51 > 0) THEN 'TransmissionMfrCode:' + RTRIM( [ID_26900_LANG_1].Field51) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field52 > 0) THEN 'TransmissionMfr:' + RTRIM( [ID_26900_LANG_1].Field52) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field53 > 0) THEN 'TransmissionElecControlled:' + RTRIM( [ID_26900_LANG_1].Field53) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field54 > 0) THEN 'ElecControlled:' + RTRIM( [ID_26900_LANG_1].Field54) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field55 > 0) THEN 'TransferCase:' + RTRIM( [ID_26900_LANG_1].Field55) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field56 > 0) THEN 'TransferCaseBase:' + RTRIM( [ID_26900_LANG_1].Field56) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field57 > 0) THEN 'TransferCaseMfr:' + RTRIM( [ID_26900_LANG_1].Field57) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field58 > 0) THEN 'DriveType:' + RTRIM( [ID_26900_LANG_1].Field58) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field59 > 0) THEN 'WheelBase:' + RTRIM( [ID_26900_LANG_1].Field59) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field60 > 0) THEN 'RestraintType:' + RTRIM( [ID_26900_LANG_1].Field60) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field63 > 0) THEN 'DormanBodyCode:' + RTRIM( [ID_26900_LANG_1].Field63) + ':' ELSE '' END
+ CASE WHEN ( [ID_26900_LANG_1].Field64 > 0) THEN 'PowerOutput:' + RTRIM( [ID_26900_LANG_1].Field64) + ':' ELSE '' END
), 
RTRIM([ID_26900_LANG_1].OBJECT_ID), [ID_26700_LANG_1].SEQUENCE
 FROM VCDB_Vehicle 
 INNER JOIN VCDB_BaseVehicle ON VCDB_Vehicle.BaseVehicleId = VCDB_BaseVehicle.BaseVehicleId 
 INNER JOIN VCDB_Make ON VCDB_BaseVehicle.MakeId = VCDB_Make.MakeId 
 INNER JOIN VCDB_Model ON VCDB_BaseVehicle.ModelId = VCDB_Model.ModelId 
 INNER JOIN VCDB_Year ON VCDB_BaseVehicle.YearId = VCDB_Year.YearId 
 INNER JOIN VCDB_VehicleToDormanManufacturer ON VCDB_Vehicle.VehicleId = VCDB_VehicleToDormanManufacturer.VehicleId 
 INNER JOIN VCDB_DormanManufacturer ON VCDB_VehicleToDormanManufacturer.DormanManufacturerID = VCDB_DormanManufacturer.DormanManufacturerID 

 INNER JOIN [ID_26900_LANG_1]
 ON ( [ID_26900_LANG_1].Field5 = VCDB_BaseVehicle.YearId )
 AND ( [ID_26900_LANG_1].Field6 = VCDB_BaseVehicle.MakeId )
 AND ( [ID_26900_LANG_1].Field7 = VCDB_BaseVehicle.ModelId )
 AND ( [ID_26900_LANG_1].Field62 = VCDB_VehicleToDormanManufacturer.DormanManufacturerID )
 LEFT JOIN [ID_26600_LANG_1] ON [ID_26600_LANG_1].FIELD1 = [ID_26900_LANG_1].Field1 AND [ID_26600_LANG_1].OBJECT_ID = [ID_26900_LANG_1].OBJECT_ID
 LEFT JOIN [ID_26700_LANG_1] ON [ID_26700_LANG_1].Field1 = [ID_26900_LANG_1].Field1 AND [ID_26700_LANG_1].OBJECT_ID = [ID_26900_LANG_1].OBJECT_ID
 WHERE [ID_26900_LANG_1].OBJECT_ID IN (  SELECT PT.OBJECT_ID FROM [USERATTRIB] PT  INNER JOIN OBJECT ON PT.OBJECT_ID = OBJECT.OBJECT_ID Where  OBJECT_OBJCLASS_ID = 4096 AND   PT.[ID_41100_LANG_1] IN (40368) )
 ORDER BY 
VCDB_Year.YearId DESC , 
RTRIM(VCDB_Make.MakeName), 
RTRIM(VCDB_Model.ModelName), 
RTRIM(VCDB_DormanManufacturer.DormanManufacturerName), [ID_26700_LANG_1].SEQUENCE OPTION ( MAXDOP 1 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 21:25:18

why do you need distinct here? see if you can avoid duplicates while joining itself so that distinct can be removed. if you need help on that post some data from tables so that we can suggest an alternative

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rkruis
Starting Member

28 Posts

Posted - 2012-05-09 : 21:53:26

Here is an example of the data.
And we need it distinct on all rows, which make it a unique record.


2010	2010	45	Buick	6333	Enclave	23530	GENERAL MOTORS	1	Front Right	500115	99			546469	1
2010	2010	45	Buick	6333	Enclave	23530	GENERAL MOTORS	1	Front Right	500115	99			546469	1
2010	2010	45	Buick	6333	Enclave	23530	GENERAL MOTORS	1	Front Right	500115	99			546469	1
2010	2010	45	Buick	6333	Enclave	23530	GENERAL MOTORS	1	Front Right	500115	99			546469	1

I just ran another test with the distinct and without and these were the results.
47,066 records - 47 seconds (With Distinct)
291,541 records - 38 seconds (Without Distinct)
291,541 records - 21 seconds (Without Distinct and Without Order By)

Does the order by happen after the records have been filtered (Distinct)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 22:38:10

what i was asking was sample data from tables to see how they're related rather than result itself. It might be because there are some table related in 1 to many way. so unless you show data from tables we cant suggest how to do join to avoid the duplicates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources