Need expert's comment on deciding DB storage or file base

Hi Friends

I have an application that stores thousands of text files in database with average of 3KB each. and another table storing brief info about them that searched more frequently. with 140,000 records, my database performance get too slow.

I thought that i could store those files on disk that reduce my database size about 80%. these files are stored only f开发者_JAVA百科or the purpose of display (reading file content and display) but not for any database operation on them. but my website has about 1000 users and I'm afraid if i store those files on disk, it gets worst (disk I/O operation increase, reading files in different session could increase I/O moving hard disk head back and forward plus I can't control reading files in one thread only)

any guys has related experience ?

Please make this decision for me. THANKS THANKS

---

With out much more info I would suggest that you look more deeply into where the performance problem stems from. Are your queries efficient, are there indexes to support your queries.

With regard to storing the files in the database, well that is interesting. I can only give my personal opinion on that which is as a rule I would rather store the files on disk and just store the reference to the file ie. path in the database. But there are many pros/cons to consider here, one thing is that your backups will need to span more than just the database backup.

---

I will take your comment Please make this decision for me. THANKS THANKS literally.

• Install at least 8 gig of memory in the db server (better SQL cache)

  Configure two disks as a mirror and place the log file for your application db on the mirror. Do not compress this disk (faster log file writes)

  Configure 4 disks as a Raid 10 and place the db on the Raid 10. Do not compress this disk (better read performance than raid 5)

  Configure a single disk as the location of the tempDB. Do not compress this disk (isolate activity of tempDB)

  Run only SQL Server on the server.

  Ensure your db has the correct indexes by inspecting the missing index dynamic management view.

  Ensure your db has a maintenance plan to reduce db and index fragmentation

  Extract and reload each of the “text” files from the database. Prior to loading the text file compress (zip) the file with the tool of your choice. Store the “text” file in a varbinary datatype. Configure the application to uncompress the file prior to display (reduce disk IO for SQL to store 'text' and network io to client)

  Benchmark you application with a known number of users and searches

  Compare your benchmark with actual values during client usage

  Monitor disk wait, network IO and memory on a regular bases.

If you still have poor performance contract a SQL DBA to evaluate your system. I believe performanceDBA on this site is in the contract business.