SQL Running Slow In Browser

Transact-SQL
1

I am trying to find vehicles in my database that match the criteria the customer is searching for. There are several tables being joined on my main vehicles table (tbl_Vehicles). The query runs fast in SQL server but slow on my site. I have read up on indexes but am not very familiar with them. I have primary keys sets on all of the "ID" fields which are unique. Any suggestions for speeding up my query?

SELECT
	vm.vm_name, v.v_id, v.v_model, v.v_stock_number, v.v_year, v.v_mileage, v.v_mileage_hours, 
	v.v_search_price, v.v_price_obo, v.v_city, v.v_state, v.v_zip_code, v.v_contact_name, 
	v.v_daytime_phone, v.v_evening_phone, vco.vco_name, vd.vd_name, vdt.vdt_name, vbs.vbs_name, 
	vt.vt_name, v.v_processed_date, v.v_last_activity, d.d_id, d.d_name, d.d_city, d.d_state, 
	d.d_logo, d.d_website, v.v_featured, v.vc_id 
FROM tbl_Vehicles v 
	FULL JOIN tbl_VehicleMake vm ON vm.vm_id = v.vm_id 
	FULL JOIN tbl_VehicleDriveTrain vdt ON vdt.vdt_id = v.vdt_id 
	FULL JOIN tbl_VehicleDoors vd ON vd.vd_id = v.vd_id 
	FULL JOIN tbl_VehicleColors vco ON vco.vco_id = v.vco_id_exterior 
	FULL JOIN tbl_VehicleBodyStyle vbs ON vbs.vbs_id = v.vbs_id 
	FULL JOIN tbl_VehicleTransmission vt ON vt.vt_id = v.vt_id 
	FULL JOIN tbl_Dealers d ON d.d_id = v.d_id 
WHERE v.v_processed = 1 AND v.v_active = 1 AND v_bid_vehicle = 0 
	AND (@vc_id IS NULL OR v.vc_id = @vc_id) 
	AND (@vm_id IS NULL OR v.vm_id = @vm_id) 
	AND (@v_model IS NULL OR v.v_model LIKE @v_model) 
	AND (@vcn_id IS NULL OR v.vcn_id = @vcn_id) 
	AND (@vco_id_exterior IS NULL OR v.vco_id_exterior = @vco_id_exterior) 
	AND (@vco_id_interior IS NULL OR v.vco_id_interior = @vco_id_interior) 
	AND (@vt_id IS NULL OR v.vt_id = @vt_id) 
	AND (@vdt_id IS NULL OR v.vdt_id = @vdt_id) 
	AND (@vd_id IS NULL OR v.vd_id = @vd_id) 
	AND (v.v_search_price BETWEEN (CASE WHEN @v_start_price IS NOT NULL THEN @v_start_price ELSE 0 END) AND (CASE WHEN @v_end_price IS NOT NULL THEN @v_end_price ELSE 999999 END)) 
	AND (v.v_year BETWEEN (CASE WHEN @v_start_year IS NOT NULL THEN @v_start_year ELSE 0 END) AND (CASE WHEN @v_end_year IS NOT NULL THEN @v_end_year ELSE 9999 END)) 
	AND (v.v_mileage BETWEEN (CASE WHEN @v_start_mileage IS NOT NULL THEN @v_start_mileage ELSE 0 END) AND (CASE WHEN @v_end_mileage IS NOT NULL THEN @v_end_mileage ELSE 999999 END))

Also, after the query is run, I am doing some data manipulation on the values being returned, for instance:

v_features = ""
If Not CheckBlank(vco_name) Then v_features = v_features & vco_name & ", "  '  Vehicle Exterior Color
If Not CheckBlank(vd_name) Then v_features = v_features & vd_name & " Door, " ' Vehicle Doors
If Not CheckBlank(vdt_name) Then v_features = v_features & vdt_name & ", "  '  Vehicle Drive Train
If Not CheckBlank(vbs_name) Then v_features = v_features & vbs_name & ", "  '  Vehicle Body Style
If Not CheckBlank(vt_name) Then v_features = v_features & vt_name & ", "  '  Vehicle Transmission
If Not CheckBlank(v_stock_number) Then v_features = v_features & "Stock #" & v_stock_number & ", "  '  Vehicle Stock Number
If Right(Trim(v_features), 1) = "," Then v_features = Left(Trim(v_features), Len(Trim(v_features)) - 1)

