April 3, 2017 at 12:20 pm
This doe snot throw errors but it does not pass any rows ( I mean it just goes on forever )
Some fine tuning will be appreciated...
I know how to write WITH stmts but not sure how to pass parameters
SELECT Claims_1.formnbr,
Claims_1.placeofservice,
Claims_1.claimtype,
Claims_1.admitdate,
ClaimsDetail_1.billedamt,
ClaimsDetail_1.netamt,
Claims_1.providernbr,
Claims_1.plancode,
Claims_1.vendornbr,
Claims.formnbr AS FormNbr2,
ClaimsDetail.billedamt AS BilledAmt2,
ClaimsDetail.netamt AS NetAmt2,
Claims.vendornbr AS VendorNbr2,
ClaimsDetail.revenuecode,
ClaimsDetail.hcpcs
FROMclaim.dclaimserviceline AS ClaimsDetail_1 WITH (nolock)
INNER JOIN claim.dclaim AS Claims_1 WITH (nolock)
ON ClaimsDetail_1.claimtid = Claims_1.claimtid
INNER JOIN claim.dclaim AS Claims WITH (nolock)
INNER JOIN claim.dclaimserviceline AS ClaimsDetail WITH (nolock)
ON Claims.claimtid = ClaimsDetail.claimtid
ON Claims_1.membernbr = Claims.membernbr
AND Claims_1.admitdate = Claims.admitdate
AND Claims_1.formnbr > Claims.formnbr
AND ClaimsDetail_1.revenuecode = ClaimsDetail.revenuecode
AND ClaimsDetail_1.hcpcs = ClaimsDetail.hcpcs
AND Claims_1.placeofservice = Claims.placeofservice
AND Claims_1.claimtype = Claims.claimtype
AND Claims_1.providernbr = Claims.providernbr
AND ClaimsDetail_1.specificdateofservice =
ClaimsDetail.specificdateofservice
WHERE ( Claims_1.placeofservice <> '21' )
AND ( ClaimsDetail_1.postingpaystatus = '1' )
AND ( ClaimsDetail.postingpaystatus = '1' )
AND ( Claims_1.claimtype = 'H' )
AND ( Claims_1.claimentrydate >= 20140701 )
AND ( Claims.claimentrydate >= 20140101 )
AND ( ClaimsDetail_1.netamt <> 0 )
AND ( ClaimsDetail.netamt <> 0 )
AND ( NOT EXISTS (SELECT Sum(netamt) AS Expr1
FROM claim.dclaimserviceline AS d
WHERE ( formnbr = ClaimsDetail.formnbr )
AND ( admitdate = ClaimsDetail.admitdate )
AND ( revenuecode = ClaimsDetail.revenuecode )
AND ( hcpcs = ClaimsDetail.hcpcs )
GROUP BY formnbr,
revenuecode,
hcpcs
HAVING ( Sum(netamt) = 0 )) )
AND ( NOT EXISTS (SELECT Sum(netamt) AS Expr1
FROM claim.dclaimserviceline AS d
WHERE ( formnbr = ClaimsDetail_1.formnbr )
AND ( admitdate = ClaimsDetail_1.admitdate )
AND ( revenuecode = ClaimsDetail_1.revenuecode )
AND ( hcpcs = ClaimsDetail_1.hcpcs )
AND ( formnbr = ClaimsDetail_1.formnbr )
GROUP BY formnbr,
revenuecode,
hcpcs
HAVING ( Sum(netamt) = 0 )) )
ORDER BY Claims_1.formnbr
April 3, 2017 at 12:25 pm
How to Post Performance Problems: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 3, 2017 at 12:27 pm
What is with all the nolock hints? Hasn't anyone told that they aren't a magic go faster button and can actually cause data issues?
April 3, 2017 at 12:45 pm
Do you want it reorganized or do you want it performance tuned? The two things are vastly different.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 3, 2017 at 4:11 pm
Made it to work...... I ran this and it does return rows.
Can you tell me whether the code below does the same as above posted ? That will be a great help !
If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;
If object_id('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
SELECT
Claims_1.formnbr,
Claims_1.placeofservice,
Claims_1.claimtype,
Claims_1.admitdate,
ClaimsDetail_1.billedamt,
ClaimsDetail_1.netamt,
Claims_1.providernbr,
Claims_1.plancode,
Claims_1.vendornbr,
Claims.formnbr AS FormNbr2,
ClaimsDetail.billedamt AS BilledAmt2,
ClaimsDetail.netamt AS NetAmt2,
Claims.vendornbr AS VendorNbr2,
ClaimsDetail.revenuecode,
ClaimsDetail.hcpcs,
ClaimsDetail.admitdate ClmDtlAdmtDt,
ClaimsDetail.formnbr ClmDtlFormNbr,
ClaimsDetail_1.revenuecode as revenuecode_1,
ClaimsDetail_1.hcpcs as hcpcs_1,
ClaimsDetail_1.admitdate as ClmDtlAdmtDt_1,
ClaimsDetail_1.formnbr as ClmDtlFormNbr_1
INTO #t
FROMclaim.dclaimserviceline AS ClaimsDetail_1 WITH (nolock)
INNER JOIN claim.dclaim AS Claims_1 WITH (nolock)
ON ClaimsDetail_1.claimtid = Claims_1.claimtid
INNER JOIN claim.dclaim AS Claims WITH (nolock)
INNER JOIN claim.dclaimserviceline AS ClaimsDetail WITH (nolock)
ON Claims.claimtid = ClaimsDetail.claimtid
ON Claims_1.membernbr = Claims.membernbr
AND Claims_1.admitdate = Claims.admitdate
AND Claims_1.formnbr > Claims.formnbr
AND ClaimsDetail_1.revenuecode = ClaimsDetail.revenuecode
AND ClaimsDetail_1.hcpcs = ClaimsDetail.hcpcs
AND Claims_1.placeofservice = Claims.placeofservice
AND Claims_1.claimtype = Claims.claimtype
AND Claims_1.providernbr = Claims.providernbr
AND ClaimsDetail_1.specificdateofservice =
ClaimsDetail.specificdateofservice
WHERE ( Claims_1.placeofservice <> '21' )
AND ( ClaimsDetail_1.postingpaystatus = '1' )
AND ( ClaimsDetail.postingpaystatus = '1' )
AND ( Claims_1.claimtype = 'H' )
AND ( Claims_1.claimentrydate >= 20140701 )
AND ( Claims.claimentrydate >= 20140101 )
AND ( ClaimsDetail_1.netamt <> 0 )
AND ( ClaimsDetail.netamt <> 0 )
Select * ,
'X' as Cond1, 'X' as Cond2
INTO #t1
FROM
#t;
PRINT 'UPDATE 1'
UPDATE T
SET Cond1 = CASE WHEN ( XYZ.Expr1 = 0 ) THEN '1' ELSE '0' END
FROM
#t1 T
CROSS APPLY
(
SELECT Sum(netamt) AS Expr1
FROM claim.dclaimserviceline AS d
WHERE ( formnbr = T.ClmDtlFormNbr )
AND ( admitdate = T.ClmDtlAdmtDt )
AND ( revenuecode = T.revenuecode )
AND ( hcpcs = T.hcpcs )
GROUP BY formnbr,
revenuecode,
hcpcs
HAVING ( Sum(netamt) = 0 )
)XYZ
PRINT 'UPDATE 2'
UPDATE T
SET Cond2 = CASE WHEN ( XYZ.Expr1 = 0 ) THEN '1' ELSE '0' END
FROM
#t1 T
CROSS APPLY
(
SELECT Sum(netamt) AS Expr1
FROM claim.dclaimserviceline AS d
WHERE ( formnbr = T.ClmDtlFormNbr_1 )
AND ( admitdate = T.ClmDtlAdmtDt_1 )
AND ( revenuecode = T.revenuecode_1 )
AND ( hcpcs = T.hcpcs_1 )
GROUP BY formnbr,
revenuecode,
hcpcs
HAVING ( Sum(netamt) = 0 )
)XYZ
PRINT 'DELETE '
DELETE FROM #t1
WHERE
Cond1 = '1' AND Cond2 = '1';
PRINT 'Select...'
SELECT
formnbr,
placeofservice,
claimtype,
admitdate,
billedamt,
netamt,
providernbr,
plancode,
vendornbr,
formnbr AS FormNbr2,
billedamtAS BilledAmt2,
netamtAS NetAmt2,
vendornbr AS VendorNbr2,
revenuecode,
hcpcs
FROM
#t1
ORDER BY formnbr
--AND ( NOT EXISTS (SELECT Sum(netamt) AS Expr1
-- FROM claim.dclaimserviceline AS d
-- WHERE ( formnbr = ClaimsDetail.formnbr )
-- AND ( admitdate = ClaimsDetail.admitdate )
-- AND ( revenuecode = ClaimsDetail.revenuecode )
-- AND ( hcpcs = ClaimsDetail.hcpcs )
-- GROUP BY formnbr,
-- revenuecode,
-- hcpcs
-- HAVING ( Sum(netamt) = 0 )) )
--AND ( NOT EXISTS (SELECT Sum(netamt) AS Expr1
-- FROM claim.dclaimserviceline AS d
-- WHERE ( formnbr = ClaimsDetail_1.formnbr )
-- AND ( admitdate = ClaimsDetail_1.admitdate )
-- AND ( revenuecode = ClaimsDetail_1.revenuecode )
-- AND ( hcpcs = ClaimsDetail_1.hcpcs )
-- AND ( formnbr = ClaimsDetail_1.formnbr )
-- GROUP BY formnbr,
-- revenuecode,
-- hcpcs
-- HAVING ( Sum(netamt) = 0 )) )
--ORDER BY Claims_1.formnbr
April 3, 2017 at 4:13 pm
Lynn Pettis - Monday, April 3, 2017 12:27 PMWhat is with all the nolock hints? Hasn't anyone told that they aren't a magic go faster button and can actually cause data issues?
Lynn: I always though the WITH ( NOLOCK) will prevent SQL server from spending time locking rows.. That means it works faster.. If not tell me why ?
April 4, 2017 at 1:55 am
mw112009 - Monday, April 3, 2017 4:13 PMLynn Pettis - Monday, April 3, 2017 12:27 PMWhat is with all the nolock hints? Hasn't anyone told that they aren't a magic go faster button and can actually cause data issues?Lynn: I always though the WITH ( NOLOCK) will prevent SQL server from spending time locking rows.. That means it works faster.. If not tell me why ?
The problem with NOLOCK is is that it may well give bad data. This could be because of dirty reads, doubles reads, phantom rows, etc. There's an article on msdn, https://msdn.microsoft.com/en-us/library/ms190805.aspx, which explains which each means.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 4, 2017 at 3:21 am
mw112009 - Monday, April 3, 2017 4:13 PMLynn Pettis - Monday, April 3, 2017 12:27 PMWhat is with all the nolock hints? Hasn't anyone told that they aren't a magic go faster button and can actually cause data issues?Lynn: I always though the WITH ( NOLOCK) will prevent SQL server from spending time locking rows.. That means it works faster.. If not tell me why ?
No. Locking time is trivial (microsecond range).
NOLOCK (or READUNCOMMITTED) tells SQL to relax its requirements for query results to be correct, in favour of allowing more concurrent writes. If you don't care whether or not the results are 100% accurate every time, that may be OK.
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
April 4, 2017 at 7:51 am
More on NOLOCK hints:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://www.queryprocessor.com/nolock-and-top-optimization/
April 4, 2017 at 8:19 am
These are for reports coming from a DATAWAREHOUSE. No body updates them. The data gets overwritten each night.
So I should be safe using WITH (NOLOCK).
Any comments appreciated ... Please feel free....
Gail: I was not sure what you meant. Does the WITH (NOLOCK) reduce query time ( I'd say yes )
April 4, 2017 at 8:28 am
The taking of locks will only add milliseconds to your query. It's waiting for locks to be released that NOLOCK helps with. It's just telling the server to ignore a whole lot of locks on the data you're accessing. Good for currency, potentially seriously bad for integrity. But you don't have any contention on your tables, so there's no reason to use NOLOCK. It won't do any harm to use it here, except that somebody, a junior developer maybe, is going to come along, see the code and copy it for use somewhere that it's not at all appropriate.
John
April 4, 2017 at 8:34 am
John: I am not understating your answer. The only part that I understood was about the JUNIOR DEVELOPER part.
I am saying NOBODY UPDATES the tables in a our DATA WAREHOUSE. So is there an advantage to using WITH (NOLOCK) ?
I personally noticed an improvement in query performance
April 4, 2017 at 8:41 am
No, there's no advantage - only the fact that it doesn't have to actually take the locks. But I'd be surprised if that difference were even measurable. But if you're getting a significant improvement from using NOLOCK, and you're sure it's attributable to that, then please leave it in. If it were my code, I would sprinkle comments throughout explaining why you've left it in, so that the aforementioned junior developer isn't tempted to copy it without giving it serious consideration.
John
April 4, 2017 at 8:43 am
mw112009 - Tuesday, April 4, 2017 8:34 AMJohn: I am not understating your answer. The only part that I understood was about the JUNIOR DEVELOPER part.I am saying NOBODY UPDATES the tables in a our DATA WAREHOUSE. So is there an advantage to using WITH (NOLOCK) ?
I personally noticed an improvement in query performance
There's an improvement that must be measured in microseconds (1 microsecond = 0.000001 second). If you can notice that, you must be amazing. If you can't, you should test it and get actual performance comparisons.
If there's concurrency, NOLOCK hint will make the queries ignore some locks and might make the response faster but probably inaccurate. If there's no concurrency, the hint won't do anything to improve the performance.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply