May 17, 2021 at 1:28 am
I work on sql server I need to rewrite statement after where statement so how to rewrite
I don't understand what is writing after where
so can you help me how to write it alternative
SELECT top 1 *
FROM extractreports.dbo.TblTemp T with(nolock) JOIN extractreports.dbo.Tbl_Temp TT with(nolock) ON T.GlobalPnID=TT.GlobalPnID
join (Select Max(GPNP.GroupID) GroupID,GPNP.GlobalPnID from dbo.GlobalPartNumberPortions GPNP with(nolock) group by GPNP.GlobalPnID ) K on k.GroupID=t.GroupID And K.GlobalPnID =T.GlobalPnID
join extractreports.[dbo].[PCDataConfiguration] p with(nolock) on p.partnumber=t.
Left JOIN extractreports.dbo.Tbl_TempCount tc with(nolock) ON t.GlobalPnID =Tc.GlobalPnID
LEFT JOIN dbo.GlobalPartNumberPortionException gpnE WITH(NOLOCK) ON t.GlobalPnID =gpne.GlobalPnId
WHERE
TT.Counts >=TT.RealCount AND (tc.GlobalPnID IS NULL or
(tc.Counts =0 or
(tc.Counts >0 AND t.[Key] LIKE CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange ELSE gpnE.ExceptionSignature end
AND 1= CASE WHEN gpnE.HasRange =0 THEN 1 ELSE /*******/ CASE WHEN gpnE.Exception LIKE N'%~%' THEN [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue) ELSE 1 END /*******/END
))
)
what i need it rewrite statement as below :
WHERE
TT.Counts >=TT.RealCount AND (tc.GlobalPnID IS NULL or
(tc.Counts =0 or
(tc.Counts >0 AND t.[Key] LIKE CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange ELSE gpnE.ExceptionSignature end
AND 1= CASE WHEN gpnE.HasRange =0 THEN 1 ELSE /*******/ CASE WHEN gpnE.Exception LIKE N'%~%' THEN [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue) ELSE 1 END /*******/END
))
)
really i don't understand what after where condition so can you help me to understand what written after where
condition
or
rewrite it with another syntax or logic ?
this actually i need to rewrite it
WHERE
TT.Counts >=TT.RealCount AND (tc.GlobalPnID IS NULL or
(tc.Counts =0 or
(tc.Counts >0 AND t.[Key] LIKE CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange ELSE gpnE.ExceptionSignature end
AND 1= CASE WHEN gpnE.HasRange =0 THEN 1 ELSE /*******/ CASE WHEN gpnE.Exception LIKE N'%~%' THEN [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue) ELSE 1 END /*******/END
))
)
only i need to rewrite statement after where to be more readable and high performance
May 17, 2021 at 2:23 am
Did you not see Steve Collins' answer?
What's the point of reposting the question verbatim in as different thread?
May 17, 2021 at 5:55 am
thank you for reply
but this different code sample
so how to rewrite after where condition
May 17, 2021 at 3:56 pm
I'd probably start by writing it in a manner that is easier to see and read (my preference, not required). So something like:
WHERE
TT.Counts >=TT.RealCount AND
(tc.GlobalPnID IS NULL or
(tc.Counts =0 or
(tc.Counts >0 AND
t.[Key] LIKE
CASE WHEN gpnE.HasRange =1
THEN gpnE.ExceptionRange
ELSE gpnE.ExceptionSignature
END
AND 1=
CASE WHEN gpnE.HasRange =0
THEN 1
ELSE
CASE WHEN gpnE.Exception LIKE N'%~%'
THEN [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue)
ELSE 1
END
END
)
)
)
So, it comes out longer (more lines), but to me this is easier to read, change, and debug.
Next, looking at the FROM and JOIN portion, that is a lot of NOLOCKs. Are those required? There is risks involved with using nolock which is why I ask. In general, I try to avoid query hints except when necessary and there are cases where they make sense. But in general, if I am putting NOLOCK onto every table, it probably means I am doing something wrong. It MAY be required in your case, but I'd look at removing them if possible.
Next, what have you tried?
As for "better performance" that is INCREDIBLY tricky for us to do as we have no idea what that function does, nor do we know anything about the table structure. Adding an index onto the table MAY get you a "good enough" performance boost for example. You MAY get a performance boost by changing your first CASE statement into AND/OR's. What I mean is instead of "A LIKE CASE WHEN B=C THEN D ELSE E" to something like "((B=C AND A LIKE D) OR (B<>C AND A LIKE E))".
I would play with the WHERE clause and determine which is the "slowest" operation in there. I suspect it is the function, but it could be one of the other comparisons. Step one in performance tuning a query is to determine what is actually slow. If it is the function, you may need to do something with that. If it is one of the other comparisons, you may get benefit from a CROSS JOIN (like in the other reply) OR you may get a benefit by adding an index on a table.
Now, on the other hand, the performance bottleneck MIGHT not even be SQL Server. What I mean is you are doing a SELECT *, so you are grabbing all of the data from a lot of tables. If that is pulling down 10 GB of data on a 10 Mbps connection, it is going to be slow. If you want it to be a bit faster, pulling only the data you care about will help a little bit.
Since it is a SELECT statement, I would run that on a test system and do some performance tests on it. How fast does it run as is? How fast is it if you rewrite the CASE statements? How large is your resulting dataset?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply