Performance problem comparing dates between SQLSERVER 7 and 2000

Question

Post reply

Performance problem comparing dates between SQLSERVER 7 and 2000

Hernan Sarazola

SSC Enthusiast

Points: 111
More actions
May 5, 2006 at 2:46 pm

#112066

Hi:
I have the following query:
select col11,col12,col13=' ',col14 into dbo.table1
from remoteserver.database.dbo.table2 where col15>=(SELECT col21 FROM table1 WHERE col22 = 101)
Local server: 2000
Remote server: 7
Col15 and col21 are datetime
It takes 15 minutes to finish.
If I change to col15>='2006-05-05 00:00:00.000' (the output from SELECT col21 FROM table1 WHERE col22 = 101) it takes 10 seconds.
The collate_name from col21 is null, but from what I know, collation doesn't apply to datetime columns.
What can be happening?
Thanks in advance.
Regards.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How long does it take to run the SELECT col21 FROM table1 WHERE col22 = 101 statement? Is col22 indexed?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Hernan Sarazola SSC Enthusiast Points: 111 More actions · Answer 2

Hernan Sarazola

SSC Enthusiast

Points: 111

May 5, 2006 at 2:56 pm

#636369

Nothing. It has 5 rows.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Have you tried rewriting the select with a join?

select col11,col12,col13=' ',col14

into dbo.table1

from remoteserver.database.dbo.table2

INNER JOIN table1

ON table2.col15 >= table1.col21

WHERE table1.col22 = 101

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Hernan Sarazola SSC Enthusiast Points: 111 More actions · Answer 4

Yes. Same bad performance.

Same if using a variable (datetime), storing the value and comparing the remote date against it. Only working when value is "hardcoded".

Antares686 SSC Guru Points: 125444 More actions · Answer 5

How many rows are in remoteserver.database.dbo.table2 (the sql 7 server)?

Antares686 SSC Guru Points: 125444 More actions · Answer 6

BTW do you have Distributed Transaction Coordinator runnin or not?

Hernan Sarazola SSC Enthusiast Points: 111 More actions · Answer 7

Hernan Sarazola

SSC Enthusiast

Points: 111

May 5, 2006 at 3:31 pm

#636380

Yes. Both servers autostart DTC.

Antares686 SSC Guru Points: 125444 More actions · Answer 8

You missed my other question:

How many rows are in remoteserver.database.dbo.table2 (the sql 7 server)?

Hernan Sarazola SSC Enthusiast Points: 111 More actions · Answer 9

Sorry Antares, didn't see your previous question: 750.000 rows. Twelve satisfying the where clause.

Antares686 SSC Guru Points: 125444 More actions · Answer 10

What I believe happens in a non-static call such as this is that all 750,000 records are pulled across then filtered so you loose the advantage of all the indexes you might be using. You should see this in the execution plan if I remember correctly. However I am trying to find the details on this because I haven't done for 3 years now and forget what options you have available.