Help Reducing a Query

[Ryan_Craig]

I am working on a project that is supposed to show data on a pie chart based on some conditions.

The information shown on the table and pie charts are filtered based on the check boxes, the drop down Machine number, drop down part number, and in between specific dates.
This is the query I am running:

The query is essentially going through every combination of inputs to tell the query what to filter by. As you can tell it is a very long query and takes awhile to run sometimes.

Is there anyway I could shorten this query up to improve run time?

[Transistor]

Aw, man, post formatted code - not pictures of code. Anyone who wants to help you would have to type it all out again or use OCR.

Wiki - how to post code on this forum

This thread is intended as a short help file for all those struggling to post formatted code on the forum. Posting formatted code on the forum (and most others) is as simple as selecting the code and pressing the </> button. It works the same as applying Bold or Italic styles to your document. It preserves indentation and applies syntax highlighting. [How to post code]

[Ryan_Craig]

IF   :ECB   = 'true' AND   :AllTimeCB    = 'true' AND   :checkBox  = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	END


IF  :ECB  = 'true' AND   :AllTimeCB  = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE PartNumber =  :partNumber 
	END


IF  :checkBox  = 'true' AND    :AllTimeCB   = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE MachineID =  :machineID
	END


IF  :checkBox  = 'true' AND  :ECB   = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE  DateTime BETWEEN  :startDate AND  :endDate 
	END
	
	
IF  :checkBox  = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE  DateTime BETWEEN  :startDate AND  :endDate 
	AND MachineID =  :machineID
	END
	
	
IF :ECB   = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE  DateTime BETWEEN  :startDate AND  :endDate 
	AND PartNumber =  :partNumber 
	END


IF   :AllTimeCB   = 'true'
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE  MachineID =  :machineID
	AND PartNumber =  :partNumber 
	END


ELSE 
	BEGIN
SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE  DateTime BETWEEN  :startDate   AND    :endDate 
	AND PartNumber =   :partNumber 
	AND MachineID =   :machineID 
	END

[DNice]

Looking at this quickly it seems you can consolidate a lot of the WHERE clauses by passing in a '%' for machineID/partNumber when the ECB or checkboxes are False and simply have:

WHERE PartNumber like :partNumber 
	AND MachineID like :machineID

For the timestamp filter you could keep the "DateTime between @startDate and @endDate" in your WHERE clause as well and set your startDate to like '1990-01-01' and endDate to CURRENT_TIMESTAMP if the :AllTimeCB is true

[Transistor]

I think you can reduce the query to,

SELECT 
	MachineID,
	PartNumber,
	JobNumber,
	DateTime,
	(case when InspectionResult = 1 then 'PASSED' when InspectionResult = -1 then 'WRONG JOB LOADED' else 'FAILED' end)InspectionResult   , 
	(case when HoleDetection = 1 then 'PASSED'  when HoleDetection = -1 then 'NOT CHECKED' else 'FAILED' end)HoleDetection ,  
	(case when SealDetection = 1 then 'PASSED' else 'FAILED' end)SealDetection ,  
	(case when WeldTrack = 1 then 'PASSED' else 'FAILED' end)WeldTrack ,  
	(case when PartFound = 1 then 'PASSED' else 'FAILED' end)PartFound 
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
	WHERE PartNumber LIKE :partNumber 
	WHERE MachineID LIKE :machineID
	WHERE DateTime BETWEEN :startDate AND :endDate 

if you pass in either partNumber or the % wildcard depending on the selected property of the checkbox..
Similarly you can do the same with machineID.

The dates are a little trickier. You could pass in the selected dates or, for all dates, pass in dates that are guaranteed to cover the period of interest. Alternatively you could create a couple of custom properties with query bindings to retrieve the first and last timestamps and pass those in.

You make the selection between dropdown and all by replacing your value bindings for the query with expression bindings.

[lrose]

Personally, I would probably move this to a script and build the query dynamically, and then call it with system.db.runPrepQuery(). Named Queries are great and should be the default solution, IMO, however, they aren't the correct solution for every situation. Let the scripting handle the logic for you, it's really good at that, and then the DB can do what it does best.

See this post for more info.

runNamedQuery vs runPrepQuery

The original purpose of Named Queries was to make it possible to pass values into Vision Query bindings in a secure and efficient way, solving the unreliability of parameter stringification (looking at you, java.util.Date), and allowing the huge "legacy DB access" security hole to be closed. The maintenance advantages of NQs can be matched by multi-line strings for SQL in library scripts, so I tend to discount that item. However, not all is well. Some things to consider: NQs permit the use…

[Ryan_Craig]

@Transistor

if you pass in either partNumber or the % wildcard depending on the selected property of the checkbox..
Similarly you can do the same with machineID.

I am not sure how I would accomplish this. Would I do this by creating an expression binding to the checkbox's Selected Property?

[Transistor]

The expression binding would be something like this.

if(
    {../Checkbox.props.selected},
	'%',                        // Return the LIKE wildcard.
	{../Dropdown.props.value}   // Return the dropdown selection.
)

[Ryan_Craig]

This seemed to work when I execute the query in the testing section of the Named Queries, but when I go back to my chart, it shows no data no matter what I click. It is also not showing any errors.

