Locking

I have a series of stored procedures that are called from an outside
application. This application supports multi-threading, so I can have 10
instances of these stored procedures running at once. This is causing a grea
t
deal of locking. As I am working through the code, the biggest offenders are
"exists" and "not exists" statements. Each one reduces performance
dramatically. Is there another way to get the same effect and not use
"exists" and "not exists"? "In" and "Not In" are worse for locking.
Thanks for your help.Can we see some code and DDL?
AMB
"joesql" wrote:

> I have a series of stored procedures that are called from an outside
> application. This application supports multi-threading, so I can have 10
> instances of these stored procedures running at once. This is causing a gr
eat
> deal of locking. As I am working through the code, the biggest offenders a
re
> "exists" and "not exists" statements. Each one reduces performance
> dramatically. Is there another way to get the same effect and not use
> "exists" and "not exists"? "In" and "Not In" are worse for locking.
> Thanks for your help.|||Have you checked the execution plans for your EXISTS/NOT EXISTS queries? Are
they doing a table or index scan? Make sure you get an index s for best
performance.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"joesql" <joesql@.discussions.microsoft.com> wrote in message
news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
I have a series of stored procedures that are called from an outside
application. This application supports multi-threading, so I can have 10
instances of these stored procedures running at once. This is causing a
great
deal of locking. As I am working through the code, the biggest offenders are
"exists" and "not exists" statements. Each one reduces performance
dramatically. Is there another way to get the same effect and not use
"exists" and "not exists"? "In" and "Not In" are worse for locking.
Thanks for your help.|||It's hard to say without actually seeing what you are doing but it sounds
like there are no indexes for the EXISTS to work against. With a proper
index the EXISTS should be extremely efficient. Also make sure the stored
procedures are being called with the owner specified. exec dbo.yoursp
Andrew J. Kelly SQL MVP
"joesql" <joesql@.discussions.microsoft.com> wrote in message
news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
>I have a series of stored procedures that are called from an outside
> application. This application supports multi-threading, so I can have 10
> instances of these stored procedures running at once. This is causing a
> great
> deal of locking. As I am working through the code, the biggest offenders
> are
> "exists" and "not exists" statements. Each one reduces performance
> dramatically. Is there another way to get the same effect and not use
> "exists" and "not exists"? "In" and "Not In" are worse for locking.
> Thanks for your help.|||All tables have proper indexes and I am using dbo. for all calls.
"Andrew J. Kelly" wrote:

> It's hard to say without actually seeing what you are doing but it sounds
> like there are no indexes for the EXISTS to work against. With a proper
> index the EXISTS should be extremely efficient. Also make sure the stored
> procedures are being called with the owner specified. exec dbo.yoursp
>
> --
> Andrew J. Kelly SQL MVP
>
> "joesql" <joesql@.discussions.microsoft.com> wrote in message
> news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
>
>|||Here is a sample of code:
update #temp_events
set external_calc_ind = 'Y'
from pending_event pe,
pending_commission pc,
#temp_events e
where pe.pending_event_id = e.pending_event_id
and pc.pending_event_id = pe.pending_event_id
and not exists(select 1
from comm_event_process
where comm_event_id = pe.comm_event_id )
"Alejandro Mesa" wrote:
> Can we see some code and DDL?
>
> AMB
> "joesql" wrote:
>|||You;re sure there's an index on comm_event_id in the comm_event_process tab
le?
"joesql" wrote:
> Here is a sample of code:
> update #temp_events
> set external_calc_ind = 'Y'
> from pending_event pe,
> pending_commission pc,
> #temp_events e
> where pe.pending_event_id = e.pending_event_id
> and pc.pending_event_id = pe.pending_event_id
> and not exists(select 1
> from comm_event_process
> where comm_event_id = pe.comm_event_id )
> "Alejandro Mesa" wrote:
>|||Try,
update #temp_events
set external_calc_ind = 'Y'
where exists (
select
*
from
pending_event as pe
inner join
pending_commission as pc
on pc.pending_event_id = pe.pending_event_id
left join
comm_event_process as c
on c.comm_event_id = pe.comm_event_id
where
pe.pending_event_id = #temp_events.pending_event_id
and c.comm_event_id is null
);
be sure to have indexes in:
- pending_event(pending_event_id)
- pending_event(comm_event_id)
- pending_commission(pending_event_id)
- comm_event_process(comm_event_id)
- #temp_events(pending_event_id)
AMB
"joesql" wrote:
> Here is a sample of code:
> update #temp_events
> set external_calc_ind = 'Y'
> from pending_event pe,
> pending_commission pc,
> #temp_events e
> where pe.pending_event_id = e.pending_event_id
> and pc.pending_event_id = pe.pending_event_id
> and not exists(select 1
> from comm_event_process
> where comm_event_id = pe.comm_event_id )
> "Alejandro Mesa" wrote:
>|||That is the first thing I looked for. This is the primary key too, so it is
a
clustered index.
"CBretana" wrote:
> You;re sure there's an index on comm_event_id in the comm_event_process t
able?
> "joesql" wrote:
>|||This doesn't lock as bad, thanks for the input.
"Alejandro Mesa" wrote:
> Try,
> update #temp_events
> set external_calc_ind = 'Y'
> where exists (
> select
> *
> from
> pending_event as pe
> inner join
> pending_commission as pc
> on pc.pending_event_id = pe.pending_event_id
> left join
> comm_event_process as c
> on c.comm_event_id = pe.comm_event_id
> where
> pe.pending_event_id = #temp_events.pending_event_id
> and c.comm_event_id is null
> );
> be sure to have indexes in:
> - pending_event(pending_event_id)
> - pending_event(comm_event_id)
> - pending_commission(pending_event_id)
> - comm_event_process(comm_event_id)
> - #temp_events(pending_event_id)
>
> AMB
> "joesql" wrote:
>

