Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:
> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>
Showing posts with label logic. Show all posts
Showing posts with label logic. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
Extended Stored Procedure Overhead ?
Hi,
We have a "Clean Name" function that I wrote in T-SQL that strips
off unwanted characters and does a lot of other logic on a customer
name in order to get the best possible match. This function was
running fine, but we now have to run it on a lot more data and it was
taking about 36hrs to run. Because we are still on 2000 we decided to
write an extended stored procedure with the function written in C
thinking it would be a lot faster. Well now that the function is
complete and tested we are doing speed tests and the T-SQL one is
actually faster!
The CPU load when running the T-SQL one is about 30% but when I run
the C one it uses less then 5% of the CPU. I was wondering why the
extended stored procedure one would be so slow ?On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> Hi,
> We have a "Clean Name" function that I wrote in T-SQL that strips
> off unwanted characters and does a lot of other logic on a customer
> name in order to get the best possible match. This function was
> running fine, but we now have to run it on a lot more data and it was
> taking about 36hrs to run. Because we are still on 2000 we decided to
> write an extended stored procedure with the function written in C
> thinking it would be a lot faster. Well now that the function is
> complete and tested we are doing speed tests and the T-SQL one is
> actually faster!
> The CPU load when running the T-SQL one is about 30% but when I run
> the C one it uses less then 5% of the CPU. I was wondering why the
> extended stored procedure one would be so slow ?
I think I'll answer this one my self.
When I was doing my tests I was doing it on a VMWare server, but once
we moved it over to a real server, we got about 4x the performance!
My guess is that all the context switches kill the VM.|||> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Yes, performance testing needs to be done on a real server, not a virtual
one.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ismell1@.gmail.com> wrote in message
news:19e824b8-db7f-467f-a2de-aaf98d9d7f48@.i29g2000prf.googlegroups.com...
> On Jan 11, 8:45 am, isme...@.gmail.com wrote:
>> Hi,
>> We have a "Clean Name" function that I wrote in T-SQL that strips
>> off unwanted characters and does a lot of other logic on a customer
>> name in order to get the best possible match. This function was
>> running fine, but we now have to run it on a lot more data and it was
>> taking about 36hrs to run. Because we are still on 2000 we decided to
>> write an extended stored procedure with the function written in C
>> thinking it would be a lot faster. Well now that the function is
>> complete and tested we are doing speed tests and the T-SQL one is
>> actually faster!
>> The CPU load when running the T-SQL one is about 30% but when I run
>> the C one it uses less then 5% of the CPU. I was wondering why the
>> extended stored procedure one would be so slow ?
> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
We have a "Clean Name" function that I wrote in T-SQL that strips
off unwanted characters and does a lot of other logic on a customer
name in order to get the best possible match. This function was
running fine, but we now have to run it on a lot more data and it was
taking about 36hrs to run. Because we are still on 2000 we decided to
write an extended stored procedure with the function written in C
thinking it would be a lot faster. Well now that the function is
complete and tested we are doing speed tests and the T-SQL one is
actually faster!
The CPU load when running the T-SQL one is about 30% but when I run
the C one it uses less then 5% of the CPU. I was wondering why the
extended stored procedure one would be so slow ?On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> Hi,
> We have a "Clean Name" function that I wrote in T-SQL that strips
> off unwanted characters and does a lot of other logic on a customer
> name in order to get the best possible match. This function was
> running fine, but we now have to run it on a lot more data and it was
> taking about 36hrs to run. Because we are still on 2000 we decided to
> write an extended stored procedure with the function written in C
> thinking it would be a lot faster. Well now that the function is
> complete and tested we are doing speed tests and the T-SQL one is
> actually faster!
> The CPU load when running the T-SQL one is about 30% but when I run
> the C one it uses less then 5% of the CPU. I was wondering why the
> extended stored procedure one would be so slow ?
I think I'll answer this one my self.
When I was doing my tests I was doing it on a VMWare server, but once
we moved it over to a real server, we got about 4x the performance!
My guess is that all the context switches kill the VM.|||> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Yes, performance testing needs to be done on a real server, not a virtual
one.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ismell1@.gmail.com> wrote in message
news:19e824b8-db7f-467f-a2de-aaf98d9d7f48@.i29g2000prf.googlegroups.com...
> On Jan 11, 8:45 am, isme...@.gmail.com wrote:
>> Hi,
>> We have a "Clean Name" function that I wrote in T-SQL that strips
>> off unwanted characters and does a lot of other logic on a customer
>> name in order to get the best possible match. This function was
>> running fine, but we now have to run it on a lot more data and it was
>> taking about 36hrs to run. Because we are still on 2000 we decided to
>> write an extended stored procedure with the function written in C
>> thinking it would be a lot faster. Well now that the function is
>> complete and tested we are doing speed tests and the T-SQL one is
>> actually faster!
>> The CPU load when running the T-SQL one is about 30% but when I run
>> the C one it uses less then 5% of the CPU. I was wondering why the
>> extended stored procedure one would be so slow ?
> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Monday, March 19, 2012
Extended Stored Procedure Overhead ?
Hi,
We have a "Clean Name" function that I wrote in T-SQL that strips
off unwanted characters and does a lot of other logic on a customer
name in order to get the best possible match. This function was
running fine, but we now have to run it on a lot more data and it was
taking about 36hrs to run. Because we are still on 2000 we decided to
write an extended stored procedure with the function written in C
thinking it would be a lot faster. Well now that the function is
complete and tested we are doing speed tests and the T-SQL one is
actually faster!
The CPU load when running the T-SQL one is about 30% but when I run
the C one it uses less then 5% of the CPU. I was wondering why the
extended stored procedure one would be so slow ?
On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> Hi,
> We have a "Clean Name" function that I wrote in T-SQL that strips
> off unwanted characters and does a lot of other logic on a customer
> name in order to get the best possible match. This function was
> running fine, but we now have to run it on a lot more data and it was
> taking about 36hrs to run. Because we are still on 2000 we decided to
> write an extended stored procedure with the function written in C
> thinking it would be a lot faster. Well now that the function is
> complete and tested we are doing speed tests and the T-SQL one is
> actually faster!
> The CPU load when running the T-SQL one is about 30% but when I run
> the C one it uses less then 5% of the CPU. I was wondering why the
> extended stored procedure one would be so slow ?
I think I'll answer this one my self.
When I was doing my tests I was doing it on a VMWare server, but once
we moved it over to a real server, we got about 4x the performance!
My guess is that all the context switches kill the VM.
|||> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Yes, performance testing needs to be done on a real server, not a virtual
one.
Hope this helps.
Dan Guzman
SQL Server MVP
<ismell1@.gmail.com> wrote in message
news:19e824b8-db7f-467f-a2de-aaf98d9d7f48@.i29g2000prf.googlegroups.com...
> On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
We have a "Clean Name" function that I wrote in T-SQL that strips
off unwanted characters and does a lot of other logic on a customer
name in order to get the best possible match. This function was
running fine, but we now have to run it on a lot more data and it was
taking about 36hrs to run. Because we are still on 2000 we decided to
write an extended stored procedure with the function written in C
thinking it would be a lot faster. Well now that the function is
complete and tested we are doing speed tests and the T-SQL one is
actually faster!
The CPU load when running the T-SQL one is about 30% but when I run
the C one it uses less then 5% of the CPU. I was wondering why the
extended stored procedure one would be so slow ?
On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> Hi,
> We have a "Clean Name" function that I wrote in T-SQL that strips
> off unwanted characters and does a lot of other logic on a customer
> name in order to get the best possible match. This function was
> running fine, but we now have to run it on a lot more data and it was
> taking about 36hrs to run. Because we are still on 2000 we decided to
> write an extended stored procedure with the function written in C
> thinking it would be a lot faster. Well now that the function is
> complete and tested we are doing speed tests and the T-SQL one is
> actually faster!
> The CPU load when running the T-SQL one is about 30% but when I run
> the C one it uses less then 5% of the CPU. I was wondering why the
> extended stored procedure one would be so slow ?
I think I'll answer this one my self.
When I was doing my tests I was doing it on a VMWare server, but once
we moved it over to a real server, we got about 4x the performance!
My guess is that all the context switches kill the VM.
|||> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Yes, performance testing needs to be done on a real server, not a virtual
one.
Hope this helps.
Dan Guzman
SQL Server MVP
<ismell1@.gmail.com> wrote in message
news:19e824b8-db7f-467f-a2de-aaf98d9d7f48@.i29g2000prf.googlegroups.com...
> On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Subscribe to:
Posts (Atom)