SQL cache dependency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL cache dependency

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-05 : 04:34:54

Hi,

I need to provide a solution to a customer who is expecting 400,000 hits per day to a pricing based website. Essentially I need to display a database query using ASP.NET, the results may changes once or twice a day.

Is caching my best option? I've read a couple of articles regarding setting up SQL cache dependency in ASP.NET by using the aspnet_regsql.exe tool, but I have a couple of questions.

When its enabled does my code just need to query the database as normal, and SQL server will handle whether it serves the cached version or fetches the results from new (and updates the cache) or do I need to handle this manually.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:42:54

It depends on the query and the data being queried.

What do the query return? Some aggregated values? If so, you might need to denormalize part of the database and store daily/weekly/monthly aggregations in a table, which makes the query run much faster.

E 12°55'05.25"
N 56°04'39.16"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-05 : 04:50:50

Hi Peso,

No aggregates, just a straight select something similar to the following.

SELECT TOP 10 make,model,derivative,price2,price3 FROM vehicles WHERE offer=1

The table contains about 100,000 rows

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:56:24

Where is the ORDER BY?

Make a clustered index over Offer column. And an index over the Order by column
100,000 is not much. It should not take more than a fraction of a second to return.

E 12°55'05.25"
N 56°04'39.16"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-05 : 05:00:06

Sorry, ORDER BY price2

So, do you think I might be over-reacting if this is the query we are going to use? And simple indexing will be sufficient.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 05:06:45

I think so, because 100,000 records are a small set.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources