SQL Server 2008 R2 Express - optimize query

Hi,

I have a problem with one of my queries which is never finished within
the timout. I've tried to optimize my query with indizes but without
success.

Here is the query:
//
use md;
SELECT eval.eval_channel, eval.eval_math, eval.eval_value,
eval.eval_ts, eval.eval_id, eval.stueck_id, stueck.los_id, los.los_nr,
stueck.stueck_nr, eval.eval_name, eval.eval_type
FROM ds_eval eval
INNER JOIN ds_stueck stueck ON stueck.stueck_id=eval.stueck_id
INNER JOIN ds_los los ON los.los_id=stueck.los_id
INNER JOIN ds_aggregat aggregat ON aggregat.aggregat_id =
los.aggregat_id
INNER JOIN ds_betrieb betrieb ON betrieb.betrieb_id =
aggregat.betrieb_id
WHERE stueck.start BETWEEN 3403670984 AND 3403834990 AND betrieb.name
= 'NRW' AND eval.eval_name IN ('NRW_STB_M2-Drehz_MIN-MAX-MEDIAN')
ORDER BY los.los_nr, stueck.stueck_nr, eval.eval_name;
//
What else can I do to optimize. I think that the parts "WHERE
stueck.start BETWEEN 3403670984 AND 3403834990" and 'eval.eval_name IN
('NRW_STB_M2-Drehz_MIN-MAX-MEDIAN')' are causing the problem. The
execution plan says that the clustered index scan on the primary key
of the table ds_eval costs 89%.

Has anybody some hints?

Best Regards,

--
Joachim

Joachim082 (Joachim.Schreibmaier@hiway.at) writes:
> I have a problem with one of my queries which is never finished within
> the timout. I've tried to optimize my query with indizes but without
> success.
>
> Here is the query:
> //
> use md;
> SELECT eval.eval_channel, eval.eval_math, eval.eval_value,
> eval.eval_ts, eval.eval_id, eval.stueck_id, stueck.los_id, los.los_nr,
> stueck.stueck_nr, eval.eval_name, eval.eval_type
> FROM ds_eval eval
> INNER JOIN ds_stueck stueck ON stueck.stueck_id=eval.stueck_id
> INNER JOIN ds_los los ON los.los_id=stueck.los_id
> INNER JOIN ds_aggregat aggregat ON aggregat.aggregat_id =
> los.aggregat_id
> INNER JOIN ds_betrieb betrieb ON betrieb.betrieb_id =
> aggregat.betrieb_id
> WHERE stueck.start BETWEEN 3403670984 AND 3403834990 AND betrieb.name
>= 'NRW' AND eval.eval_name IN ('NRW_STB_M2-Drehz_MIN-MAX-MEDIAN')
> ORDER BY los.los_nr, stueck.stueck_nr, eval.eval_name;
> //
> What else can I do to optimize. I think that the parts "WHERE
> stueck.start BETWEEN 3403670984 AND 3403834990" and 'eval.eval_name IN
> ('NRW_STB_M2-Drehz_MIN-MAX-MEDIAN')' are causing the problem. The
> execution plan says that the clustered index scan on the primary key
> of the table ds_eval costs 89%.
>
> Has anybody some hints?

Generally, it is not possible to optimize queries without knowledge of
table definitions, data sizes and distribution. Furthermore, while I could
suggest indexes for this query, I assume that these table are involved in
other queries as well - and for which other indexes may be better.

So, please post the CREATE TABLE and CREATE INDEX statements for these
tables. Please also indicate the number of rows in each table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx