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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-02 : 08:00:14
|
| mayank writes "Hi i have two questions-- 1. Can we Optimize stored proecedure so that it can't be optimize furhter? 2. how can we know that it is fully optimized?regardsmayank" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-02 : 08:01:32
|
| 1. Probably not, there's always room for improvement.2. Testing, testing, testing, testing, testing, and more testing. Have I mentioned testing yet? By the way, make sure you test your code. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-02 : 08:12:27
|
| Hi mayank, Welcome to SQL Team!"how can we know that it is fully optimized?"Post the code here on SQL Team and see if anyone can find anything wrong with it!Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-02 : 08:52:18
|
| Rob - I think you forgot to mention the most important thing - TESTING.Mayank - Fully Optimized implies an unchanging query and an unchanging set of data that it is querying against. Odds are those two will never converge. Meaning any given query can be optimized as best as possible, but once the underlying data changes and suddenly what worked great for the last results, doesn't work as well as it could for these results. Or the data remains static but this time a different parameter value to the stored procedure implies that it could have been optimized better. For instance: Just yesterday I was playing around showing someone new how to use the SQL Profiler tool to monitor performance. We did a simple select * from a table to see the time it took. The table happened to have 7 rows in it. I then did a select top 5 * from table, and much to my surprise the profiler indicated that it actually required more reads and worked slower than the select * by itself. However, the same TOP 5 which didn't perform well against a nearly empty table, will certainly perform better against the table when it has a million rows in it next week. If you show us your underlying table schema(s), including indexes, sample data, and the stored procedure we could help you get a good start on "optimizing it." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-02 : 11:04:38
|
I've been monitoring some Sprocs that called a lot each day, and their execution time is wandering. No idea why, as yet, but it doesn't mean that it isn't a fixed target.There were fine immediately after the last reboot, so at least I have a solution to that problem  Kristen |
|
|
|
|
|
|
|
|
Sp Optimization