SQL compact edition: delete

Showing posts with label delete. Show all posts

Monday, March 26, 2012

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

[ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE

[FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet000]))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE

If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES

[FAXNET].[UniqueID]), DEFINE

[Expr1006] = [PROBE VALUE]))
| |--Sort(ORDER BY

[FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT

[MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT

[ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool
|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes
|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

All,
MS SQL Server 2000
Win 2K SP2
I'm trying to get a better handle on why the performance on a
particular DELETE statement is so poor.
Query: DELETE FROM FAXNET WHERE DataSetID = ?
Here is some basic information on my table:
FAXNET table Size: 3.1M (million) rows
Clustered index on DataSetID
Distinct values of DataSetID in table: 4305
7 other indices on the table including the built-in one for the primary
key, as well as a UNIQUE index on 5 columns
Unusual symptoms:
If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
quickly as possible, do my DELETE, it takes under 5 seconds. However,
if there is any significant delay between the UPDATE STATISTICS command
and the execution of the DELETE, the statement takes minutes (at least
5 - I haven't actually waited for it to complete).
Is the fact that I'm deleting based on the clustered index column value
significant (I'm pretty sure it's significant, but not sure how)?
Does anything about this scenario seem like expected results based on
the table and query setup?
How can I get better feedback on how this is being processed - what is
the best way to look into it? I attempted to look at execution plans
etc. but I haven't done that stuff on MS SQL Server ever (although I
used to be quite good at it 10 years ago on Informix :]).
We also see brutal lock contention for this query with SELECTs being
blocked after about two minutes into the execution. I've definitely
seen an exclusive table lock acquired due by this statement - can
anyone explain why?
Is there any standard maintenance that should be done - does the
clustered index need to be rebuilt periodically? I noticed that both
the "auto create statistics" and "auto update statistics" DB options
were set to ON for this database, so I don't feel like I need to
recommend UPDATE STATISTICS (am I dating myself yet?).
Any suggestions would be appreciated.
Thanks,
Wes Gamble> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
Yes , it is a good practice to rebuild CI periodically
Do you have a CI UNIQUE on DataSetId column? If you don't SQL Server will
rebuild all NCI ( because it has CI Keys as pointers to the actual data)
and it takes time
You may want to INSERT the data ( you do not want to delete) INTO a new
table and then delete the old table and rename a new tabe as an old one
"weyus" <wesgamble@.gmail.com> wrote in message
news:1162943286.659963.188050@.e3g2000cwe.googlegroups.com...
> All,
> MS SQL Server 2000
> Win 2K SP2
> I'm trying to get a better handle on why the performance on a
> particular DELETE statement is so poor.
> Query: DELETE FROM FAXNET WHERE DataSetID = ?
> Here is some basic information on my table:
> FAXNET table Size: 3.1M (million) rows
> Clustered index on DataSetID
> Distinct values of DataSetID in table: 4305
> 7 other indices on the table including the built-in one for the primary
> key, as well as a UNIQUE index on 5 columns
> Unusual symptoms:
> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
> Is the fact that I'm deleting based on the clustered index column value
> significant (I'm pretty sure it's significant, but not sure how)?
> Does anything about this scenario seem like expected results based on
> the table and query setup?
> How can I get better feedback on how this is being processed - what is
> the best way to look into it? I attempted to look at execution plans
> etc. but I haven't done that stuff on MS SQL Server ever (although I
> used to be quite good at it 10 years ago on Informix :]).
> We also see brutal lock contention for this query with SELECTs being
> blocked after about two minutes into the execution. I've definitely
> seen an exclusive table lock acquired due by this statement - can
> anyone explain why?
> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
> Any suggestions would be appreciated.
> Thanks,
> Wes Gamble
>|||> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
This suggests that you get different execution plans for the delete operations. However, the DELETE
you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
100% certainty that the update of statistics does change things and also check the execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"weyus" <wesgamble@.gmail.com> wrote in message
news:1162943286.659963.188050@.e3g2000cwe.googlegroups.com...
> All,
> MS SQL Server 2000
> Win 2K SP2
> I'm trying to get a better handle on why the performance on a
> particular DELETE statement is so poor.
> Query: DELETE FROM FAXNET WHERE DataSetID = ?
> Here is some basic information on my table:
> FAXNET table Size: 3.1M (million) rows
> Clustered index on DataSetID
> Distinct values of DataSetID in table: 4305
> 7 other indices on the table including the built-in one for the primary
> key, as well as a UNIQUE index on 5 columns
> Unusual symptoms:
> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
> Is the fact that I'm deleting based on the clustered index column value
> significant (I'm pretty sure it's significant, but not sure how)?
> Does anything about this scenario seem like expected results based on
> the table and query setup?
> How can I get better feedback on how this is being processed - what is
> the best way to look into it? I attempted to look at execution plans
> etc. but I haven't done that stuff on MS SQL Server ever (although I
> used to be quite good at it 10 years ago on Informix :]).
> We also see brutal lock contention for this query with SELECTs being
> blocked after about two minutes into the execution. I've definitely
> seen an exclusive table lock acquired due by this statement - can
> anyone explain why?
> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
> Any suggestions would be appreciated.
> Thanks,
> Wes Gamble
>|||Tibor Karaszi wrote:
> This suggests that you get different execution plans for the delete operations. However, the DELETE
> you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
> 100% certainty that the update of statistics does change things and also check the execution plans.
What is the best way to check execution plans for DELETE statements?
Wes|||> What is the best way to check execution plans for DELETE statements?
Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"weyus" <wesgamble@.gmail.com> wrote in message
news:1163010846.268748.290020@.f16g2000cwb.googlegroups.com...
> Tibor Karaszi wrote:
>> This suggests that you get different execution plans for the delete operations. However, the
>> DELETE
>> you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
>> 100% certainty that the update of statistics does change things and also check the execution
>> plans.
> What is the best way to check execution plans for DELETE statements?
> Wes
>|||Tibor Karaszi wrote:
> > What is the best way to check execution plans for DELETE statements?
> Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
> or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
I see the same execution plan regardless of whether I run UPDATE
STATISTICS on the FAXNET table or not.
The execution plan is below - can anyone help me decipher it - does it
look prohibitively expensive assuming 3M+ rows in the FAXNET table?
Thanks,
Wes
=========================================
|--Sequence
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE:([FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet000]))
| |--Table Spool
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
| |--Table Spool
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES:([FAXNET].[UniqueID]), DEFINE:([Expr1006] = [PROBE VALUE]))
| |--Sort(ORDER BY:([FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[PK_FAXNET]))
| | |--Table Spool
| |--Row Count Spool
| |--Index
Spool(SEEK:([MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT:([ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes|||Wes,
There is a foreign key constraint from MailData.FaxNetID to
FAXNET.UniqueID (or vice versa). It seems, this column
MailData(FaxNetID) is not indexed. It could speed up your query
significantly (as could a reduction in the amount of indexes on FAXNET).
The query plan in the poorly performing situation must be different. The
estimated query plan might be enough. Wrap the statement in SET
SHOWPLAN_TEXT ON and SET SHOWPLAN_TEXT OFF and you should get it.
HTH,
Gert-Jan
weyus wrote:
> Tibor Karaszi wrote:
> > > What is the best way to check execution plans for DELETE statements?
> >
> > Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
> > or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
> I see the same execution plan regardless of whether I run UPDATE
> STATISTICS on the FAXNET table or not.
> The execution plan is below - can anyone help me decipher it - does it
> look prohibitively expensive assuming 3M+ rows in the FAXNET table?
> Thanks,
> Wes
> =========================================> |--Sequence
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet0]))
> | |--Table Spool
> | |--Clustered Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet00]),
> WHERE:([FAXNET].[DataSetID]=57242))
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet000]))
> | |--Table Spool
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
> | |--Table Spool
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IDXZIP]))
> | |--Table Spool
> |--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
> | |--Nested Loops(Left Semi Join, OUTER
> REFERENCES:([FAXNET].[UniqueID]), DEFINE:([Expr1006] = [PROBE VALUE]))
> | |--Sort(ORDER BY:([FAXNET].[UniqueID] ASC))
> | | |--Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[PK_FAXNET]))
> | | |--Table Spool
> | |--Row Count Spool
> | |--Index
> Spool(SEEK:([MailData].[FaxNetID]=[FAXNET].[UniqueID]))
> | |--Clustered Index
> Scan(OBJECT:([ABSMain].[dbo].[MailData].[PK_MailData]))
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[NODUPE]))
> | |--Table Spool
> |--Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
> |--Table Spool|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

[ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE

[FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet000]
))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IX_FAXNET]
))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE

If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES

[FAXNET].[UniqueID]), DEFINE

[Expr1006] = [PROB
E VALUE]))
| |--Sort(ORDER BY

[FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT

[MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT

[ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes

Tuesday, March 20, 2012

Pls Help-2000 Replication

How do I know whether the record in a row was insert,update and delete that replicated to a subscriber?
Is there a flag or an indicator in msmerge_contents,msmerge_tombstone ,msmerge_genhistory tables or any other msdb/replication system tables?You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks

Originally posted by joejcheng
You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Sory I left out a sentence. Let say, just to know whether the records in MSMERGE_CONTENTS and MSMERGE_TOMBSTONE has been send over to replicate on Publisher or Subscriber.

Thanks again!

Originally posted by eshl
Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks|||msrepl_commnads and msrepl_transactions.|||I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Originally posted by joejcheng
msrepl_commnads and msrepl_transactions.|||back to this issue, I could find the history record that stored the latest record that was merged in th publisher and subscriber. By using this, I will be able to know which record was generated and which wasnt. This is helpful in determining the records that was replicated or not yet.
I m looking into modifying the replication engine,MSSQL replication engine is flexible.

Originally posted by eshl
I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Monday, March 12, 2012

Pls help! Delete records in VB.net

Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right?
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help

Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc

Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)

objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()Check out this Quickstart for information on how to accomplish this:Server-Side Data Access: Deleting Data in a SQL Database

Terri

PLS Help with query

Please help me with complicated query:
eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
value in "a" field is dublicate, but leave one of each. in simple sentence I
need to delete invert distinct of field "a".
Please help me with this query
Rows are NOT duplicated, just value in one of fields are
There are primary key in this table
Following the similar explanation of this table
log_id int(11) UNSIGNED auto_increment //ID
rnd_id int(11) //SOMETHING
option_id int(11) //SOMETHING
timestamp DateTime //SOMETHING
ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
host varchar(70) //SOMETHING
agent varchar(80) //SOMETHING
thxHi
You may want to try something like:
DELETE FROM MyTable
FROM MyTable A
WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
A.log_id > B.log_id )
or
DELETE FROM A
FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
B.log_id
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Please help me with complicated query:
> eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> value in "a" field is dublicate, but leave one of each. in simple sentence
I
> need to delete invert distinct of field "a".
> Please help me with this query
> Rows are NOT duplicated, just value in one of fields are
> There are primary key in this table
> Following the similar explanation of this table
> log_id int(11) UNSIGNED auto_increment //ID
> rnd_id int(11) //SOMETHING
> option_id int(11) //SOMETHING
> timestamp DateTime //SOMETHING
> ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> host varchar(70) //SOMETHING
> agent varchar(80) //SOMETHING
> thx
>
>|||Thank you for response, but
As far as I see it will delete everything.
I need to leave one of each kind
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> Hi
> You may want to try something like:
> DELETE FROM MyTable
> FROM MyTable A
> WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> A.log_id > B.log_id )
> or
> DELETE FROM A
> FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> B.log_id
> John
> "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > Please help me with complicated query:
> > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> > value in "a" field is dublicate, but leave one of each. in simple
sentence
> I
> > need to delete invert distinct of field "a".
> > Please help me with this query
> > Rows are NOT duplicated, just value in one of fields are
> > There are primary key in this table
> > Following the similar explanation of this table
> > log_id int(11) UNSIGNED auto_increment //ID
> > rnd_id int(11) //SOMETHING
> > option_id int(11) //SOMETHING
> > timestamp DateTime //SOMETHING
> > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > host varchar(70) //SOMETHING
> > agent varchar(80) //SOMETHING
> >
> > thx
> >
> >
> >
>|||Hi
It will only delete those that have a higher log_id and the same Ip address.
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:Owbc3aoMEHA.3940@.tk2msftngp13.phx.gbl...
> Thank you for response, but
> As far as I see it will delete everything.
> I need to leave one of each kind
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> > Hi
> >
> > You may want to try something like:
> >
> > DELETE FROM MyTable
> > FROM MyTable A
> > WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> > A.log_id > B.log_id )
> >
> > or
> >
> > DELETE FROM A
> > FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> > B.log_id
> >
> > John
> >
> > "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> > news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > > Please help me with complicated query:
> > > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows
where
> > > value in "a" field is dublicate, but leave one of each. in simple
> sentence
> > I
> > > need to delete invert distinct of field "a".
> > > Please help me with this query
> > > Rows are NOT duplicated, just value in one of fields are
> > > There are primary key in this table
> > > Following the similar explanation of this table
> > > log_id int(11) UNSIGNED auto_increment //ID
> > > rnd_id int(11) //SOMETHING
> > > option_id int(11) //SOMETHING
> > > timestamp DateTime //SOMETHING
> > > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > > host varchar(70) //SOMETHING
> > > agent varchar(80) //SOMETHING
> > >
> > > thx
> > >
> > >
> > >
> >
> >
>|||Hi Tamir,
I noticed that the issue was posted in
microsoft.public.sqlserver.programming with the same title. Based on my
test, John's T-SQL statements hit the right answer.
Anyway, if you have follow up questions, please post there and I will be
glad to work with you.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

