Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #795

Re: SQL Server 2008 R2 Express - optimize query

Show all headers | View raw

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

SQL Server 2008 R2 Express - optimize query Joachim082 <Joachim.Schreibmaier@hiway.at> - 2011-11-10 22:03 -0800
  Re: SQL Server 2008 R2 Express - optimize query Erland Sommarskog <esquel@sommarskog.se> - 2011-11-11 08:34 +0000

csiph-web