deleting old cursors

Hello. How can I delete an old cursor which appears when I execute the statement

select * from sys.dm_exec_cursors(0) ?

Thanks.

KILL session_id

...or alternatively if you can issue commands against the connection reported in the session_id column (for instance if the problem is with a SSMS connection that you have open) then you can issue:

CLOSE <cursor name>

DEALLOCATE <cursor name>

Chris

|||Thanks a lot.|||

Just to add that the first example I gave was really an 'emergency' measure. It would be advisable to track and fix the cause of the problem.

Chris

|||That was exactly what I was looking for, an emergency measure. I couldn't use close and deallocate. I forgot to mention that I am using SQL SERVER 2005. In a stored procedure I commented out a block together with close and deallocate for the respective cursor. The weird thing is that the procedure has only the declaration/definition of the cursor, otherwise there is no reference to it. I realized that it still exists when I received the error "cursor ... already exists". Thanks again for your answer.

deleting old cursors

Hello. How can I delete an old cursor which appears when I execute the statement

select * from sys.dm_exec_cursors(0) ?

Thanks.

KILL session_id

...or alternatively if you can issue commands against the connection reported in the session_id column (for instance if the problem is with a SSMS connection that you have open) then you can issue:

CLOSE <cursor name>

DEALLOCATE <cursor name>

Chris

|||Thanks a lot.|||

Just to add that the first example I gave was really an 'emergency' measure. It would be advisable to track and fix the cause of the problem.

Chris

|||That was exactly what I was looking for, an emergency measure. I couldn't use close and deallocate. I forgot to mention that I am using SQL SERVER 2005. In a stored procedure I commented out a block together with close and deallocate for the respective cursor. The weird thing is that the procedure has only the declaration/definition of the cursor, otherwise there is no reference to it. I realized that it still exists when I received the error "cursor ... already exists". Thanks again for your answer.

Deleting duplicate records

I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila

quote:

---

>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from

---

SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/

quote:

---

>.
>

---

|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila

quote:

---

>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of

---

select..group by

quote:

---

>statement.
> If you want to use group by statement, the content you

---

select should in

quote:

---

>the group by or have a function tell the machine how to

---

modify the data.

quote:

---

>Best Wishes
>Wei Ci Zhou
>
>.
>

---

|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy

quote:

---

>--Original Message--
>Wei,
>What should I do to delete these records' Can you

---

help

quote:

---

>with a better script?
>Thanks.
>Laila
>
>
>select..group by
you[QUOTE]
>select should in
>modify the data.
>.
>

---

|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?

Deleting a Database

I am using ADO to execute a DROP DATABASE command. Before that I run some
query against the DB to do some checks. However I get the meesage "Cannot
drop the database XXX because it is currently in use". If I do not run the
query before the DROP works. I have made sure that all connections to the DB
are closed.
I run the SQL Profiler and noticed that there is an "Audit Login" line added
before a command and a corresponding "Audit Logout" afterwards. Now I
noticed that the "Audit Logout" command takes some time to appear in the
trace window. I put a message box before my DROP command and after my query
and waited till the "Audit Logout" appeared. Then the DROP worked. So it
seems that there is some kind of delay before the DB is "released" after my
query is run.
Is there some expalnation/solution to that?
Thanks
YannisYannis wrote on Wed, 17 May 2006 10:30:01 +0300:

> I am using ADO to execute a DROP DATABASE command. Before that I run some
> query against the DB to do some checks. However I get the meesage "Cannot
> drop the database XXX because it is currently in use". If I do not run the
> query before the DROP works. I have made sure that all connections to the
> DB are closed.
> I run the SQL Profiler and noticed that there is an "Audit Login" line
> added before a command and a corresponding "Audit Logout" afterwards. Now
> I noticed that the "Audit Logout" command takes some time to appear in the
> trace window. I put a message box before my DROP command and after my
> query and waited till the "Audit Logout" appeared. Then the DROP worked.
> So it seems that there is some kind of delay before the DB is "released"
> after my query is run.
> Is there some expalnation/solution to that?
> Thanks
> Yannis
Do you have connection pooling enabled? Connections which are "closed" by
the application will be passed to the pool so they can be reused by another
request, rather than starting a new connection from scratch. The delay will
be due to the connection pool holding the connection open and then releasing
it after the set timeout.
Dan|||Thank you Daniel,
I don't do anything relating to connection pooling.
Any idea where that may occur in ADO and what I can do to overcome it?
Yannis
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:%23GWlEFZeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Yannis wrote on Wed, 17 May 2006 10:30:01 +0300:
>
> Do you have connection pooling enabled? Connections which are "closed" by
> the application will be passed to the pool so they can be reused by
> another request, rather than starting a new connection from scratch. The
> delay will be due to the connection pool holding the connection open and
> then releasing it after the set timeout.
> Dan
>|||You must prevent other connections to access the database:
alter database <db name>
set single_user
with rollback immediate;
use master;
drop database <db name>;
ML
http://milambda.blogspot.com/|||If you're using ODBC drivers, you'll need to make a change to your ODBC
settings in Windows. If you're not, you'll have to dig around for how to
change this for OLE DB or whatever connection type you're using.
Dan
Yannis wrote on Wed, 17 May 2006 15:34:14 +0300:
> Thank you Daniel,
> I don't do anything relating to connection pooling.
> Any idea where that may occur in ADO and what I can do to overcome it?
> Yannis
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:%23GWlEF
ZeGHA.380@.TK2MSFTNGP04.phx.gbl...

