Urgent !!!Processing Time

[agina1]

hii...

Is there anybody out there that can help me on how can I know the processing time taken for one transaction by using SQL Analyzer??

1)For example, I want to update using Analyzer and I would like to know time taken to do this update???

2) How to reduce processing time by using Store Procedures that using cursor?? I have add in some commit statement in my update statement.. Is there any other ways??

Please advise

[YuckFou]

You can turn on
"Show Client Statistics"
(Ctrl+Shift+S) and you'll see time taken..

[agina1]

i have read one article saying that we can reduce the processing time of store procedures by using WITH RECOMPILE statement like below

CREATE PROCEDURE ST_Daily_Old WITH RECOMPILE

have anyone tried this and is it true?

[rmiao]

Not really, depends on how to reuse execution plan.

[agina1]

rmio...

Cau you explain more how to reuse the execution plan? Thanks

[rmiao]

The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical, and when a new execution plan should not be cached or stored in memory. Otherwise, system can use same execution plan in cache and it's faster.

[agina1]

rmio...

When the new execution plan will happens?? Is it when you change your store procedures??? I still can use the WITH RECOMPILE Statement in order to reduce the processing time...I'm sorry if I'm wrong....

Please advise

[rmiao]

When modify sp, modify referenced objects, etc. But don't know how can you reduce proseccing time with this option since system has to build new plan every time you run it.

[agina1]

rmio...

have you tested it before???

[rmiao]

And you?

[agina1]

rmio....

I haven't tried it yet...I wanna to know your outcome if you have tried it...


I'm in the minutes of searching the pro and the con of using this method.. I thought you have tried this method so maybe you can share your experience with me...

Anyway, maybe I'll try it first and share with you then....

[MAK]

How does your procedure look like and how dynamic it is.

You can have table hints in all your queries you use in the USP which will force the query optimizer to use the same plan for all the queries inside your usp.

Usually recompiles are used only when there is a change in the query plan like you add new indexes and new columns or alter etc. for one time recompile you can do sp_recompile
so when the usp gets execute for the first time it will recompile and update the query plan.

If you want full analysis you can post a sample table , data and a procedure that u r using.

[rmiao]

I ran same sp on same table with and without option 'WITH RECOMPILE', it's faster without the option.

[agina1]

hiiii guys..

Rmio, thanks for trying it out...Like you said it's faster using Without "With Recompile' option... I wonder when is the right time to use this option...

Mak, can you please advise on this...


Thanks

[MAK]

Hi Agina.

You said you read an article about this "when we do a recompile it runs faster" can you direct me to that article.

Because there might be one reason (speculating) for that article could be true if the
1. stored procedure is poorly written with no table hints.
2. it receives so many parameters that it cannot maintain a static query plan
3. has distributed queries
4. constant schema changes
5. if queries are based on temp tables (drop and recreate)
6. temp tables
7. when referenced table itself is dropped and recreated

Basically if you have a very poor architecture then WITH recompile helps in increasing the performance

I faced this problem of sp recompiling automatically and being slow (we used set concat yields null off). once we replaced all the string concat with isnull, it ran 85% faster than before.

I know and I used KEEP PLAN option inside the procedures (DML) when I am sure how the execution plan should be and what index it is suppose to use.

If you are looking for optimizing your stored procedure, all you have to do it post the code here with sample data.


-MAK