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 |
|
GaryJordan
Starting Member
1 Post |
 Posted - 2004-12-08 : 05:37:24
|
| I am having a problem with updating some records. I have a table with 15000 records, it has a clustered index on the primary key only, no other indexes have been created. Select, Insert, Update and Delete statements are all working well most of the time, however the problem I am having happens when the server has not been used for a while.I have an ASP .NET application which issues the DML statements, it is only on a development system so this part of the application is often left on its own to do nothing. It performs very well most of the time, however when I leave it overnight and then come back in, in the morning, the application takes about 60 seconds to perform Inserts and Updates (I haven't tested Deletes yet), however any Select statements that the application issues are fast and at the normal speed.The type of Update statement the application would typically execute is shown below....UPDATE ATECatering SET FedDate = '2004-1-1', BookedNumber = 200, ActualFed = 200 WHERE ID = 20268I am very confused because once the first statement is made in the morning the application then speeds back up again and all the DML statements are quick. The only thing I can think of that could cause this is perhaps some sort of problem with the clustered index. Maybe I have a setting somewhere on the index that is incorrect, however the clustered index is just the standard one created by SQL Server 2000 when you create a primary key.Does anyone know why I am experiencing a temporary loss of performance when the application has not been used in a while? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-08 : 05:56:49
|
| Cache flushed....as a result of overnight operations, backups, data re-organisations, stop/start server? |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-08 : 17:45:52
|
| I bet it's the ASP.net and not the SQL stuff. I've seen this a bunch of times. The first time you hit an ASP.net page, it compiles / loads it into memory. After that, it's smoking fast.After some time or a file changes then it has to be recompiled and reloaded. Unfortunatly, I don't know of a solution. The only thing I think would work is maybe ngen for .net. DO some googling for ngen and asp.net.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Clustered Index Problems on Update