Paul White NZ (4/14/2010)

---

I still can't get my head around a suitable block size for your earlier method (one that is guaranteed to work all the time) but it is...

Paul White NZ (4/14/2010)

---

Hey I never claimed my idea was better than yours 😛

I said the question had been well answered - I'm just exploring an alternative for large inputs....

Interesting concept.

Unfortunately If I am not missing something, it will not really work correctly.

Consider the following consecutive rows in the IpToCountryMap table:

from_ip to_ip ...

Paul, I find your knowledge of the internal workings of SQL Server very impressive.

How do you know all this internal stuff ?

What I mean is try rewriting the from clause of the query like this:

from #FL as a inner HASH join Obbeaver.State_FL.dbo.HomeLook_USA as b on a.Clmn10 = b.Clmn10

left join Obbeaver.State_FL.dbo.VwOffLook_USA...

Tara-1044200 (4/13/2010)

---

remote server is a also sql 2005 but why is the other fast query which also has remote query is doing faster with 100 times bigger data ? If...

Tara-1044200 (4/13/2010)

---

Thanks stefan that gives more information for me. But i was wondering why would execution plan chose different route for the same query, LOOP join in the slower one...

You are reading 32 million rows from HomeLook_USA with a remote query. The optimizer estimates this remote query to return only 570 rows.

This extreme difference in estimated rows and actual...

I dont know why your numbers are so strange. Perhaps your system was heavily loaded ?

I ran the same test on my unloaded 2-CPU laptop and I got the following...

Jeff Moden (4/12/2010)

---

On the subject of a non-clustered index in a similar order at work... it caused very frequent timouts but, admittedly, that was on a table with a fair...

Jeff Moden (4/12/2010)

---

It'll make for a fine nonclustered index. Using it as a clustered index can cause a lot...

Jeff Moden (4/12/2010)

---

Ohhhh... be careful now. Unless that index is applied after all data is present and no new...

It sure sounds like you could benefit from partitioning.

It sounds like you have a very large table with data that covers a long time period and the table only gets...

I actually found such a formula myself by modifying code found at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471

Here is the code:

DECLARE @IntDate INT

SELECT @IntDate = 20090117

dateadd(month,((@IntDate/10000-1900)*12)+((@IntDate/100)%100)-1,(@IntDate%100)-1)

But, the performance of this expression is actually...

I would say option 1) There is no performance difference between the index used for the primary key and any other non-clustered index.

Instead of placing different objects in different filegroups,...