http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660AG, I agree it's only 2 mill rows, Microsoft Sql will do such a search in milliseconds, no doubt about that. Max Mind is terrible wrong, but that is not up to discuss that :), but maybe they were talking about Excel *LOL*http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment10http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment10Thu, 21 Jun 2012 21:22:51 GMTTobi, I wrote a bunch of posts about materialized views a few years back, they are great, but in RDMBS they have severe limitations, and for the same reason you can't really do full denormalization during read.http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment9http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment9Thu, 21 Jun 2012 14:42:39 GMTAyende, that might be a reason. I just know that materialized views in SQL Server are a joy to use. I write normalized, I read with full performance. Joins become no-ops at runtime. Often it is possible to save on sorting and filtering as well. Complex queries become single range scans on some index.http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment8http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment8Thu, 21 Jun 2012 14:32:34 GMTSo equivalent sql query run on my machine (nothing special really...) and ms sql (with the database imported but no optimisation at all) takes something between 60 and 170 ms. Tried for a few IPs. That is waaay less than several seconds, and has plenty of places it can be improved (with indexes).http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment7http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment7Thu, 21 Jun 2012 14:17:15 GMTTobi, Very simple reason, it is HARD to track denormalization on reads. http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment6http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment6Thu, 21 Jun 2012 13:57:48 GMTMaterialized views are the genius of RavenDB. Not sure why RavenDB tries to proliferate denormalization on the writing side. I think we should write normalized and read denormalized. That's the best of both worlds. I think that denormalization on the writing side is generally completely misguided.http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment5http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment5Thu, 21 Jun 2012 13:55:34 GMTIndex lookup in a relational database? It takes no more than few milliseconds. I don't have to do any coding to know that... Probably MaxMind guys were talking about database without any indexing that you get by importing the csv file in dumbest possible way. Only then it would take few seconds to do a table scan...http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment4http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment4Thu, 21 Jun 2012 12:17:58 GMT@Ayende of course he did, what I meant is that somebody should verify that. Perhaps he is doing something wrong there. By the size of the data, it doesn't seem to me that it should take that long. Anyway that's a thing to try.http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment3http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment3Thu, 21 Jun 2012 10:43:13 GMTAG, I assume that MaxMind did.http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment2http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment2Thu, 21 Jun 2012 10:37:52 GMTHas anybody tried to load it into relational database, add some indexes, and perform such query (apart from MaxMind, just to verify)? I somehow find it hard to belive that it takes several seconds...http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment1http://ayende.com/156418/geo-location-spatial-searches-with-ravendb-part-iv-searching#comment1Thu, 21 Jun 2012 10:34:00 GMT