delete BLOB objects

When I delete rows from a table that contains an NTEXT column, I get a delet
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql

delete a text file

I have a Job which imports from a text file. The second step is to delete th
e
text file. I have use the following code and it reports success but the file
is still there. Any ideas?
EXEC master..xp_cmdshell 'Del D:\Documents and
Settings\Administrator\Desktop\testextra
ct.txt'Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Windows logon permission. I am always in as admin
"Aaron Bertrand [SQL Server MVP]" wrote:

> Who is SQL Server service starting as? My guess is that the account does
> not have write/modify permissions on a user's folder.
>
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>
>|||> Windows logon permission. I am always in as admin
In every single installation I have ever seen, *YOU* are not the user
account SQL Server is running as.
Go to the control panel on the SQL Server machine, open up the services
control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
the Log On tab. Dollars to donuts says that account is not your account.
But it is a given that *that* account needs permissions on the folder where
xp_cmdshell is attempting to play.|||You are right!! Placed the folder on another drive and it worked perfectly.
Thanks so much
"Aaron Bertrand [SQL Server MVP]" wrote:

> In every single installation I have ever seen, *YOU* are not the user
> account SQL Server is running as.
> Go to the control panel on the SQL Server machine, open up the services
> control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
> the Log On tab. Dollars to donuts says that account is not your account.
> But it is a given that *that* account needs permissions on the folder wher
e
> xp_cmdshell is attempting to play.
>
>

delete a field help?

I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?You don't have a primary key on that field with a unique constraint? (something like an identity column?)

If you don't have anything to uniquely identify the record, you will have to select one of the records into a temp table, delete the record, then insert the record back. There really isn't any other way to do it.|||UserId has an indentity set, but not in the table that it references to,it is not the primary key.

Could you explain a bit more what would I need to do, as I have only done regular delitions,where things go smooth.

I will attach a file with the error eventhough you probabaly know what it is

Thanks for help

I forgot to tell you that the record I am trying to delete it from is in a view,don't konw if that makes any difference?|||Can you post the table structure of the table you're trying to delete from and the table that's not letting you delete?|||ok,viewSurveySubmission is where the double record is and RelocateeServices will not let me delete it.|||You need to find where the problem is in the base tables and delete the record from there. The view will try to delete from both tables, which it cannot do because another record is still using one of the records from a table you are trying to delete from. Make sense?

In this case find the duplicate in the base table and delete that record.|||Thanks to all willing to help,but I have actually been able to do it myself.

Thanks

Default value in textboxes are lost when a drop down does a post b

We have a couple of dropdown boxes and a text field as parameters to a report.
The options in the second dropdown box depends on the selection of the first
one, so I'm happy with the fact that a post back occurs when the selected
option in dropdown box 1 is changed.
However the value entered by the user in the text box get overriden by the
default value, that I'm not too happy with.
Is there a way of preserving user input?
Fred.
--
FredFred, have you tried NOT setting a default for the second parameter?
If that doesn't work then I think the answer is no!
Chris
Fred wrote:
> We have a couple of dropdown boxes and a text field as parameters to
> a report.
> The options in the second dropdown box depends on the selection of
> the first one, so I'm happy with the fact that a post back occurs
> when the selected option in dropdown box 1 is changed.
> However the value entered by the user in the text box get overriden
> by the default value, that I'm not too happy with.
> Is there a way of preserving user input?
> Fred.

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new session
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.Correct. MARS in yukon is designed to solve this.
http://msdn.microsoft.com/library/en-us/dnsql90/html/MARSinSQL05.asp?frame=true
--
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>