Which is better: XML File or SQL Query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DeepDiver - 21 May 2004 08:14 GMT

I am building a Web site navigation menu User Control; this User Control
will be embedded in most of the dynamic pages of the site. The menu data
will change over time, but not frequently: most weekly traffic will see the
menu choices as static data. So my question is: will I get better
performance reading the data from an XML file on the hard drive, or by
querying the SQL Server database?

It seems to me that reading the XML file would be faster, particularly if
the server can cache the xml data. (But how does it know when the data in
the file changes?)

Then again, I recall reading some time back about the server being able to
cache SQL query results. (If so, how persistent is this? Will the menu data
in the query cache get purged if there are lots of other database queries
occurring as visitors browse from page to page?)

So is there is a penalty for populating the menu nodes directly from the
database? If not, then it would certainly be simpler than having to
re-generate the XML file when the menu data does change, even if it changes
infrequently.

Your input and recommendations on this would be greatly appreciated!

Reply to this Message

Alvin Bruney [MVP] - 21 May 2004 15:31 GMT

you won't know until you test it. The database is optimized for data
retrieval and storage, but you have to factor in connectivity and network
issues. Disk reads are orders of magnitude slower when compared to memory
reads so one solution is to store the data in a cache envelope which you can
replenish as needed. Keep the cache envelope current by setting a file
dependency.

Signature

Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok

>I am building a Web site navigation menu User Control; this User Control
> will be embedded in most of the dynamic pages of the site. The menu data
[quoted text clipped - 21 lines]
>
> Your input and recommendations on this would be greatly appreciated!

Reply to this Message

DeepDiver - 22 May 2004 08:25 GMT

> The database is optimized for data retrieval and storage, but you
> have to factor in connectivity and network issues. Disk reads are
> orders of magnitude slower when compared to memory reads

Thanks for the reply Alvin.

For the sake of this example, let's assume that the SQL server is running on
the same box as the Web server. So we can ignore the network load issues.

I realize that reading an XML file from the disk is much slower than pulling
from memory, but doesn't hitting the database also mean a read from the data
store on the hard drive?

> so one solution is to store the data in a cache envelope which you can
> replenish as needed. Keep the cache envelope current by setting a file
> dependency.

I'm sorry, but I don't know how to do that. Can you recommend a resource
where I can learn more about this technique?

Also, is this only for caching the contents of a disk read (e.g., my XML
file)? What about caching SQL queries? Is that automatically handled by SQL
Server and/or IIS?

I still don't know which is likely to yield better performance: executing a
SQL query (cached or not) to build the navigation menu for every page hit,
or extracting the data from an XML file (cached or not) to build the menu.

More input anybody? Thanks!

Reply to this Message

Alvin Bruney [MVP] - 22 May 2004 14:59 GMT

> I realize that reading an XML file from the disk is much slower than
> pulling
> from memory, but doesn't hitting the database also mean a read from the
> data
> store on the hard drive?

Yes, but a data store is optimized for reading and writing.

> I'm sorry, but I don't know how to do that. Can you recommend a resource
> where I can learn more about this technique?

DataSet ds = commandobject.("select * from tablename");
Session["_data"] = ds;

later,
DataSet ds = Session["_data"] as DataSet;
if(ds != null && ds.Tables[0].Rows.Count > 0)
do something with dataset

or you can use the same strategy to cache the query instead of the dataset
for less load on the cache object. Have a look at ASP.NET caching on msdn.

> Also, is this only for caching the contents of a disk read (e.g., my XML
> file)? What about caching SQL queries? Is that automatically handled by
> SQL
> Server and/or IIS?

The cache can hold any type of object.

Normally, the sql engine caches the query so that a subsequent exact query
has an optimal path and can avoid the overhead of a compilation. But that
assumes that the query is repeated. There is no optimal path for new
queries. These two cachings are unrelated to each other. One happens in the
framework, the other happens in the datastore.

> I still don't know which is likely to yield better performance

You have to test it. All the debate in the world will not help here.

Signature

Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok

>> The database is optimized for data retrieval and storage, but you
>> have to factor in connectivity and network issues. Disk reads are
[quoted text clipped - 30 lines]
>
> More input anybody? Thanks!

Reply to this Message

Rate this thread:

Which is better: XML File or SQL Query?

Free Magazines