GETDATE() Function Issue

Hi Everyone,
I'm running into an issue trying to update a column on a remote database.
The server is linked from the server I'm calling this update statement. The
syntax is as follows:
UPDATE Server1.Test.dbo.Testdata
SET dtUpdated = GETDATE()
WHERE intID = 111
The problem is that the update takes a very long time to complete. Now if I
declare a variable with a datetime and set that variable to GETDATE(), the
update takes seconds. Is there some known issue? Can anyone help with this
?
I have noticed that I can update small size tables, but it takes much longer
to update large size tables. The columns in the "Where" clause have indexes
on them and that can't be the issue since my test using the variable worked
quicker. I'm just wondering why I can't use the GETDATE() function directly
.
DarrenDarren
No, it works fine with GETDATE() function too. Please take a look at
execution plan of the query (with GETDATE() and without) and see what is
going on
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
> Hi Everyone,
> I'm running into an issue trying to update a column on a remote database.
> The server is linked from the server I'm calling this update statement.
> The
> syntax is as follows:
> UPDATE Server1.Test.dbo.Testdata
> SET dtUpdated = GETDATE()
> WHERE intID = 111
> The problem is that the update takes a very long time to complete. Now if
> I
> declare a variable with a datetime and set that variable to GETDATE(), the
> update takes seconds. Is there some known issue? Can anyone help with
> this?
> I have noticed that I can update small size tables, but it takes much
> longer
> to update large size tables. The columns in the "Where" clause have
> indexes
> on them and that can't be the issue since my test using the variable
> worked
> quicker. I'm just wondering why I can't use the GETDATE() function
> directly.
> Darren|||Run it again and see if there is any issues
Madhivanan|||I looked at the execution plan and it is doing what I thought it would. The
execution plan shows that it does a remote scan to look at the whole table
then it will filter by the where clause. This is the source of my problem.
For a large table that holds over 4 million records, it has to scan that
whole table before filtering on the primary key I'm already passing it. I
don't understand why on the remote server it must scan the whole table
instead of already filtering by the where clause. When I use a variable to
update the date column it shows a remote query for the update. Why is this
happening? Did some setting happen that I don't know about? How can I trac
k
down this issue?
"Uri Dimant" wrote:

> Darren
> No, it works fine with GETDATE() function too. Please take a look at
> execution plan of the query (with GETDATE() and without) and see what is
> going on
>
>
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
>
>