August 2, 2012 at 8:43 am
Hi,
What's is better? I prefer the third case because my table scps_cadastro_os has more than 1.000.000 and I made the change IN for EXISTS, so can I use the second case in my procedure?
case1
SELECT 1
FROM scps_cadastro_os os WITH(nolock)
WHERE conret = 'S'
AND status IN ( '01', '04' )
AND filial = 74
AND serisco = '3788329'
In this case the execution plan brings me 50% cost in 2 indexs
case2
declare @tbStatus table (
cod char(2)
)
insert into @tbStatus values ('01')
insert into @tbStatus values ('04')
SELECT 1
FROM scps_cadastro_os os WITH(nolock)
WHERE conret = 'S'
AND EXISTS (select 1 from @tbStatus t where t.cod = os.status) -- status IN ( '01', '04' )
AND filial = 74
AND serisco = '3788329'
This case the execution plan brings me 33% cost in the same 2 indexs and 33% in the variable table, but before 100% in the 2 inserts
August 2, 2012 at 9:46 am
dccbr (8/2/2012)
Hi,What's is better? I prefer the third case because my table scps_cadastro_os has more than 1.000.000 and I made the change IN for EXISTS, so can I use the second case in my procedure?
case1
SELECT 1
FROM scps_cadastro_os os WITH(nolock)
WHERE conret = 'S'
AND status IN ( '01', '04' )
AND filial = 74
AND serisco = '3788329'
In this case the execution plan brings me 50% cost in 2 indexs
case2
declare @tbStatus table (
cod char(2)
)
insert into @tbStatus values ('01')
insert into @tbStatus values ('04')
SELECT 1
FROM scps_cadastro_os os WITH(nolock)
WHERE conret = 'S'
AND EXISTS (select 1 from @tbStatus t where t.cod = os.status) -- status IN ( '01', '04' )
AND filial = 74
AND serisco = '3788329'
This case the execution plan brings me 33% cost in the same 2 indexs and 33% in the variable table, but before 100% in the 2 inserts
I am not sure the second is going to scale as well on a large dataset. The first one will have a table scan where the second is going to have two scans and a hash match. I would think the first one will out perform the second one hands down.
So the next question is why the NOLOCK hint?
_______________________________________________________________
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/
August 2, 2012 at 10:49 am
The NOLOCK it's because my Database is READ COMMITED 😀
August 2, 2012 at 11:03 am
dccbr (8/2/2012)
The NOLOCK it's because my Database is READ COMMITED 😀
So you are intentionally trying to get dirty reads? I don't quite follow that you need nolock because your transaction isolation level is read committed. You don't need nolock unless you want dirty reads. It is not a good practice to use nolock. I am guessing that based on your lack of a explanation you don't truly understand the implications.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
In short it is not a good idea to just use nolock because you think you need to. It can have serious ramifications on your database. It can even corrupt your entire database in certain situations.
_______________________________________________________________
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/
August 2, 2012 at 1:14 pm
Sean Lange (8/2/2012)
dccbr (8/2/2012)
The NOLOCK it's because my Database is READ COMMITED 😀So you are intentionally trying to get dirty reads? I don't quite follow that you need nolock because your transaction isolation level is read committed. You don't need nolock unless you want dirty reads. It is not a good practice to use nolock. I am guessing that based on your lack of a explanation you don't truly understand the implications.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
In short it is not a good idea to just use nolock because you think you need to. It can have serious ramifications on your database. It can even corrupt your entire database in certain situations.
I understand your concern, I wrote you with few explanations because I can't write in english very well.
I know about what you told me and I don't like use NOLOCK too. Sorry but we need to use NOLOCK, trust me, it's necessary here yet!
So, I need to change some procedures, I change procedures when they use like "select @existe = count(*) from table where id = @id" instead "if exists(select 1 from table where @id)"
My doubt it's about that query than I told before.
Thank's a lot.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply