Really big Database

Results 1 to 3 of 3

Thread: Really big Database

  1. #1
    Derek Baron Guest

    Default Really big Database

    Soon I will have a database with approx 1/2 million records in it at any given time. There will be about 100,000 new records submitted every day and 100,000 records deleted. My scripts tend to output and input no more than 100 records at any given time.<BR><BR>Will I need to break this database out into smaller, more managable chunks?<BR>How fast of a server will I need, if its only purpose is to handle this large SQL file?<BR><BR>Thanx,<BR>Derek

  2. #2
    Join Date
    Dec 1969

    Default RE: Really big Database

    Provided you&#039ve got a reasonably-sized machine and your database is well designed you shouldn&#039t have any problems. The trick is to make sure that your database is structured to support the most common types of queries - a careful choice of indexes makes all the difference.<BR><BR>&#039Fraid I can&#039t actually tell you what sort of hardware you need - I&#039m just a developer :-)<BR><BR>Dunc

  3. #3
    Join Date
    Dec 1969

    Default RE: Really big Database

    Duncan&#039s suggestion of proper indexing is an incredibly valuable one! The SQL database that most of my data is pulled from is built by a third party vendor and I have no control over indexes. There are essentialy two ways to located a member in our database, their MemberID, or their email address. When a member logs on to one of our applications we give them the choice of entering their memberID or their email address. When I first wrote the stored procedures that located a member in the database only the MemberID field was indexed. Searches by MemberID were almost instaneous, while searches by email took approximately 15 seconds. When our vendor upgraded from SQL 6.5 to 7.0 they added an index to the email field! Now both stored procedures execute beautifully. This database only has 48,000 names in it though, so I imagine that the performance gain/decrease would be even more dramatic with a larger data base. Another application that I wrote connects to a SQL 7.0 that I wrote. This database now has almost 700,000 rows and grows by about 10,000 rows per day. It sits on a single processor (PIII 866) Windows 2000 server with 256Mb of Memory. Performance is fine. Follow the normal ASP/ADO performance coding guidlines and use stored procedure where it makes sense. If you notice performance issues and are confident in your ASP/ADO look at your SQL/Sotred procedures and database configuration... make sure that the fields you search by are indexed, avoid LIKE statements, when joining tables join them at the end of your statement, only return the minimum amount of data required, make sure that connection pooling is configured on ODBC configuration. Since there are a number of ways to write to a database, it worth the time to investigate the performance issues with the various methods. I will look into this and post whatever I find<BR><BR>enjoy,<BR>bart<BR><BR>good luck<BR><BR>bart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts