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.

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.