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:
>