Hi All,

I am creating an applications with say of millions of users... I am using SQL Server to manage the data. 

In a page I need to fetch the data (only a single row) in the fastest way. I mean, even the microsecond difference also counts. Again 10s of 1000s of request will be coming per minute.

Now the question is -- Will it be advisable to keep data in XML & make it available for the request. (I am NOT talking about changing the db to XML. I will be keeping the data only in SQL server. BUT to make the request as fast as possible, shall I put the data into an XML file & fetch from there.)

Inshort the question is -- Data feching performance wise (for small amount of data and for large number of requets) - which is faster - XML or SQL Server? (Forget all other factors like security etc... consider only speed).

Kindly advice on this with quoting the supporting factors from sites...

Thank You All...

Shine

to be honest, I would look at caching the data in memory instead rather than moving small amounts of data from the DB to an Xml file.

Caching would work best here as there is no external access to a resource where there could be file IO contention.

With database connection pooling and if the SQL is a simple select then it would really be not much different from loading from an XML.

If the data is relational in nature with keys then moving to XML would be worse. If there are multiple reads then the Xml will suffer being a flat file.

I would suggest you look into caching to improve read performance if the data is static in nature.

Hi,

Thank your for the quick response.

But will it be advisable to cache millions of records in cache... though i am fetching only a small data (single row) in a request?

Thanks,

Shine

Hello Friend,

    If you ask which is faster sql or xml for small amount of data . I would answer xml (even use of  json is better).

But in your case if the request are coming so frequent; Best Way is to use Cache.

It wount be a problem to have millons of record for a table in Cache. It purely depends on your server RAM ,processor and other factors.  But in further cases if cache data exceeds the processor limit it would slow down the performance.

Solution:- Use Cache. And make sure your server is good enough to process them all in a goody manner.

Hi,

Thanks you... 

Let us examine the options both of you suggested .  

1) Cache

Say there are 1 million records, suppose each record taking just 1kb. We need 1GB of RAM dedicated for this... Practically if there is 2 or 3 kb per record then..... I am not planning to go with such a big config.Again the data will be varying... so frequently...

2) XML

I'm not sure I really understand your question.  Are you asking whether it would be faster to move your data from the database into an XML file first, and then return the XML, rather than returning the data directly from the database?  If so, I would think the answer is obvious: skip writing the XML file and just return the data.

In general, retrieving small files from the filesystem is slower than retrieving them from the database.  Once the filesize goes beyond roughly 20KB, then the filesystem can actually be faster.

As far as 100 queries per second against a table with millions of rows, that shouldn't be a problem as long as the table is properly indexed.  Let the database handle the caching.  If the data is in memory on the DB server, you should be able to reach query rates well in excess of 10,000 per second against a single table.

If microseconds matter, there's a lot more you need to think about than just this....

Hi,

What method you would suggest for SQL Server Caching? PINTABLE has some drawbacks & not supported any more.... 

I would be using SQL Server 2005 (or 2008) in the hosting environment.

Pls help...

(By the way, the case is exactly as you understood... I was planning to copy data to XML, keeping original data in DB & to fetch from XML).

Thanks,

Shine

There are a lot of variables for caching.  The best solution depends on the nature of your data, queries and application.  Speaking very generally only:

1. Lots of RAM on your DB machine.  With enough RAM, the DB acts like a giant cache.

2. Use SqlDependency or SqlCacheDependency on the web tier, to avoid re-reading tables unless they change.

3. Use the ASP.NET Cache object for medium-to-long-term caching.

The details quickly turn caching into a big subject.  (FWIW, I cover it thoroughly in my book).