PLS Help with query

Wednesday, March 7, 2012

please interpret this delete with 2 from clauses

On Sat, 30 Jun 2007 14:10:23 -0000, metaperl <metaperl@.gmail.com>
wrote:

>I pretty much think I know what this does, but I've never seen a
>DELETE statement with 2 FROM clauses:
>delete from #acc
> from ggg d
> where #acc.id=d.nbr
> and d.oid='294'
The first FROM is optional, and most examples in this group don't use
it.

>I think this is the same as
>DELETE FROM #acc
>WHERE #acc.id IN
> (SELECT nbr FROM ggg d
> WHERE d.oid ='294' )
Yes, that does the same thing. So does:
DELETE FROM #acc
WHERE EXISTS
(SELECT * FROM ggg d
WHERE d.oid ='294'
AND #acc.id = nbr )

>but it never hurts to make sure

Especially with DELETE!!! 8-)
Roy Harvey
Beacon Falls, CT

please interpret this delete with 2 from clauses

I pretty much think I know what this does, but I've never seen a
DELETE statement with 2 FROM clauses:
delete from #acc
from ggg d
where #acc.id=d.nbr
and d.oid='294'
I think this is the same as
DELETE FROM #acc
WHERE #acc.id IN
(SELECT nbr FROM ggg d
WHERE d.oid ='294' )
but it never hurts to make sure :)On Sat, 30 Jun 2007 14:10:23 -0000, metaperl <metaperl@.gmail.com>
wrote:
>I pretty much think I know what this does, but I've never seen a
>DELETE statement with 2 FROM clauses:
>delete from #acc
> from ggg d
> where #acc.id=d.nbr
> and d.oid='294'
The first FROM is optional, and most examples in this group don't use
it.
>I think this is the same as
>DELETE FROM #acc
>WHERE #acc.id IN
> (SELECT nbr FROM ggg d
> WHERE d.oid ='294' )
Yes, that does the same thing. So does:
DELETE FROM #acc
WHERE EXISTS
(SELECT * FROM ggg d
WHERE d.oid ='294'
AND #acc.id = nbr )
>but it never hurts to make sure :)
Especially with DELETE!!! 8-)
Roy Harvey
Beacon Falls, CT

