getdate() causes locks in views

[Allan Martin]

Hello,

I have a problem with a view. The view reads as:

create view v_lds
as
select * from lds where dsdate > getdate() - 14

The idea being that the view only shows data which has modified within
the last 2 weeks. It works fine when viewing in through any tool.

However, when I have one application read from the view and a
different application write to the lds table directly, a dead lock
occurs.

If I modify the view to read:

create view v_lds
as
select * from lds where dsdate > '2003-08-15'

The problem disappears.

Any help would be much appreciated.

Thanks,

Allan Martin

[Erland Sommarskog]

[posted and mailed, please reply in news]

Allan Martin (al*********@ntlworld.com) writes:

I have a problem with a view. The view reads as:

create view v_lds
as
select * from lds where dsdate > getdate() - 14

The idea being that the view only shows data which has modified within
the last 2 weeks. It works fine when viewing in through any tool.

However, when I have one application read from the view and a
different application write to the lds table directly, a dead lock
occurs.

If I modify the view to read:

create view v_lds
as
select * from lds where dsdate > '2003-08-15'

The problem disappears.

Not much to work from, I'm afraid.

What is likely to make a difference is that when you say

select * from lds where dsdate > '2003-08-15'

The optimizer can determine from the statistics how many rows in
lds it will hit, and therefore decide whether to use a non-clustered
index on that column or not. (You don't give any details on indexing,
but from the behaviour I guess that there such an index.)

When you instead put in an expression "getdate() - 14", SQL Server
does no longer have a value for it when building the query plan,
so it prefers to scan the table from left to right instead. This is
because, if many rows passes the where clause it would be slower
to use the index.

You should probably look into your index strategy. Many the index
on lds.dsdate should be clustered. But for a quick fix, you might
save the situation with an index hint to force use of the index
on lds.dsdate:

SELECT * FROM lds WITH (INDEX = lds_dsdate_ix) WHERE dsdate ...

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[M A Srinivas]

Yo can also try creating a row in a table with single column indexed
with getdate()-14 and convert the query as a join

Srinivas

al*********@ntlworld.com (Allan Martin) wrote in message news:<a6**************************@posting.google. com>...

Hello,

I have a problem with a view. The view reads as:

create view v_lds
as
select * from lds where dsdate > getdate() - 14

The idea being that the view only shows data which has modified within
the last 2 weeks. It works fine when viewing in through any tool.

However, when I have one application read from the view and a
different application write to the lds table directly, a dead lock
occurs.

If I modify the view to read:

create view v_lds
as
select * from lds where dsdate > '2003-08-15'

The problem disappears.

Any help would be much appreciated.

Thanks,

Allan Martin