[Ryan_Craig]

It also seems like the binding doesn't actually change the value after the first try. Once I hit apply on the expression, the value will change to '%' but after checking the box on and off, the value remains the same.

[Transistor]

I don't see any expressions in the query parameter values. That's where they're supposed to go.

[Ryan_Craig]

My mistake. I changed it to be on the query parameter values, and I got this error.

Here are the four values:

if(
 {.../FlexSelect/FlexContainer_3/ENCB.props.selected},
 '%',
 {.../FlexSelect/FlexContainer_1/Dropdown_ENum.props.value}
 )

if(
 {.../FlexSelect/FlexContainer_1/PNCB.props.selected},
 '%',
 {.../FlexSelect/FlexContainer_1/Dropdown_PartSel.props.value}
 )

if(
 {.../FlexSelect/FlexContainer/ALLTCB.props.selected},
 '2020-1-01',
 {.../FlexSelect/FlexContainer/DateTimeInput_Start.props.value}
 )

 if(
  {.../FlexSelect/FlexContainer/ALLTCB.props.selected},
  CURRENT_TIMESTAMP,
 {.../FlexSelect/FlexContainer_3/DateTimeInput_End.props.value}
 )

it is saying that I have a comma that is not supposed to be there, but it looks correctly formatted to me.

[Transistor]

Expression language doesn't know what to make of CURRENT_TIMESTAMP.

Try
dateFormat(now(), "yyyy-MM-dd hh:mm:ss")

[bkarabinchak.psi]

I find it easier to do it like WHERE PartNumber LIKE CONCAT('%',:partNumber,'%'), - use only the beginning or trailing '%' depending on where you want your wildcard to be (you can have it as both). Then you feed the partNumber directly no modification in an expression needed.

[Transistor]

@bkarabinchak.psi

WHERE PartNumber LIKE CONCAT('%',:partNumber,'%')

But we're looking for an exact match when partNumber is specified.

[bkarabinchak.psi]

Then why is LIKE being used? Should be = then no?
Something like
WHERE (:partNumber='' OR partNumber=:partNumber)

[Transistor]

@bkarabinchak.psi

Then why is LIKE being used?

I was trying to come up with a way of passing in a parameter which could be either a valid partNumber or the '%' wildcard.
LIKE :partNumber without the % wildcards should return only exact matches.
If :partNumber is '%' then the SQL becomes
LIKE '%' which will return all partNumbers.

[paul-griffith]

For fun, I coached ChatGPT through rewriting the query with awareness of named query's query string and regular parameter types, and it suggested this:

SELECT 
    MachineID,
    PartNumber,
    JobNumber,
    DateTime,
    (CASE WHEN InspectionResult = 1 THEN 'PASSED' WHEN InspectionResult = -1 THEN 'WRONG JOB LOADED' ELSE 'FAILED' END) AS InspectionResult,
    (CASE WHEN HoleDetection = 1 THEN 'PASSED' WHEN HoleDetection = -1 THEN 'NOT CHECKED' ELSE 'FAILED' END) AS HoleDetection,
    (CASE WHEN SealDetection = 1 THEN 'PASSED' ELSE 'FAILED' END) AS SealDetection,
    (CASE WHEN WeldTrack = 1 THEN 'PASSED' ELSE 'FAILED' END) AS WeldTrack,
    (CASE WHEN PartFound = 1 THEN 'PASSED' ELSE 'FAILED' END) AS PartFound
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
WHERE
    ({ECB} != 'true' OR {AllTimeCB} != 'true' OR {checkBox} != 'true' OR 1=1)
    AND ({ECB} != 'true' OR {AllTimeCB} != 'true' OR PartNumber = :partNumber OR 1=1)
    AND ({checkBox} != 'true' OR {AllTimeCB} != 'true' OR MachineID = :machineID OR 1=1)
    AND ({checkBox} != 'true' OR {ECB} != 'true' OR DateTime BETWEEN :startDate AND :endDate OR 1=1)
    AND ({checkBox} != 'true' OR DateTime BETWEEN :startDate AND :endDate AND MachineID = :machineID OR 1=1)
    AND ({ECB} != 'true' OR DateTime BETWEEN :startDate AND :endDate AND PartNumber = :partNumber OR 1=1)
    AND ({AllTimeCB} != 'true' OR MachineID = :machineID AND PartNumber = :partNumber OR 1=1)
    AND (DateTime BETWEEN :startDate AND :endDate AND PartNumber = :partNumber AND MachineID = :machineID OR 1=1);

Even if it's correct, that's an absolute monster of a query (and a terrible query plan) - I'd definitely recommend moving some of the logic out to a "higher level" than trying to force everything into the query.

[bkarabinchak.psi]

I have a few queries that look like this and I will say when well indexed, the performance is not necessarily as bad as you might think (though it will need some TLC to get decent).

However, it is a absolute pain to maintain. The where's that manage multiple column selects is awful imo. Figure out what cases you have, and use a CASE statement.

[Ryan_Craig]

Everything seems to be working correctly now, except for when I search by MachineID.
When testing it, if I use '%', I get all of the results:

But after typing in a value for the MachineID, it returns Nulls:

Any ideas?