Locked Stored Procedures

Hi

I am reasonably new to this so bear with me.

I have a copy of a SQL Server 2000 Database, and would like to see what the stored procedures are doing (as I think it is wrong).

in Server Management Studio the stored procedures have a lock symbol on them and when I try and view them I get the message:

TITLE: Microsoft SQL Server Management Studio

Script failed for StoredProcedure 'dbo.sp_procedure'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure '[dbo].[sp_procedureprocedure]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

This was developed by someone else, but I need to see it.

My question is, is there any way i can see the text of this stored procedure?

Thanks

Will

The stored procedure code has been encrypted. Apparently, whoever wrote the code didn't want others to read the code.

I suggest that you contact the person ( or vendor ) that supplied the stored procedure code and request a copy of the code.

LOCK Table necessary in stored procedure?

Hi,

I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.

I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.

The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.

The table looks like this:
Logid PK
LogTypeID -- what category
LogValue --
LogTime -- when it occurred

My imaginary stored procedure looks something like this:

CREATE PROCEDURE ShrinkDB AS
-- 1)
"lock table log" -- do I have to do something like this?

-- 2)
select * into log_keep FROM Log where
(
logtypeid <> 2020 AND -- activity played
logtypeid <> 5020 AND -- database connected
-- ...etc et.... about 10 different things I don't need to keep
or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours
)
-- 3)
drop table log

-- 4)
EXEC sp_rename 'log_keep', 'log'
GO

I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??

Hopefully it works that way, but if not I assume I'll run into these two problems:

- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?

- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?

Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject... :-)Hi,

Why drop the table? why not delete the data you have just taken, then you will never lose any data|||...simply because it takes such an insane amount of time. A regular "delete from log where logtypeId = stuff-I-don't-need" takes forever. :( The complete post is here, if you want the details: http://www.dbforums.com/showthread.php?threadid=979910|||You can open a transaction, and then do SELECT * FROM TBL WITH (TABLOCK) WHERE 1=2 as you first statement. Then, instead of moving 1-3% of data to a different permanent table, you can put it into a temptable (whether # or @., doesn't matter,) then truncate the original (instead of dropping it,) and putting the data from your temptable back into the original.|||Even truncate takes quite a few seconds, allthough I guess that's related to some locking issues and not the truncate process in it self.

The method involving dropping the table takes about 3-5 seconds, and is much faster than select-away-and-truncate. It sort of works, the problem with that solution is that PK, indexes, rights and what not are gone too. This can of course be re-created with a number of SQL sentences, but it's not exactly ideal......

But I cannot understand why deleting takes so much time? I'm assuming the cause of this problem is that about 50 clients are constantly logging into the table while I'm deleting.

I'm not an expert sql-stored procedure maker, anyone who whould take a crack at setting up something for me with locking (and whatever else) that would work? Would help me a lot!! :-)|||I can send you a contract, and upon signing you'll never have to deal with it again :cool:sql

Local variables in stored procedures

Hi!
I'm using SQL Server 7.0 and it is a multiuser application.
My problem is that sometimes during parallel calls to a stored procedure it
produces wrong results. It takes between 5 to 20 sec to execute the stored
procedure.
To be able to know why the result sometimes is wrong I will log some values
from local variables.
My guess is that my part-result from some lookups are overwritten.
But until I get enough log-results to analyse I have a couple of questions:
- Are local variables overwritten by another call to the same procedure?
- Can I save part-result in a more secure way? I still want to have the
possibilty to call the procedure in a parallel way.
- As a last option. Is there a simple way to forbid parallel calls to a
procedure? (I have read some about "set transaction isolation level
serializable" but I'm afraid it has too large impact on other calls that
questions the same tables that are in use in the stored procedure)
Hope someone has a godd answer to give.
Best regards
SvenneSvenne
Can you show us your SP's call and how do you handle local variables within
SP?
If you have SELECT/UPDATE/DELETE/INSERT operations within a SP try to wrap
it into BEGIN TRAN ...COMMIT commands
"Svenne" <sasodergren@.hotmail.com> wrote in message
news:%23Y0MMBp7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I'm using SQL Server 7.0 and it is a multiuser application.
> My problem is that sometimes during parallel calls to a stored procedure
> it produces wrong results. It takes between 5 to 20 sec to execute the
> stored procedure.
> To be able to know why the result sometimes is wrong I will log some
> values from local variables.
> My guess is that my part-result from some lookups are overwritten.
> But until I get enough log-results to analyse I have a couple of
> questions:
> - Are local variables overwritten by another call to the same procedure?
> - Can I save part-result in a more secure way? I still want to have the
> possibilty to call the procedure in a parallel way.
> - As a last option. Is there a simple way to forbid parallel calls to a
> procedure? (I have read some about "set transaction isolation level
> serializable" but I'm afraid it has too large impact on other calls that
> questions the same tables that are in use in the stored procedure)
> Hope someone has a godd answer to give.
> Best regards
> Svenne
>

Local Temporary Table Bottlenecks?

Greetings,
I'm fairly new to stored procedures and temporary tables and i was going
through this tutorial for a banner ad system.
http://aspnet.4guysfromrolla.com/ar...033104-1.2.aspx
it's a nice write up, how over i'm concearned over some of its logic. In
part two, the author discusses how he generates a temporary table each time
the NRCA_sp_Get_Random_Banner Stored Procedure is called. This would happen
d
each time a banner ad is requested from a page.
My concearn regards scalibility. Let's say that there is a high traffic
website that display banners on its homepage.
1) Would that create a bottle neck?
2) what would happen if two browser requests invoke the
NRCA_sp_Get_Random_Banner Stored Procedure at the same time?
3) Would the temp database create two temporary tables? Or, would one
request fail?
4) would it be better to make an actual table that is simply updated when
new ads are inserted or old ones are modified and query it?"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:3DDE7E76-CB03-49BB-8EA4-1D8B0A8D97E5@.microsoft.com...
> 1) Would that create a bottle neck?
Maybe. It depends on many factors, such as memory, the disk system
tempdb is on, and how it's configured. See the following KBA for some
hints:
http://support.microsoft.com/default.aspx/kb/328551?

