November 9, 2013 at 1:22 pm
Bhuvnesh (11/8/2013)
TheSQLGuru (11/8/2013)
the engine LIES to SSMS when UDFs are in play.please elaborate. Or ANy article reference.
Here's the proverbial tip of the iceberg on that subject...
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2013 at 2:52 pm
Thanks to knowing about it
November 12, 2013 at 10:12 am
Bhuvnesh (11/8/2013)
TheSQLGuru (11/8/2013)
the engine LIES to SSMS when UDFs are in play.please elaborate. Or ANy article reference.
Below is the UDF:
CREATE FUNCTION [dbo].[CheckDigit](@vol varchar(50))
returns int
as
begin
declare @trdx bigint,
@i int
select @i = 0,
@trdx = 0,
@vol = replace(@vol,'.','')
if isNumeric(@vol) = 1
begin
while len(@vol) > 0
begin
if @i in (0,1)
begin
select @i = 3
end
else if @i = 3
begin
select @i = 7
end
else if @i = 7
begin
select @i = 1
end
select @trdx = @trdx + left(@vol,1) * @i
if LEN(@vol) = 1
begin
select @vol = ''
end
else
begin
select @vol = substring(@vol,2,len(@vol))
end
end
end
return case when @trdx % 11 = 0 then 1
when @trdx % 11 = 1 then 0
else 11 - @trdx % 11
end
end
November 12, 2013 at 10:19 am
1) Yep - that's a scalar UDF alright 😀
2) At least it doesn't have ancillary data access!
3) Looks like some kind of funky iterative logic that might be able to take advantage of a Numbers table
4) How is it used in the query? If in the WHERE clause you are pretty much guaranteed to be hosed.
5) I wonder if a persisted calculated column would be workable here...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2013 at 10:32 am
Those are good workarounds, and it might be what we will end up doing. The SQL statement is something like this:
select someID, right ('0000000000' + convert(varchar(10), a.someID, 10)) +
convert (varchar, dbo.CheckDigit(right('0000000000' _ convert(varchar, someID),10)))
from my_table as a inner join some_other_table b on blah = blah
where some_column not in ('a', 'b', 'c');
EDIT: Both "blah" columns are bigint, and have a clustered index on them.
November 12, 2013 at 12:36 pm
I don't have (unpaid 😎 ) time to give it a go, but I would look to unwind that logic into the SELECT statement if possible.
Also, to validate that the UDF is the cause of your perf issues, run the query with some action on that column. Check for blocking/locking, query plan, stats IO, etc. All the usual suspects...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2013 at 12:47 pm
I agree with Kevin that really you could do with refactoring this function, however that does not deal with the problem of why there is such a difference in performance between the two systems...
So, if you care to post up the two .sqlplan files from Production and Dev/Test we can see if the answer lies within...:-)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 12, 2013 at 1:46 pm
TheSQLGuru (11/12/2013)
I don't have (unpaid 😎 ) time to give it a go, but I would look to unwind that logic into the SELECT statement if possible.Also, to validate that the UDF is the cause of your perf issues, run the query with some action on that column. Check for blocking/locking, query plan, stats IO, etc. All the usual suspects...
Thank you so much for your assistance!
When I take out the UDF, the statement runs in 2 seconds flat. I am not sure what you mean by "some action on that column", can you please provide an example?
I ran the query in production, it took 2 hours and 45 minutes. I had been running Profiler for the bach_completed event. Here is the result:
CPU: 8,830,218
Reads: 5,060
RowCounts: 16,187,830
I'm not sure why the row count is so high, total number of records that are returned by the query is just over 274K.
The same query on staging had the following results:
CPU: 29,796
Reads: 63,060
RowCounts: 16,168,656
Further, the SET STATISTICS IO on prod shows:
(274370 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table1'. Scan count 2, logical reads 962, physical reads 208, read-ahead reads 214, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 4095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
And on staging shows:
(274045 row(s) affected)
Table 'table2'. Scan count 1, logical reads 4094, physical reads 1, read-ahead reads 4006, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table1'. Scan count 1, logical reads 57686, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
It looks like due to the fact that the production SQL Server assigns less RAM to the query, it is forced to create the a work table in tempdb. Is that correct? And if so, how do I change this behavior? BTW, I have already run "update statistics with 100 percent" command on both tables in prod and staging.
Also, there is a huge difference between the logical reads for table1. Not sure why or what that means!?
Thanks again for everything, this has been an informative experience for me!
November 12, 2013 at 1:51 pm
mister.magoo (11/12/2013)
So, if you care to post up the two .sqlplan files from Production and Dev/Test we can see if the answer lies within...:-)
I wiiiish I could post the execution plans, it would make my life so much easier to have you guys look at the details (unpaid, as Kevin said :-D) while I drink some tea. But this financial institution is paranoid for some reason. J/K
November 12, 2013 at 2:38 pm
Are you REALLY REALLY REALLY sure the code is the same on both servers?!?! :Whistling:
I was just looking for some simple action on the column, like a mod or some such. Just to make sure the engine has to touch and do something with the column to get it more similar to what is going on when the UDF is in play.
You cannot control the memory grant for a single query to my knowledge. Sure would be nice occasionally. Oh how I pine for the days of SQL Server 6.5!! MORE KNOBS I SAY!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2013 at 3:29 pm
TheSQLGuru (11/12/2013)
Are you REALLY REALLY REALLY sure the code is the same on both servers?!?! :Whistling:I was just looking for some simple action on the column, like a mod or some such. Just to make sure the engine has to touch and do something with the column to get it more similar to what is going on when the UDF is in play.
You cannot control the memory grant for a single query to my knowledge. Sure would be nice occasionally. Oh how I pine for the days of SQL Server 6.5!! MORE KNOBS I SAY!! 😀
Yes I am sure, I copy pasted from one window to the other.
I queried "sql server uses large worktable" and the second link is from Paul White, it seems to deal with a large worktable and similar XML execution plans. I'm still reading through it to see if there is a good solution I can use. Here's the link in case you are interested:
November 12, 2013 at 4:49 pm
Here is a freebie....
Try this version of the function to see if it helps / makes a difference
create function [dbo].[CheckDigit](@vol varchar(50))
returns table
with schemabinding
as
return
select
case isnumeric(base.vol)
when 1
then
case sum(c.digit) % 11
when 0 then 1
when 1 then 0
else 11 - sum(c.digit) % 11
end
else 0
end as CheckDigit
from (select replace(@vol,'.','')) base(vol)
outer apply (
select top(datalength(vol)) M*cast(substring(base.vol,N,1) as int)
from (values(1,3),(2,7),(3,1),(4,3),(5,7),(6,1),(7,3),(8,7),(9,1),(10,3),(11,7),(12,1),(13,3),(14,7),(15,1),(16,3),(17,7),(18,1),(19,3),(20,7),(21,1),(22,3),(23,7),(24,1),(25,3),(26,7),(27,1),(28,3),(29,7),(30,1),(31,3),(32,7),(33,1),(34,3),(35,7),(36,1),(37,3),(38,7),(39,1),(40,3),(41,7),(42,1),(43,3),(44,7),(45,1),(46,3),(47,7),(48,1),(49,3),(50,7)) x(N,M)
) c(digit)
group by base.vol
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 12, 2013 at 5:18 pm
Thank you MM, I will try that first thing tomorrow morning. Very kind of you.
November 12, 2013 at 6:00 pm
I just LOVE Paul's posts!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 15, 2013 at 3:18 pm
I'm so impressed with the code Paul, amazing work!
The developer here decided to go another route and create a SP that runs in 1 minute.
Thank you all for your patience and help!
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply