serialized binary files vs Sql Server Performance..

I'm considering deploying an application that will us an IP address locaiton
database provided by Ip2location.com...
http://www.ip2location.net/ip2locat...-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!Fabuloussites wrote:
> I'm considering deploying an application that will us an IP address locait
on
> database provided by Ip2location.com...
> http://www.ip2location.net/ip2locat...-component.aspx
> their .net component reads data from a binary file. I'm guess i'm wonderi
ng
> 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? Wou
ld
> a high traffic webisite be able to use a serialized file efficiently if th
e
> 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).
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
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com
"Fabuloussites" wrote:

> I'm considering deploying an application that will us an IP address locait
on
> database provided by Ip2location.com...
> http://www.ip2location.net/ip2locat...-component.aspx
> their .net component reads data from a binary file. I'm guess i'm wonderi
ng
> 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? Wou
ld
> a high traffic webisite be able to use a serialized file efficiently if th
e
> file was read often?
> Thanks!
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!
"Peter Bromberg [C# MVP]" wrote:
> 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 u
se
> with the MapControl.js VE Maps control. which can also be used to lookup t
he
> 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 databas
e,
> 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
> --
> Co-founder, Eggheadcafe.com developer portal:
> http://www.eggheadcafe.com
> UnBlog:
> http://petesbloggerama.blogspot.com
>
>
> "Fabuloussites" wrote:
>
i found info on the live local service. Looks like it is still in beta
format.
http://download.microsoft.com/downl...iveLocalFAQ.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 work
s
with dot net 1.x and i want to use a 2.0 app.
thanks!
"Fabuloussites" wrote:
> Thanks for the useful information. I was not aware of the localive servic
e.
> 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!
> "Peter Bromberg [C# MVP]" wrote:
>
If it's only 27mb, why not just read the whole thing and put it in memory?
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:D3FEF3C4-5341-40CD-BE58-A8B915B4DB6B@.microsoft.com...
> I'm considering deploying an application that will us an IP address
> locaiton
> database provided by Ip2location.com...
> http://www.ip2location.net/ip2locat...-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!
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" wrote:

> If it's only 27mb, why not just read the whole thing and put it in memory?
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:D3FEF3C4-5341-40CD-BE58-A8B915B4DB6B@.microsoft.com...
>
>
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?
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:83853729-F280-4CE6-A619-8E1EAA662751@.microsoft.com...
> 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" wrote:
>
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 inf
o
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 accessin
g
1.8 million rows of data would impact the performace if the site had high
traffic.
"PJ" wrote:

> either way it's a wash if you just cache the information and code somethin
g
> to update the cache every...what...day? how often does city state
> information change?
>
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:83853729-F280-4CE6-A619-8E1EAA662751@.microsoft.com...
>
>
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.
Fabuloussites wrote:
> I'm considering deploying an application that will us an IP address locait
on
> database provided by Ip2location.com...
> http://www.ip2location.net/ip2locat...-component.aspx
> their .net component reads data from a binary file. I'm guess i'm wonderi
ng
> 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? Wou
ld
> a high traffic webisite be able to use a serialized file efficiently if th
e
> file was read often?
> Thanks!