June 24, 2015 at 4:22 pm
Hello comunity
I build a UDF scalar function like this:
CREATE FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT
AS
BEGIN
DECLARE @CodigoPerfil INT
SELECT DISTINCT
@CodigoPerfil =
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC
RETURN @CodigoPerfil
END
Curiously when i call my function the same one return always the same value, ex:
Select VerificaAcessoPerfil(2)
the return value is : 698 ??
but if i run the Select statment like this:
SELECT DISTINCT codigo,
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)[codigo]
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC
the value are:
1695
2211
41
10698
11697
13696
Someone could help me?
Many thanks
Luis
June 24, 2015 at 4:44 pm
Sorry Guys
I solve the problem like this:
ALTER FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT
AS
BEGIN
DECLARE @CodigoPerfil INT
SELECT DISTINCT
@CodigoPerfil =
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697
WHEN 2 THEN 211
WHEN 10 THEN 698
WHEN 13 THEN 696
WHEN 4 THEN 1 else 0 END)
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (@codigo)
--ORDER BY 1 ASC
RETURN @CodigoPerfil
END
I understand that was not passed the parameter.
Many thanks
Luis
June 25, 2015 at 3:47 am
Hi Luis,
Have you looked at the possible performance improvements of converting this into an inline table valued function (iTVF)?
June 25, 2015 at 4:00 am
Couple things jump out.
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Scalar UDFs are notorious performance problems. Consider converting this to an in-line table function if you plan to use it within another select statement
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2015 at 4:03 am
Here's a start:
SET NOCOUNT ON
CREATE TABLE dbo.pf
(
codigo int not null PRIMARY KEY CLUSTERED,
pfstamp CHAR(1) not null
);
CREATE TABLE dbo.pfu
(
pfstamp CHAR(1) not null PRIMARY KEY CLUSTERED
);
INSERT INTO dbo.pf (codigo,pfstamp)
VALUES (1,'A'),
(2,'B'),
(4,'C'),
(10,''),
(11,'E'),
(13,'F');
INSERT INTO dbo.pfu (pfstamp)
VALUES ('A'),('B'),('C'),('D'),('E'),('F');
GO
CREATE FUNCTION dbo.VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT
AS
BEGIN
DECLARE @CodigoPerfil INT
SELECT DISTINCT
@CodigoPerfil =
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697
WHEN 2 THEN 211
WHEN 10 THEN 698
WHEN 13 THEN 696
WHEN 4 THEN 1 else 0 END)
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (@codigo)
--ORDER BY 1 ASC
RETURN @CodigoPerfil
END;
GO
CREATE FUNCTION dbo.VerificaAcessoPerfil2
(
@codigo INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT DISTINCT
CodigoPerfil =
case codigo WHEN 1 THEN 695
WHEN 11 THEN 697
WHEN 2 THEN 211
WHEN 10 THEN 698
WHEN 13 THEN 696
WHEN 4 THEN 1 else 0 END
FROM DBO.pf INNER JOIN dbo.pfu ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (@codigo);
GO
SET STATISTICS IO ON
DECLARE @test-2 TABLE
(
N INT NOT NULL
);
INSERT INTO @test-2(N)
SELECT n
FROM ff_winning_together.dbo.getnums(0,20);
DECLARE @timer DATETIME
SET @timer = GETDATE()
SELECTT.N,
CodigoPerfil = dbo.VerificaAcessoPerfil(T.N)
FROM@test-2 AS T
CROSSJOIN @test-2 AS T1
CROSSJOIN @test-2 AS T2
CROSSJOIN @test-2 AS T3
PRINT '#1 ' + CAST(DATEDIFF(MS,@Timer,GETDATE()) AS VARCHAR) + ' MS'
SET @timer = GETDATE()
SELECTT.N,
CA.CodigoPerfil
FROM@test-2 AS T
CROSSJOIN @test-2 AS T1
CROSSJOIN @test-2 AS T2
CROSSJOIN @test-2 AS T3
OUTER
APPLYdbo.VerificaAcessoPerfil2(T.N) AS CA;
PRINT '#2 ' + CAST(DATEDIFF(MS,@Timer,GETDATE()) AS VARCHAR) + ' MS'
SET STATISTICS IO OFF
SET NOCOUNT OFF
DROP FUNCTION dbo.VerificaAcessoPerfil;
DROP FUNCTION dbo.VerificaAcessoPerfil2;
DROP TABLE dbo.pf;
DROP TABLE dbo.pfu;
Edit - Added some cross joins to beef up the test data
June 25, 2015 at 12:21 pm
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2015 at 1:37 pm
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
They repeat it because without taking those locks, there's no guarantee that you are operating on current information, so data integrity is at risk. The only time that you should ever use that hint is when being slightly out of date isn't an issue, and you're not going to represent that data as authoritative or use it as a source for an UPDATE.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 25, 2015 at 2:20 pm
sgmunson (6/25/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
They repeat it because without taking those locks, there's no guarantee that you are operating on current information, so data integrity is at risk. The only time that you should ever use that hint is when being slightly out of date isn't an issue, and you're not going to represent that data as authoritative or use it as a source for an UPDATE.
That's not what they're claiming -- they claiming definitively that it does not make it faster ("[NOLOCK hints] don't make queries faster"). I dispute that again. It is indeed less overhead to do reads that way.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2015 at 8:06 pm
ScottPletcher (6/25/2015)
sgmunson (6/25/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
They repeat it because without taking those locks, there's no guarantee that you are operating on current information, so data integrity is at risk. The only time that you should ever use that hint is when being slightly out of date isn't an issue, and you're not going to represent that data as authoritative or use it as a source for an UPDATE.
That's not what they're claiming -- they claiming definitively that it does not make it faster ("[NOLOCK hints] don't make queries faster"). I dispute that again. It is indeed less overhead to do reads that way.
As much as I'd like to believe that (it does sound the way it should be), have you got an example that you could prove that statement with because I'm just not seeing what you claim in the following code...
--DROP TABLE #PerfTest;
GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO #PerfTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM #PerfTest;
GO
--===== This is the WITH (NOLOCK) code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM #PerfTest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
GO 5
--===== This is the un-hinted code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM #PerfTest;
SET STATISTICS TIME,IO OFF;
GO 5
I'm also not seeing the locks manifest themselves either way using sp_lock in another window against the spid.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2015 at 9:36 pm
Piling on the NOLOCK discussion, the behaviour is different between temporary tables and normal heap tables. When using #temp tables, the NOLOCK hint will not only have a shared (S) (bulk operation) lock on the HOBT but a schema stability (Sch-S) lock on the table. Without the NOLOCK there is only a shared lock (S) on the #table. On a normal heap table, the NOLOCK does the same while without it there will be a Intended Shared (IS) on the table and shared PAGE locks (S).
😎
IMHO, using NOLOCK is as clever as ignoring traffic lights.
June 26, 2015 at 2:10 pm
Jeff Moden (6/25/2015)
ScottPletcher (6/25/2015)
sgmunson (6/25/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
They repeat it because without taking those locks, there's no guarantee that you are operating on current information, so data integrity is at risk. The only time that you should ever use that hint is when being slightly out of date isn't an issue, and you're not going to represent that data as authoritative or use it as a source for an UPDATE.
That's not what they're claiming -- they claiming definitively that it does not make it faster ("[NOLOCK hints] don't make queries faster"). I dispute that again. It is indeed less overhead to do reads that way.
As much as I'd like to believe that (it does sound the way it should be), have you got an example that you could prove that statement with because I'm just not seeing what you claim in the following code...
--DROP TABLE #PerfTest;
GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO #PerfTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM #PerfTest;
GO
--===== This is the WITH (NOLOCK) code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM #PerfTest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
GO 5
--===== This is the un-hinted code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM #PerfTest;
SET STATISTICS TIME,IO OFF;
GO 5
I'm also not seeing the locks manifest themselves either way using sp_lock in another window against the spid.
I wouldn't expect SQL to lock private temporary tables the same way it does permanent tables, given they are single-user tables only.
No, I haven't tried to prove that SELECTs takes shared locks whereas (NOLOCK) does not. I trust MS's documentation on that, given how long it's been that way. And I've seen enough deadlocks with SELECTs involved to know that some locking must be occurring with them ;-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 26, 2015 at 2:48 pm
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that?
Scott you have been around sql long enough to know the point Gail was making here. It is often used as a magic "go faster" button by people who do not even pretend to understand the nuances of what it does. I am one of those people who shudder when I see that hint and make frequent comments about not using it. I typically tell people it is not a mechanism just to make queries run faster. It has some very huge issues that very few people understand. As with everything it has its place, but that place should not be on every single query. I can't begin to count the number of times people's response to why they use it is "because the technical lead says to use it on every query to make them faster".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2015 at 4:22 pm
Sean Lange (6/26/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that?
Scott you have been around sql long enough to know the point Gail was making here. It is often used as a magic "go faster" button by people who do not even pretend to understand the nuances of what it does. I am one of those people who shudder when I see that hint and make frequent comments about not using it. I typically tell people it is not a mechanism just to make queries run faster. It has some very huge issues that very few people understand. As with everything it has its place, but that place should not be on every single query. I can't begin to count the number of times people's response to why they use it is "because the technical lead says to use it on every query to make them faster".
All very true. But one shouldn't say "NOLOCK does not make queries faster", as people repeatedly do here, because that's just, sadly, factually false. We must instead stress the invalid, and missed, data that NOLOCK can show.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 26, 2015 at 5:30 pm
ScottPletcher (6/26/2015)
Jeff Moden (6/25/2015)
ScottPletcher (6/25/2015)
sgmunson (6/25/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
They repeat it because without taking those locks, there's no guarantee that you are operating on current information, so data integrity is at risk. The only time that you should ever use that hint is when being slightly out of date isn't an issue, and you're not going to represent that data as authoritative or use it as a source for an UPDATE.
That's not what they're claiming -- they claiming definitively that it does not make it faster ("[NOLOCK hints] don't make queries faster"). I dispute that again. It is indeed less overhead to do reads that way.
As much as I'd like to believe that (it does sound the way it should be), have you got an example that you could prove that statement with because I'm just not seeing what you claim in the following code...
--DROP TABLE #PerfTest;
GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO #PerfTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM #PerfTest;
GO
--===== This is the WITH (NOLOCK) code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM #PerfTest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
GO 5
--===== This is the un-hinted code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM #PerfTest;
SET STATISTICS TIME,IO OFF;
GO 5
I'm also not seeing the locks manifest themselves either way using sp_lock in another window against the spid.
I wouldn't expect SQL to lock private temporary tables the same way it does permanent tables, given they are single-user tables only.
No, I haven't tried to prove that SELECTs takes shared locks whereas (NOLOCK) does not. I trust MS's documentation on that, given how long it's been that way. And I've seen enough deadlocks with SELECTs involved to know that some locking must be occurring with them ;-).
Ok... Understood and I'll give you that. But, the following experiment with a real table still seems to disprove your point. According to this this test, WITH (NOLOCK) provides no boost in performance. Do you have a demonstrable test that proves otherwise? And no... not doing battle with you. You made an interesting claim and I'd like to see it be right but I'm just not seeing that.
--DROP TABLE dbo.JBMTest;
GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO dbo.JBMTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM dbo.JBMTest;
GO
PRINT REPLICATE('=',100);
GO
--===== This is the WITH (NOLOCK) code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = [name]
FROM dbo.JBMTest WITH (NOLOCK);
SET STATISTICS TIME OFF;
GO 5
PRINT REPLICATE('=',100);
GO
--===== This is the un-hinted code
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = [name]
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
GO 5
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2015 at 5:34 pm
ScottPletcher (6/26/2015)
Sean Lange (6/26/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that?
Scott you have been around sql long enough to know the point Gail was making here. It is often used as a magic "go faster" button by people who do not even pretend to understand the nuances of what it does. I am one of those people who shudder when I see that hint and make frequent comments about not using it. I typically tell people it is not a mechanism just to make queries run faster. It has some very huge issues that very few people understand. As with everything it has its place, but that place should not be on every single query. I can't begin to count the number of times people's response to why they use it is "because the technical lead says to use it on every query to make them faster".
All very true. But one shouldn't say "NOLOCK does not make queries faster", as people repeatedly do here, because that's just, sadly, factually false. We must instead stress the invalid, and missed, data that NOLOCK can show.
Until you can actually provide code that proves otherwise, I'll also have say that "NOLOCK does not make queries faster". The only thing that I've seen that "makes them faster" is when blocking occurs. Because of dirty reads, it doesn't wait on most blocking. By itself, though, I've not seen any proof from you or anyone else that WITH(NOLOCK) causes queries to run faster when blocking is not present.
Again... not ganging up on you here. It's an interesting claim that I'd like to see be true but I'm not taking anyone's word, best guess, or text only article (even if it's from MS) on the subject. Show us some code that proves it, please.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply