serialized binary files vs Sql Server Performance..

[Guest]

I'm considering deploying an application that will us an IP address locaiton
database provided by Ip2location.com...

http://www.ip2location.net/ip2location-dotnet-component.aspx

their .net component reads data from a binary file. I'm guess i'm wondering
which of the two options would be best.

1) read data from the binary files (~27 mb or so)
2) or query a sql DB with the same info.

The database will have about 8 columns and ~ 1.8 millions rows.

i'm developing for a high traffic webisite, and i would like to query this
DB to get the City info from the DB when a visitor first visits the site
(based on their ip address).

Also, how do serialized binary files handle simultaneous file access? Would
a high traffic webisite be able to use a serialized file efficiently if the
file was read often?

Thanks!

 

[john smith]

I'm considering deploying an application that will us an IP address locaiton
database provided by Ip2location.com...

http://www.ip2location.net/ip2location-dotnet-component.aspx

their .net component reads data from a binary file. I'm guess i'm wondering
which of the two options would be best.

1) read data from the binary files (~27 mb or so)
2) or query a sql DB with the same info.

The database will have about 8 columns and ~ 1.8 millions rows.

i'm developing for a high traffic webisite, and i would like to query this
DB to get the City info from the DB when a visitor first visits the site
(based on their ip address).

Also, how do serialized binary files handle simultaneous file access? Would
a high traffic webisite be able to use a serialized file efficiently if the
file was read often?

Thanks!

I don't see reading from a file as a big problem (open read-only), but
that's still a fair amount of data to parse (27MB). A database can
handle that much data easily (they can handle many GBs of data no
problem). Perhaps the best thing to do would be to benchmark both (like
say, query it 1000 times each ways and compare how much time it took)
and to do some load testing (not just 1 request after each other but
many simultaneous queries). My guess is that the results (which one
works best) will vary depending on how much traffic you have, the load
on the server as well as the load on the database server. It's hard to
predict accurately what would work best.

If you didn't need the city I'd recommend you have a look at this
article http://www.eggheadcafe.com/articles/20051109.asp which manages
to do it all in RAM (no file access or DB queries).

 

[Guest]

Actually,
you can get all this information for free without a database
by using the MSN Virtual Earth script generated by this URL:

http://local.live.com/WiFiIPService/locate.ashx

What this does is return a function, SetAutoLocateViewport, that you can use
with the MapControl.js VE Maps control. which can also be used to lookup the
city, state information.

SetAutoLocateViewport(28.5418, -81.3736, 10, false, 'Virtual Earth has
determined your location by using your computer\'s IP address.');

Note that the first two parameters represent the latitude and longitude of
the IPAddress represented by your REMOTE_HOST server variables.

Here's an article I did on this with more info (and a much smaller database,
in binary form as an embedded resource):

http://www.eggheadcafe.com/articles/20051109.asp

I am not familiar with how efficient their binary file lookup is, probably
the best way to find out is to ask the vendor. I suspect since they expect
high-volume usage that they've figured out how to optimize it.

Peter

 

[Guest]

Thanks for the useful information. I was not aware of the localive service.
However, i've notice that i'm getting a consistent "Server currently busy,
Try again Later" message. Is there a commercial web service that will be
more reliable? Will this web service remain free?

thanks!

 

[sloan]

A properly setup database will perform better, I believe.

But testing is the only way to check for sure.

...

You need to properly index the table, esp with that many rows.

The addage of "if the column is in the where clause, then index it" may
not be sufficient here. You need to determine the exact query usage.

www.sqlservercentral.com would be a good resource for that kind of sql
server performance question.

 

[Guest]

i found info on the live local service. Looks like it is still in beta
format.

http://download.microsoft.com/downl...6df-ad81-0bc85745356e/WindowsLiveLocalFAQ.doc

thanks for the info. i think i will try to get access to a sample database
and use your methods to make my own binary files. their component only works
with dot net 1.x and i want to use a 2.0 app.

thanks!

 

[PJ]

If it's only 27mb, why not just read the whole thing and put it in memory?

 

[Guest]

As mentioned before, i'm really new to this type of situation. So, i was
just trying to get some input regaring the performance of using a binary
file, vs a SQL server.

 

[PJ]

either way it's a wash if you just cache the information and code something
to update the cache every....what....day? how often does city state
information change?

 

[Guest]

I see you point. my concern is that i'm going to query this data for every
visitor to the website to determine what content to show them. So, this info
will be queried when a user first visits the site to obtain the city/state
info and store it in a cookie or something. So, i was not sure how accessing
1.8 million rows of data would impact the performace if the site had high
traffic.