Query too slow! Need some performance enhancing tips!

[gizmo@consultant.com]

I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.

SELECT vehicle.vehicle _id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_it em_assn] on
vehicle.vehicle _id=[vehicle_subj_it em_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_it em_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field _id=@template_f ield_id) AND
([template_field].template_field _type_id=3) AND
([vehicle_subj_it em_assn].subj_item_valu e_text=@value) AND
(vehicle.end_dt m IS NOT NULL)

Thanks
Gavin

[Hugo Kornelis]

Re: Query too slow! Need some performance enhancing tips!

On 1 Dec 2004 06:17:40 -0800, gizmo@consultan t.com wrote:
[color=blue]
>I have a stored procedure that queries a database using a Select
>statement with some inner joins and conditions. With over 9 million
>records it takes 1 min 36 sec to complete. This is too slow for my
>requirements .
>
>Is there any way I can optimize this query. I have thought about
>using an indexed view. I haven't done one before, does anyone know if
>this would have potential to improve performance or indeed any other
>performance enhancing techniques I might try.
>
>SELECT vehicle.vehicle _id
>FROM (( [vehicle]
>INNER JOIN [vehicle_subj_it em_assn] on
>vehicle.vehicl e_id=[vehicle_subj_it em_assn].vehicle_id)
>INNER JOIN [subj_item] on
>[vehicle_subj_it em_assn].subj_item_id=[subj_item].subj_item_id)
>INNER JOIN [template_field] on
>[subj_item].subj_item_id=[template_field].subj_attr_id
>WHERE
>([template_field].template_field _id=@template_f ield_id) AND
>([template_field].template_field _type_id=3) AND
>([vehicle_subj_it em_assn].subj_item_valu e_text=@value) AND
>(vehicle.end_d tm IS NOT NULL)
>
>Thanks
>Gavin[/color]

Hi Gavin,

I don't think you need all those paretheses and brackets. They don't hurt
performance, but they do make the query harder to read.

Also, you never use any column in subj_item. I think you can remove that
table (unless it is ppossible that some value of subj_item_id that does
not exist in subj_item does exist in vehicle_subj_it em_assn.subj_it em_id
and template_field. subj_attr_id). I don't see any way to improve on the
remaining query:

SELECT vehicle.vehicle _id
FROM vehicle
INNER JOIN vehicle_subj_it em_assn
ON vehicle.vehicle _id = vehicle_subj_it em_assn.vehicle _id
INNER JOIN template_field
ON vehicle_subj_it em_assn.subj_it em_id=template_ field.subj_attr _id
WHERE template_field. template_field_ id = @template_field _id
AND template_field. template_field_ type_id = 3
AND vehicle_subj_it em_assn.subj_it em_value_text = @value
AND vehicle.end_dtm IS NOT NULL
(untested)

An other way to optimize this is to look at your indexes. I don't think an
indexed view will do you much good (though you can always try, of course -
remember to test a typical workload, as this specific information will
return faster, but update performance will suffer) - indexed views are
often used for views with aggregations.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Query too slow! Need some performance enhancing tips!

(gizmo@consulta nt.com) writes:[color=blue]
> I have a stored procedure that queries a database using a Select
> statement with some inner joins and conditions. With over 9 million
> records it takes 1 min 36 sec to complete. This is too slow for my
> requirements.
>
> Is there any way I can optimize this query. I have thought about
> using an indexed view. I haven't done one before, does anyone know if
> this would have potential to improve performance or indeed any other
> performance enhancing techniques I might try.[/color]

You could materialize the query into an indexed view, but I would suspect
that it is an overkill.

You can probably improve performance considerably by reviewing indexes.
96 seconds for 9 millions rows sounds like a table is being scanned
somewhere. However, I cannot do that for you, since you have not submitted
enough with information.

I would suggest that you post the CREATE TABLE and CREATE INDEX statments
for your tables, as well as the approxamite rowcount for the tables.

Also consider Hugo's note about the subj_item table. It may not fill a
function in the query. (Then again, it could serve as an EXISTS condition.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[tonyharkins@post.com]

Re: Query too slow! Need some performance enhancing tips!

Have you checked out http://www.xprime.com

We've been using their database accelerator for MS SQL with good
results.... 5X on many of our stored procs, similar to those you
describe below.

Tony

gizmo@consultan t.com wrote:[color=blue]
> I have a stored procedure that queries a database using a Select
> statement with some inner joins and conditions. With over 9 million
> records it takes 1 min 36 sec to complete. This is too slow for my
> requirements.
>
> Is there any way I can optimize this query. I have thought about
> using an indexed view. I haven't done one before, does anyone know[/color]
if[color=blue]
> this would have potential to improve performance or indeed any other
> performance enhancing techniques I might try.
>
> SELECT vehicle.vehicle _id
> FROM (( [vehicle]
> INNER JOIN [vehicle_subj_it em_assn] on
> vehicle.vehicle _id=[vehicle_subj_it em_assn].vehicle_id)
> INNER JOIN [subj_item] on
> [vehicle_subj_it em_assn].subj_item_id=[subj_item].subj_item_id)
> INNER JOIN [template_field] on
> [subj_item].subj_item_id=[template_field].subj_attr_id
> WHERE
> ([template_field].template_field _id=@template_f ield_id) AND
> ([template_field].template_field _type_id=3) AND
> ([vehicle_subj_it em_assn].subj_item_valu e_text=@value) AND
> (vehicle.end_dt m IS NOT NULL)
>
> Thanks
> Gavin[/color]