John Mitchell-245523 (6/25/2013)
Lowell (6/25/2013)
also, i see a scalar function in the selection, so that will substantially slow things down...it looks like it's just formatting the data as 6 chars, maybe 201306?lose the function and do an inline conversion instead for something that simple would help.
Lowell, if the query only returns about 100 rows, is that likely to have a large impact? I thought that maybe the YEAR function in the final join predicate might be the culprit, especially if skus_min_drel is a large table, but without the execution plan, we're just guessing.
John
probably not a big impact, i agree John;
I think one of the join criterias are using scalar functions though...i think that is the performance killer here:
isn't this three different scalar functions being used to create the joins??
[dbo].[tipo_id_std]()
[dbo].[get_release_id]()
[dbo].[skus_min_drel]()
INNER JOIN [prod_starts] [PRODSTARTS]
--
--
--
WITH (INDEX (MI_prod_starts_linea_id_tipo_id_cod_rel_id_fitgrp_id_year))
--
--
--
ON (
[PRODSTARTS].[linea_id] = [models].[linea_id]
AND [PRODSTARTS].[tipo_art_id] = [dbo].[tipo_id_std]([models].[tipo_id])
AND [PRODSTARTS].[cod_rel_id] = [dbo].[get_release_id]([skus].[id])
AND [PRODSTARTS].[fitgrp_id] = [fitcode].[fitgrp_id]
AND [PRODSTARTS].[year] = YEAR([dbo].[skus_min_drel]([skus].[id]))
)
Lowell