|
|
Posted by Erland Sommarskog on 01/11/07 22:39
Matik (marzec@sauron.xo.pl) writes:
> - oaVIEW_MainData, is a view on major big table, holding lot of records
> joinden with small table containing configuration data, over left outer
> join; both tables are with nolock option,
NOLOCK in a view? That's about criminal in my opinion.
> - on major table, are indexes which one is on id field (not used in
> this query at all), which is a pk clustered, and other is on dateEvt
> (c_dateTime) which is a desc index with fill level 90%
> - table has also other indexes, on three different fields, one of
> theses is dsmIdent,
>
> Now, if I'm using max(id) works very fast, and ok for me, but the
> problem is, I should not use id, because might be, that the records
> will be written in the table with random order, so the only one saying
> which is newest, will be dateEvt.
>
> Using dateEvt as max(), dramaticly slows query, so I'm acctualy unable
> to get result. What is much more funny, server is totaly busy with this
> query, and it's procesor jumps on 100%.
Well, the easy fix would be to make the index on dateEvt() clustered
rather than the index on id. That may of course have repercussions
elsewhere.
The query looks funny to me, as it repeats the entire outer query in
the subquery. Somehome I feel that that should not be necessary. But
to say for sure I would need to know the view definition and the
definition of the underlying tables, including their key and check
constraints.
--
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
Navigation:
[Reply to this message]
|