And also additional data formatting:

v_description = ""
If Not CheckBlank(v_year) AND v_year <> 0 Then v_description = v_description & v_year  ' Vehicle Year
If Not CheckBlank(vm_name) Then v_description = v_description & " " & vm_name  ' Vehicle Make/Name
If Not CheckBlank(v_model) Then v_description = v_description & " " & v_model  ' Vehicle Model
If Not CheckBlank(v_description) Then
	If Len(v_description) > 35 Then
		v_description = Trim(Left(v_description, 35)) & "..."
	End If
End If

Can this all be done directly in the SQL query to help speed up the process? Any help is much appreciated!

2

Is the query in a stored procedure? If so, likely reason for it being slow is "parameter sniffing". SQL Server caches the query plan when you run it the first time (or when there is no query plan in cache). That query plan is optimized for the set of parameters that were used to run that specific instance, but might be a very poor choice for a different set of parameters.

There are couple of options that you could try - one is to use WITH RECOMPILE at the stored procedure level or query hint RECOMPILE at the statement level. The problem with that, of course, is that the stored proc/query will be recompiled each time the stored procedure is run.

Another option is to use dynamic SQL. See a discussion and example here. Although in general, dynamic SQL is something that one should avoid, this is one of those cases where dynamic SQL does work, and work well and work safely.

If you want to read about parameter sniffing in some detail, see here.

3

Do you need a FULL OUTER JOIN on all the tables? That is probably expensive.

	AND (@Param1 IS NULL OR v.Col1 = @Param1) 
	AND (@Param2 IS NULL OR v.Col2 = @Param2)

is never (I'm 99% sure :smile: ) going to use an index if there are lots of parameters/columns involved. Only way that I know of to improve that is to use Dynamic SQL which only includes code/tests for parameters that are NOT NULL.

If you combine that with sp_ExecuteSQL and use a parametrised query then the query plan will be cached so will be effective on subsequent queries [i.e. that use the same parameters, but with different values] too (notwithstanding parameter sniffing issues)

With all those parameters the time to parse the query may actually be longer than the execution time ...

I reckon you also need to improve this (although moving to Dynamic SQL and only acting on non-null parameters would make that fix easier)

AND (v.v_search_price BETWEEN 
    (CASE WHEN @v_start_price IS NOT NULL THEN @v_start_price ELSE 0 END) 
AND (CASE WHEN @v_end_price IS NOT NULL THEN @v_end_price ELSE 999999 END))

I think this would work, and be easier for SQL to parse, but I still think the WHERE clause is too complex for SQL to do anything useful with it (performance-wise)

AND (@v_start_price IS NULL 
 OR v.v_search_price 
BETWEEN COALESCE(@v_start_price, 0)
    AND COALESCE(@v_end_price, 999999)
)

An index on the ID column in the related table may help e.g.

FROM tbl_Vehicles v 
	JOIN tbl_VehicleMake vm ON vm.vm_id = v.vm_id

then an index on vm.vm_id (which you already have, as the PKey on tbl_VehicleMake) but also an index on v.vm_id in tbl_Vehicles

Other than that indexes on the columns that included in the WHERE clause are useful, but they need to be multi-part key indexes covering all the fields actually used in the WHERE clause. If you change to dynamic SQL for the WHERE clause you will probably find that users tend to generate queries that predominantly use a small subset of the available parameters, and you can improve the most commonly used ones with indexes

AND v.v_model LIKE @v_model

is only likely to use an index if the wildcard is at the Right end (or middle) NOT if it is at the Left, so either let the user type in the wildcard in the form field when/where they want one, or offer a "Starts with" / "Contains" option and then only add a leading "%" for a Contains test, or use some sort of Free Text index/query system.