Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
AlexI have a .NET application that needs to work with about 5,000,000 XML files 5Kb each. Mostly the application randomly reads these files and it adds/modifies about 100 files every 10 minutes. What would be the best data storage in tirms of performance (connection, search and retrieval times) a big SQL table that uses NTEXT to store XML or regular NTFS file system (500 folders with 10,000 files each)? How big the difference will be? -- Message posted via webservertalk.com http://www.webservertalk.com/Uwe/Fo...eneral/200511/1
Post Follow-up to this message
Re: AlexHi Use SQL Server 2005 and load the XML files into the new XML data type. http://msdn2.microsoft.com/en-us/library/ms190936.aspx Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: mike@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "Performance: SQL table vs NTFS via webservertalk.com" <no@spam.pls> wrote i n message news:5780ab7f8a064@u we... >I have a .NET application that needs to work with about 5,000,000 XML files > 5Kb each. Mostly the application randomly reads these files and it > adds/modifies about 100 files every 10 minutes. What would be the best > data > storage in tirms of performance (connection, search and retrieval times) a > big SQL table that uses NTEXT to store XML or regular NTFS file system > (500 > folders with 10,000 files each)? How big the difference will be? > > -- > Message posted via webservertalk.com > http://www.webservertalk.com/Uwe/Fo...eneral/200511/1
Post Follow-up to this message Re: AlexMike thanks for the response. I do not really need to do any XPath operation s on the level of the database. I believe the XML data type should be slower and probably take more space to store than NTEXT or VARCHAR(max). At this momemnt I just want to find out whether storing data in SQL will be slower/faster than storing same data using millions of regular files. -- Message posted via http://www.webservertalk.com
Post Follow-up to this message Re: AlexAlex via webservertalk.com (no@spam.pls) writes: > Mike thanks for the response. I do not really need to do any XPath > operations on the level of the database. I believe the XML data type > should be slower and probably take more space to store than NTEXT or > VARCHAR(max). At this momemnt I just want to find out whether storing > data in SQL will be slower/faster than storing same data using millions > of regular files. My guess is that regular files are faster, but its definitely more difficult to manage, as there is no transactional control for file- system and database operations. If you are to store the data in the database, don't use the deprecated ntext data type. I would agree with Mike that the xml type is the best choice, but if you only want to store raw text, then nvarchar(MAX) should be your choice. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pr...oads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodin...ions/books.mspx
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