> 2) what would happen if two browser requests invoke the
> NRCA_sp_Get_Random_Banner Stored Procedure at the same time?
Temp tables (except global temp tables, created with ##) are local to the
connection that created them. So it would work as expected.

> 3) Would the temp database create two temporary tables? Or, would one
> request fail?
Two.

> 4) would it be better to make an actual table that is simply updated when
> new ads are inserted or old ones are modified and query it?
I probably wouldn't use a table at all; I didn't read the article in
much depth, but I would probably do something like:
SELECT TOP 1
Ad_Id
FROM tbl_NRCA_Banner_Ads
WHERE
Page = @.Page
AND StartDate < getdate()
AND EndDate > getdate()
AND Active = 1
AND Weight <= @.MaxWeight
ORDER BY NEWID()
I believe this has the same net effect as the temp table approach -- but you
should test on your end to make sure.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thanks for the info Adam, i appreciate it. The reason the temporary table i
s
created is because each ad has a weight factor that will increase the
probability of its beign shows. So, an ad with weight 3 is three times more
likely to be shown that one with weight one.
So, the temporary table does this...
say there are two ads
ad one, weight 1
ad two, weight 3
i creates a temporary table with four records..
id | ad
1 ad one
2 ad two
3 ad two
4 ad two
so, ad two is given three entries (instead of one). Then a random selection
is made to get an ad and that will be the one that is displayed.
is there a better alternative to temp tables for this?
ad two
"Adam Machanic" wrote:

> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:3DDE7E76-CB03-49BB-8EA4-1D8B0A8D97E5@.microsoft.com...
> Maybe. It depends on many factors, such as memory, the disk system
> tempdb is on, and how it's configured. See the following KBA for some
> hints:
> http://support.microsoft.com/default.aspx/kb/328551?
>
> Temp tables (except global temp tables, created with ##) are local to th
e
> connection that created them. So it would work as expected.
>
> Two.
>
> I probably wouldn't use a table at all; I didn't read the article in
> much depth, but I would probably do something like:
> SELECT TOP 1
> Ad_Id
> FROM tbl_NRCA_Banner_Ads
> WHERE
> Page = @.Page
> AND StartDate < getdate()
> AND EndDate > getdate()
> AND Active = 1
> AND Weight <= @.MaxWeight
> ORDER BY NEWID()
> I believe this has the same net effect as the temp table approach -- but y
ou
> should test on your end to make sure.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>

Loads of System Generated Procedures In publisher

Dear Friends,
I have a database which is a published for 3 subscriber with bit of
variations. now while checking i found that there are 8870 system generated
procedures in the database with name.
ap_sel_0394BA4C0A144B4DB88E9E00ED0446B3_pal or similar way.
please suggest do i need the same.
Can i clean this? How?
Best regards
Sharad
Dear Paul,
Thanks. You have guide me several times i salute you for the knowledge you
have.
now with the query you have given i got the results of 56 rows but there are
8000+ procedure do i need to delete the rest.
Thanks and best regards
Sharad.
"Sharad2005" wrote:

> Dear Friends,
> I have a database which is a published for 3 subscriber with bit of
> variations. now while checking i found that there are 8870 system generated
> procedures in the database with name.
> ap_sel_0394BA4C0A144B4DB88E9E00ED0446B3_pal or similar way.
> please suggest do i need the same.
> Can i clean this? How?
> Best regards
> Sharad