serialized binary files vs Sql Server Performance..

Discussion in 'ASP .Net' started by =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=, Jan 6, 2006.

  1. 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!
     
    =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=, Jan 6, 2006
    #1
    1. Advertising

  2. =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=

    john smith Guest

    Fabuloussites wrote:
    > 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).
     
    john smith, Jan 6, 2006
    #2
    1. Advertising

  3. 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 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!
     
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Jan 6, 2006
    #3
  4. 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 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 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!
     
    =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=, Jan 6, 2006
    #4
  5. =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=

    Guest

    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 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!
     
    , Jan 6, 2006
    #5
  6. 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!
    "Fabuloussites" wrote:

    > 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 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 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!
     
    =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=, Jan 6, 2006
    #6
  7. =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=

    PJ Guest

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

    "Fabuloussites" <> wrote in message
    news:D...
    > 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!
     
    PJ, Jan 7, 2006
    #7
  8. 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" <> wrote in message
    > news:D...
    > > 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!

    >
    >
    >
     
    =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=, Jan 7, 2006
    #8
  9. =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=

    PJ Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in
    >> message
    >> news:D...
    >> > 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!

    >>
    >>
    >>
     
    PJ, Jan 7, 2006
    #9
  10. 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.

    "PJ" wrote:

    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in
    > >> message
    > >> news:D...
    > >> > 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!
    > >>
    > >>
    > >>

    >
    >
    >
     
    =?Utf-8?B?RmFidWxvdXNzaXRlcw==?=, Jan 7, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tommy Christian

    Saving serialized data to database problem

    Tommy Christian, Jul 25, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    1,289
    Tommy Christian
    Jul 25, 2003
  2. Chris
    Replies:
    16
    Views:
    3,986
    Chris
    Feb 28, 2006
  3. WebBuilder451

    save serialized binary to viewstate question

    WebBuilder451, Nov 19, 2008, in forum: ASP .Net
    Replies:
    5
    Views:
    343
    WebBuilder451
    Nov 20, 2008
  4. Jim
    Replies:
    6
    Views:
    768
  5. Remco Gerlich
    Replies:
    0
    Views:
    113
    Remco Gerlich
    Sep 29, 2004
Loading...

Share This Page