Microsoft SQL Server forum

Author

Alex

Performance: SQL table vs NTFS via SQLMonster.com

2005-11-17, 8:24 pm

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

Mike Epprecht \(SQL MVP\)

2005-11-19, 11:23 am

Hi

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 in
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

Alex via SQLMonster.com

2005-11-19, 11:23 am

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.

--
Message posted via http://www.webservertalk.com

Erland Sommarskog

2005-11-20, 8:23 pm

Alex 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

Sponsored Links