please interpret this delete with 2 from clauses

Especially with DELETE!!! 8-)
Roy Harvey
Beacon Falls, CT

please I need this!

hi all

Any body knows where I can get the SQL Versión: 8.0 Service Pack 3 ?.
this SP3 allow create, edit, delete tables from VS.NET 2003.

or I'm wrong?

please anybody?SQL Server 2000 SP3 is downloadable from the MS SQL server website.

Monday, February 20, 2012

Please Help, I need information about restoring!

Help!!! Help !!! Help !!!!

I have really done it this time. I ran a delete statement in the query analyzer and I did not see what tables were link to the table I was deleting from. But the table was linked to two other tables and the delete statement cascaded deleting information from other tables that is needed. I need to restore that information without losing the records that were added before and after the delete. The last back up that I have seen was 10/9/02 at 3:34 am. Is there anyway that I can restore the data I lost and keep the new data added after the last backup.I believe you will need to backup the log with NO_TRUNCATE and then using the restore example:

RESTORE DATABASE MyNwind
FROM MyNwind_1_DBBackup
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO

Need to look into Books-On-Line

Please help! How to execute a Query without keeping log?

I try ro delete many data from a table but the log file is always full so
that I cannot delete the uncessary data in a table.
How can I disable the keeping log function so that I can execute the Query
successfully.
--
Regards,
Anthony LamHi Anthony,
If it is SQL server 2000 , check the recovary model. If the database is not
critical u can change the model to Simple and run the command Backup tran
dbname with no_log.
After this u can run the delete statement.
In the other way if u database is very critical , perform a backup logdbname
to disk='c:\dbname.trn'
after this run the delete statement again.
If you are running SQL 7 , Simple recovary model is equalent to "truncate
log on checkpoint' db option. The rest of things are same as SQL 2000.
Thanks
Hari
MCDBA
"AA" <anthony@.jadeflex.com> wrote in message
news:uTgtSMfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> I try ro delete many data from a table but the log file is always full so
> that I cannot delete the uncessary data in a table.
> How can I disable the keeping log function so that I can execute the Query
> successfully.
> --
> Regards,
> Anthony Lam
>|||If it was a problem about running out of the log space with a single DELETE
statement, changing the recovery model to SIMPLE will not help.
1. You may get away with TRUNCATE TABLE because it uses less tran log space
by only recording the page deallocations in the tran log.
2. Or you can put your DELETE in a loop and delete a smaller chunk each time
followed by a BACKUP LOG statement. The size of the 'chunk' is basically the
number of rows to delete.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMC3BUfmDHA.2528@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> If it is SQL server 2000 , check the recovary model. If the database is
not
> critical u can change the model to Simple and run the command Backup tran
> dbname with no_log.
> After this u can run the delete statement.
> In the other way if u database is very critical , perform a backup
logdbname
> to disk='c:\dbname.trn'
> after this run the delete statement again.
> If you are running SQL 7 , Simple recovary model is equalent to "truncate
> log on checkpoint' db option. The rest of things are same as SQL 2000.
> Thanks
> Hari
> MCDBA
>
> "AA" <anthony@.jadeflex.com> wrote in message
> news:uTgtSMfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> > I try ro delete many data from a table but the log file is always full
so
> > that I cannot delete the uncessary data in a table.
> > How can I disable the keeping log function so that I can execute the
Query
> > successfully.
> >
> > --
> > Regards,
> >
> > Anthony Lam
> >
> >
>

Please help! How to execute a Query with keeping log?

I try ro delete many data from a table but the log file is always full so
that I cannot delete the uncessary data in a table.
How can I disable the keeping log function so that I can execute the Query
successfully.
--
Regards,
Anthony LamAlready answered in another group. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"AA" <anthony@.jadeflex.com> wrote in message news:%23HPc5EfmDHA.2160@.TK2MSFTNGP10.phx.gbl...
> I try ro delete many data from a table but the log file is always full so
> that I cannot delete the uncessary data in a table.
> How can I disable the keeping log function so that I can execute the Query
> successfully.
> --
> Regards,
> Anthony Lam
>

Monday, March 26, 2012

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

Tuesday, March 20, 2012

Pls Help-2000 Replication

Monday, March 12, 2012

Pls help! Delete records in VB.net

PLS Help with query

PLS Help with query

PLS Help with query

Wednesday, March 7, 2012

please interpret this delete with 2 from clauses

please interpret this delete with 2 from clauses

please interpret this delete with 2 from clauses

please I need this!

Monday, February 20, 2012

Please Help, I need information about restoring!

Please help! How to execute a Query without keeping log?

Please help! How to execute a Query with keeping log?

SQL compact edition

Blog Archive

About Me