DELETE without logging

Hi all,
Is there a way to execute a DELETE sentence with a WHERE condition (cannot use TRUNCATE TABLE) without logging in order to execute a fast deletion of records?
Thanks in advance.
God Bless.Q1 Is there a way to execute a DELETE statement with a WHERE clause (cannot use TRUNCATE TABLE) without logging?


A1 No. (Not in any kind of production setting. Also, note that truncations are not logged on a row by row basis.)

Q2 Is there a way to execute a DELETE statement with a WHERE clause in order to execute a "faster" deletion of records?


A2 Sometimes, yes. Generally optimizing large scale deletions involves on the data, indexing, the schema, and the environment. Sometimes it is a matter or having a suitable index the system can take advantage of; sometimes it may be a matter of reducing concurrency locking issues; sometimes it may be a matter of dropping excessive indexes (and later recreating needed ones); often several such factors may be involved.

Delete takes extremly long time.

guys, im running MSSQL 2000 latest SP on PIII-1Ghz, 4GB RAM, SCSII and all good things but...

...when i execute this simple query to delete 8,000 records out of 18,000 total in the table it takes 11 seconds:

delete from tempViewResults where opportunity_id = '016158'

i do have an index on opportunity_id.
any ideas why? is this normal?

any help is greatly appreciated.please anybody?|||In this case, the index is not really helping you so much as hurting you. In order for the optimizer to consider using the index, you have to be going after <10% of the table. How many other indexes are there on the table? Also check for triggers on the table that may be firing for deletes.|||Also consider to create that "temp" table in the tempdb, which is faster.|||Two more cents:

Cent #1:Your delete statement will take longer if their are cascading relationships set up with subtables.

Cent #2:I'm not sure if using tempdb would be faster, because it still would be writing data to disk, but if you can get away with using a table variable which uses memory you might be able to avoid disk writes altogether.

blindman|||Using a #temp table is faster if your database is in FULL or BULK-LOGGED model.

Of course if your database is in SIMLE model you won't get any speed improvement|||Manowar:

What makes the temptable faster than a stored table? I couldn't find anything in Books Online regarding this. I'd have thought that querying a stored table would enable the optimizer to take advantage of cached query plans. Could you point me to where this is explained or documented in Books Online or Microsoft's support site?

blindman|||I think there's nothing about it on BOL or MSDN. I've found a note on Kalen Delaney "Inside SQL Server 2000" world-famous book.

