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 1 3704
Matik (ma****@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, es****@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andy S. |
last post by:
Hi,
I'm trying to declare and use temporary tables. I have written the
following code in Java. Creating the tablespace (i can see the
tablespace created using the Control Center), the temporary...
|
by: anilcool |
last post by:
Hi all,
This is probably a simple problem for most of you.. Let me know if you
have any pointers for me. I am new to DB2.
In my stored procedure I want to select records that match a range of...
|
by: peteh |
last post by:
Hi All;
We have many production jobs that "load from cursor" to a UDB/AIX 8.2
(with dpf) data warehouse from source tables residing Oracle 9i. Since
Oracle dates are (roughly) equivalent to DB2...
|
by: TGEAR |
last post by:
I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.
if exists (select * from dbo.sysobjects where id =
object_id(N'.') and...
|
by: satish mullapudi |
last post by:
Hi,
I am using DB v8.2. when I issuing the following stmt.:
"select DATE('25.12.2006')-DATE('24.12.2006')from sysibm.sysdummy1" :
1
"select DATE('25.12.2006')-DATE('24.11.2006')from...
|
by: jesmi |
last post by:
hi
i got problem in inserting the date into the database. my requirement is that when i choose a date ie from :2007-01-01 & to :2007-12-01 then all the dates starting from 2007-02-01 upto...
|
by: jeanbdenis |
last post by:
Hi Folks,
I have been struggling with this issues for the last couple of days. I have a java application which does an update to the database every 5 mins. The data written to the database...
|
by: pradeep84 |
last post by:
Hi .. friends.. in the below program
public void actionPerformed(ActionEvent ae)
{
int flag=0;
s1=(from.getText());
s2=(to.getText());
if(ae.getSource()==view)
{
|
by: Yew12 |
last post by:
We are trying to get the following script to display the full date and time. The field we are calling does have both date and time in. Unfortunatly its only returning the date.
So I tried putting...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |