Please note that it is not enough just to run the posted code.

You must also write your own code to split up the ipCountryLookup in 64K-sized blocks.

I just wanted to...

Please post...

If the performance is still not good enough you could preprocess the IpCountryLookup to split all blocks that are larger than 64K into several smaller blocks.

You could then rewrite the...

Some random tips:

1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]

I assume that when you run this update most of the rows in the table already have a CountryCode....

In what way do you want to improve it ?

Does it not work correctly ?

Performance ?

Disk space ?

Just asking us to...

I agree completely with Wayne.

/SG

Sure, 8 million rows should be no problem. Queries against such a table with an appropriate index will still be blindingly fast.

/SG

You should define a clustered index on GeoID, ProductID, AsOf just as you suggested yourself.

You should also change the query so the SQL server understands that you are really asking...

The only way to know how much compression you would actually get is to actually try it on your data.

Install SQL 2008 developer somewhere

Import a sample of your largest tables...

I would use a precomputed table of distances between all zipcodes and all stores.

Since you only have a few hundred stores this would give a much smaller table than the...

I am pretty sure that your problem is caused by allowing NULL in ReferenceNumber.

Try changing the table...

You probably have lots of overlapping queries. If your application generates 20 simultaneous queries that all start at 00:00:00 and then all finish at 00:00:02 the elapsed time would be...

Hmm. I see your point.

The problem is that if I do this with CREATE INDEX I will probably have to create new filegroups and new partitioning schemes using the new...

Paul, thank you for the idea of using EMPTYFILE to move all the data to a new file.

My current plan is to use backup/restore to move the data to...

Kevin Bullen (3/25/2010)

---

Thanks all for the input. I ripped out the UDF and called the logic inline, mostly hacking from Stefan_G's sql example. That took the runtime from...