Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.
If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>
|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Monday, March 26, 2012
Latch wait time
Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
Latch wait time
Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
Friday, March 9, 2012
Largest SQL Server 2000 Enterprise Ed. Databases
I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evalua.../wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techin...scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evalua.../wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techin...scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
Labels:
database,
databases,
disk,
edition,
enterprise,
largest,
largestsql,
microsoft,
mysql,
obtain,
oracle,
server,
space,
specification,
sql,
thehardware
Largest SQL Server 2000 Enterprise Ed. Databases
I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
Mike
Hi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluat...wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinf...calability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
Mike
Hi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluat...wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinf...calability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
Labels:
database,
databases,
disk,
edition,
enterprise,
largest,
largestsql,
microsoft,
mysql,
obtain,
oracle,
server,
space,
specification,
sql,
thehardware
Largest SQL Server 2000 Enterprise Ed. Databases
I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluation/compare/wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluation/compare/wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
Monday, February 20, 2012
Large insert, how to switch off transactions?
Hello, I've a database with 8 million rows on my development pc and I've got
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:
> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:
> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>
Subscribe to:
Posts (Atom)