Posted by Matik on 01/10/07 10:37

Hello Everybody,

I have a problem, with select stmt:

SELECT TOP 15 *
FROM oaVIEW_MainData AS TOP_VIEW,
oaLanguageData_TAB AS RwQualifierJoin with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin.text_id = c_cfgRegPoint
AND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)
FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB AS
RwQualifierJoin1 with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin1.text_id = c_cfgRegPoint
AND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)
order by c_dateTime desc


Please consider:
- top doesn't metter, if I will use one or 10000 result is always the
same.
- 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,
- quersy supose to return last record from major table/view, in given
time, additionaly, with other where conditions (like in this case with
text),
- 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%.

Now, because the query is builded dynamicly, by a user selections,
that's why we decided on such a parser ... problem is, it is not
working :(

Can I change index on dateEvt somehow, to sped this up?
Maybe construct query somehow different, to get this over max() date?

Please help

Matik

[Back to original message]