| Author |
Topic |
|
rohitgraycell
Starting Member
8 Posts |
 Posted - 2006-05-01 : 08:28:58
|
| i want to speed up the stored procedures and i am now a days in search of ways to increase the performance of my application. Actually the stored procedures fetch a lot of data after joining a lot of tables and so i had to use temporary tables and table variables. Now my plan of action is that i want to stop some of my lengthy stored procedures from making log entries(so that the time for I/O with the log is saved). But i am not finding the proper command to execute from within my stored procedure that can interact with the sql server environment and do the task for me.Does anyone have a solution to this??? Rohit |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-01 : 10:32:52
|
| What activity in your stored procedure is causing log activity that you'd like to stop? I'm not sure I understand exactly what you are looking to do, or if it can be done.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
 |
|
|
rohitgraycell
Starting Member
8 Posts |
Posted - 2006-05-01 : 10:40:39
|
| Druer, Actually for eveyr update, detele and insert in a temp table , etc within a stored procedure causes entry to be written in log files which also slows my stored procedure. i have read in some article that if we stop these log entries then it reall speeds up the procedure because there is no I/O with the log files of our stored procedure. That is why for lengthy stored procedures i want to turn log entries off.Rohit |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-01 : 10:43:42
|
quote:
Originally posted by rohitgraycell
Druer, Actually for eveyr update, detele and insert in a temp table , etc within a stored procedure causes entry to be written in log files which also slows my stored procedure. i have read in some article that if we stop these log entries then it reall speeds up the procedure because there is no I/O with the log files of our stored procedure. That is why for lengthy stored procedures i want to turn log entries off.Rohit
You csn't stop Microsoft SQL Server from writing to the transaction log.CODO ERGO SUM |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-01 : 10:51:55
|
| So you are using #TEMP tables and that is what you'd like to stop logging. Try using TABLE variables instead if they will work for your situation. Checkout Books On Line to see what situations they will/won't work for.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-01 : 22:03:18
|
try to filter as much rows as you can try to determine which part of your sp is dragging its performance, generate the execution plan --------------------keeping it simple... |
|
|
|
rohitgraycell
Starting Member
8 Posts |
Posted - 2006-05-02 : 00:03:34
|
| I am using both table variables and #temp tables (as per situation).But that is not solving my problem , i have read some where that if we defer entries in the transaction log that speeds up performance but i am not able to do so...Rohit |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-02 : 00:13:07
|
I think the priority is to optimize your query by looking at the execution plan, index etc. As said, you can't stop SQL Server from writing to the transaction log.What is your stored procedure doing ? Is it performing any INSERT or UPDATE ? or purely SELECT only.
KH |
|
|
|
rohitgraycell
Starting Member
8 Posts |
Posted - 2006-05-02 : 01:23:38
|
| My stored procedure is fetching data from a lot of tables and a lot of filtering goes on within the procedure.So i need to use sometimes around 5-6 table variables + #temp tables . Also a lot of activity goes on within the loops and inserts and deletes from these temp tables goes in loops. Thats why i dont want these entries to go into the log and waste unnecessary time and then i select the data from the last table variable (or temp table).Rohit |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-02 : 05:27:35
|
| Sounds like its a complex situation, and you know what you are doing.However, if you haven't already done so, if you can move a "temp table" to become a nested sub-select then that would "prevent it being logged"Other than that all you do [that I can think of!!] is to try to reduce the number of rows that you initially save into the TEMP tables - e.g. by using tighter criteria.Kristen |
|
|
|
|
Stopping the Stored procedure from log entries