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 |
|
karan_jv
Starting Member
2 Posts |
 Posted - 2010-05-26 : 08:17:49
|
| I have an SP which takes nearly a min when i run it with some parametrs. If I try running the SP for the second time the time decreases to 40 sec or less and next time its less than 30 sec. How can I stop this cold startups. Mainly I am trying to improve my SP performance and this is one problem i am facing.Also note i need to support SQL 2000, SQL 2005 and SQL 2008. Secondly In my SP we call a delete statement in a table.DELETE FROM TABLE_NAME WHERE USER_ID = @User_ID This table has a clustered index and 2 non clus index on it 9yes the indexex are required for subsequent joins in the SP). Cos of this the delete itself takes more than 15 sec to delete. Is there any way i can decrese this time. My company wants the total SP time to be less than 15 sec. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-05-26 : 08:59:19
|
| How much data do you have in these tables? And do you have index and statistics maintenance in place...?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-05-26 : 10:53:40
|
| improved perforance due to effects of cache....only way to avoid initial cost is to run the query via SQL Agent in an off-peak time-slot after SQL startup.improve delete times.... a) partition by userid b) delete with a smaller "set rowcount" value and loop until zero records deleted. minimise transaction logging. |
|
|
|
karan_jv
Starting Member
2 Posts |
Posted - 2010-05-27 : 01:04:40
|
| @Lumbago: thanks for the reply. As for testing purposes now I have some 200,000 lines only. The delete will remove all the 200,000 lines as in test i ahve put one user. Yes we have index maintenance in place. Not so sure abt statistics maintenance. How doe we do a statistics maintenance. Do we have command like DBCC REINDEX. @Andrew : Thanks for your tip. Let me check out by seting row count. How do I minimise transaction logging in my case. Partion wont work in my case cos mainly the customers need to do that and also we need to support 2000 also |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-07 : 04:29:50
|
| Sorry for the late reply, been on holiday for the past week. But varying performance based on different parameters is usually caused by either internal caching of data in sql server or by skewed data statistics. The internal caching you basically can't do much about but make sure you update statistics on a regular basis (I usually do this daily). Here is the link to the BOL article on how to update:http://msdn.microsoft.com/en-us/library/ms187348.aspx- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-06-07 : 04:38:59
|
Are there cascaded deleted enabled on the customer table?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
SP taking different times for same parameters