Is anybody knowing How to Improve performance in Stored procedure using cashe memory

Question

Is anybody knowing How to Improve performance in Stored procedure using cashe memory

Dipak-387207

Mr or Mrs. 500

Points: 566
More actions
April 4, 2008 at 4:19 pm

#128472

Here I am going to explain that porblem i am facing.
I have one stored procedure with lots of Joins now when i am executing this SP first time it gives me a data within 25 to 30 secods and second time when i am going to run it gives me a 15 seconds and third time when i am going to run it gives a 5 seconds, but I would like to execute this SP within 5 seconds everytime, can anybody tell me how to manage stored data in cashe memory for a day?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

The best way to improve performance of a query is to examine the Execution plan, Statistics (Set statistic IO on) and tune the query based on this information. Execution time can be deceiving because the load on the server and network varies and execution time can be affected by these things. You also cannot be guaranteed that the data will be cached. It is likely that the stored procedure will be compiled and in memory.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Johan Bijnens SSC Guru Points: 135310 More actions · Answer 2

- as Jack stated, you cannot guarantee data will be kept in memory.

- Best you can do is check your proc that it is performing its queries in the most optimal way (performance wise), provide supporting indexes to speed up these queries, ...

- if different queries are executed when different parameters are provided, maybe even consider creating your proc using the with recompile parameter. There is also an overhead to that, but if the queries perform better, it may pay off.

- If you can, check your sqlserver instance so it has enough memory available.

- avoid physical i/o as much as possible.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Like said before you should check your executions plans first in case there are any differences betwen the fast and the slow executing ones.If you find a differece between the plans it can be due to different parameters which makes using an index more or less usefull.

If you find that a certain plan is generally faster you might want to try using a plan guide. It basically means that you tell the procedure which plan to use instead of creating a new plan or using a cached one.

For more information see http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

[font="Verdana"]Markus Bohse[/font]

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 4

If it's just cache, then execute it a few times to warm the cache. You can use a job to do this.

If it's parameters, I'm not sure what you can do. could execute a few times with the different parameters if they're known.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Rather than "warming the cache"... try analyzing the code to see where indexes would help you solve the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

First thing is to check through the query and make sure that it is written as optimally as possible (avoid triangular joins, subselects in the select clause, functions on columns in from and where, ...)

Once the query is written as optimally as possible, make sure that the tables have appropriate indexes. Covering indexes if at all possible.

Badly written queries or poor indexing usually means that the server has to read more data than actually necessary to run the query

If you would like some specific help with this proc, please post the code, the table structures and the index definitions.

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass