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.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2009-06-23 : 12:08:17
|
| Hi,Just a general question here. I am redesigning parts of my application and am considering a change.Currently I have a heavy query that is called from my .NET application, since its quite heavy, its cached for a period of 6 hours. Its been working fine for years, but I had an idea that might work a little better, and give me some more flexibility. The issue I would like to improve is that when my web application resets, this cache is cleared, and we have to re-execute the heavy query. If rather than run this query and cache it in the web app, I had a SQL job that ran every 6 hours, and put the results into a results table, my application could just query this table.If I were to do this, I think I would clear out the table and then fill it with a INSERT INTO statement. This would give me some added flexibility so that I could do JOINS off this table to get more "live" data, which I previously couldnt do with a cached dataset in the web app.My question is, how can I prevent my ASP.NET web app from querying and returning data in between the DELETE and INSERT statements of this job, that fills the new results table ? Should I employ a special technique ? Or should I not be taking this approach ? I expect this step last just a portion of a second, and it very well may never happen, just want to take all precautions, and am curious as to the best way to do this.Any tips much appreciated !!Thanks again :),Mike123 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-23 : 12:12:10
|
| depending on how much data you are loading you could put it in a transaction - a delete followed by an insert. follow it up with an update stats (which can be done online outside the transaction). You could also load up the data in another table, index it, then open a transaction, drop the first one, rename the second etc..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-06-29 : 09:01:39
|
| Sounds like you might be re-inventing indexed views. If not, transactions are the way to go. Yours is a textbook example of what they are for. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-29 : 09:08:14
|
And snapshot isolation leve maybe?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
webappguru
Starting Member
10 Posts |
Posted - 2009-07-02 : 12:54:05
|
The only thing that scares me about this process is if in the unlikely event you had some SQL Server issue between the delete and the new insert or while deleting, or while inserting... you run the risk of data being incomplete.Its always better to update than delete and re-insert IMO....quote:
Originally posted by mike123
Hi,Just a general question here. I am redesigning parts of my application and am considering a change.Currently I have a heavy query that is called from my .NET application, since its quite heavy, its cached for a period of 6 hours. Its been working fine for years, but I had an idea that might work a little better, and give me some more flexibility. The issue I would like to improve is that when my web application resets, this cache is cleared, and we have to re-execute the heavy query. If rather than run this query and cache it in the web app, I had a SQL job that ran every 6 hours, and put the results into a results table, my application could just query this table.If I were to do this, I think I would clear out the table and then fill it with a INSERT INTO statement. This would give me some added flexibility so that I could do JOINS off this table to get more "live" data, which I previously couldnt do with a cached dataset in the web app.My question is, how can I prevent my ASP.NET web app from querying and returning data in between the DELETE and INSERT statements of this job, that fills the new results table ? Should I employ a special technique ? Or should I not be taking this approach ? I expect this step last just a portion of a second, and it very well may never happen, just want to take all precautions, and am curious as to the best way to do this.Any tips much appreciated !!Thanks again :),Mike123
"I live for programming"www.aliabidhusain.net |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-03 : 23:12:09
|
quote:
Originally posted by webappguru
The only thing that scares me about this process is if in the unlikely event you had some SQL Server issue between the delete and the new insert or while deleting, or while inserting... you run the risk of data being incomplete.
Bollocks. That's what transactions are for.quote:
Originally posted by webappguruIts always better to update than delete and re-insert IMO....
Correct, because you don't have to remove everything from the index and data pages then re-create it & re-check all the RI. Not because 'something might go wrong'. |
|
|
|
|
|
|
|
|
deleting and then inserting vs updating ?