You can look in the section about temp (#) tables. Kalen says that since in the tempdb sql server only register log data for rollback purposes (and not for database recovery), data modification operations can be up to for time faster that in a standard db.

Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users ??
regards
Hi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.
|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards
|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards
|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>[quoted text clipped - 14 lines]
|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.
|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users ??
> regards
|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>[quoted text clipped - 6 lines]
|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:

Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>>Hi sarajuma !
>>You could use a more granualar lock like ROWLOCK specified after the
>>Tablename in the Delete Statement. This will produce more overhead but
>>could solve you problem in some ways.
>>DELETE FROM SomeTable WITH (ROWLOCK)
>>HTH, Jens Suessmeyer.
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>>Hi sarajuma !
>[quoted text clipped - 14 lines]
>> regards|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
>>Hi
>>You have to have a WHERE condition in your DELETE statement . Also you can
>>remove all indexes defined on the table and re-create them after
>>deletion.
>>Hi sarajuma !
>>[quoted text clipped - 14 lines]
>> regards|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>[quoted text clipped - 6 lines]
>> regards|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
>>In addition to the other posts: Are you saying that you have foreign key referring to this table?
>>If
>>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>>[quoted text clipped - 6 lines]
>> regards|||are there any indexed views involved?|||On Tue, 13 Dec 2005 07:26:42 GMT, "sarajuma" <u16630@.uwe> wrote:
>I want to execute a delete statement for one table , which is very common
>and so many tables refers to it
>when the delete is hapening all the users will be blocked and the delete
>never happens
>Is there any way that I can delete rows without blocking other users '?
One would expect it to complete.
Can you show us the SQL for the delete, and/or the table declaration?
Are there any joins involved in the delete?
J.

Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've adde
d
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>
>[quoted text clipped - 14 lines]|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key ref
erring to this table? If
so, did you create indexes over those foreign key columns in the other table
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key re
ferring to this table? If
>so, did you create indexes over those foreign key columns in the other tabl
es?
>
>[quoted text clipped - 6 lines]|||So do you have indexes on your foreign key columns? This can help delete and
update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:

delete sql not working.

hi

win 2k and xp
excel 2k
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?

Sub UPDATED_DELETE()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date

MyDate = Format(Date, "MM/DD/YYYY")

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
conn.Open

Set cmd.ActiveConnection = conn

cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute
conn.Close
End SubI think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||I think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||to begin with, LIKE should only be used with strings

what is DateOnCall? datetime or varchar?|||Be smart and create a stored procedure in you SQL Server database that accepts StopTime, EndTime, ClientName, Representative, and DateOnCall as parameters and deletes the records you want. Then just call the procedure with the values from your spreadsheet.

...and look up the syntax and usage of the LIKE operator too. I suspect it needs wildcards, or at the very least is inappropriate for Date values (as Rudy said).

But I really think the problem is in your methodology, not your syntax...

Delete query taking long time to execute

hi,
I have a table which is a Master to other 12 tables and it is Child for
other 2 tables.
Therefore, It has 14 relationships with other tables. The size of table is
very small. (approx 30-40 rows). It also has a clustered index in its primary
key.
The problem I am facing here is that when I try to delete a row from the
table using its primary key in where clause, it takes more than 30 seconds to
execute it. Any subsequent attempt will be of a shorter duration (approx
20-25 sec.) but still it is very time taking.
I can't understand what could be the problem here when the table size is
very small. Is it because of constraints or any other sort.
Please comment if you have any idea about it. Any help would be highly
appreciated.
Thanks!
Regards,
~Somesh
Hi,
This is because of the multiple relationship. While deleting it checks all
the relationship. Where as if you execute a SELECT statement for the same
query
it will be faster. If you can do this during offline you could disable the
constraints [See ALTER TABLE WITH NOCHECK] to make the DELETE faster.
Since the number of records is very less it is not required to disable the
constratins
Thanks
Hari
"Somesh" <Somesh@.discussions.microsoft.com> wrote in message
news:FD6E5B4A-34AF-4CF9-948D-6EA21944587C@.microsoft.com...
> hi,
> I have a table which is a Master to other 12 tables and it is Child for
> other 2 tables.
> Therefore, It has 14 relationships with other tables. The size of table is
> very small. (approx 30-40 rows). It also has a clustered index in its
> primary
> key.
> The problem I am facing here is that when I try to delete a row from the
> table using its primary key in where clause, it takes more than 30 seconds
> to
> execute it. Any subsequent attempt will be of a shorter duration (approx
> 20-25 sec.) but still it is very time taking.
> I can't understand what could be the problem here when the table size is
> very small. Is it because of constraints or any other sort.
> Please comment if you have any idea about it. Any help would be highly
> appreciated.
> Thanks!
> Regards,
> ~Somesh
>
|||Thank you Hugo for the information.
FYI, I have asked him to do the NOCHECK when the system is offline (No users
are connected) and after the delete activity he can
make ALTER statement WITH CHECK. For one of my manual delete ( 5 million
record deletion) I did this and identified a huge
performance difference.
Thanks
Hari
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@.4ax.com...
> On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:
>
> Hi Hari,
> There are of course two downsides to disabling the constraints:
> 1. If there is some error in the manual checking process, or some
> concurrent opdate after the manual checking, the database may be left in
> an unconsistent state.
> 2. Even if all checks were made aith 100% accuracy, SQL Server doesn't
> know this, so the constraints are marked as "not trusted" once they are
> re-enabled. That means that the optimizer can no longer rely on those
> constraints for considering shortcuts for the query plan, which may
> result in longer running queries. (Unless you use the WITH CHECK option
> when the constraints are re-enabled, but in that case you still get the
> same performance hit, only at a later time).
> --
> Hugo Kornelis, SQL Server MVP