# Help Reducing a Query

**URL:** <https://forum.inductiveautomation.com/t/help-reducing-a-query/81922>\
**Category:** Ignition\
**Tags:** perspective, ignition81\
**Created:** [November 15, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922 "2023-11-15T14:33:05Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/1 "2023-11-15T14:33:05Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/b/bb666521e5e972e2359c0cdc5e5794bf6cc701c5.png)  
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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/d/0de11b9ee66f75408f534745450decdc2ba9d429.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/6/96342f49d41a396a66e47d18c4992d9b53197f8b.png)  
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?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 2:45pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/2 "2023-11-15T14:45:40Z")

</div>

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](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831):
>
> 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]

---

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 2:48pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/3 "2023-11-15T14:48:19Z")

</div>

```auto
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

```

---

<div class="post-metadata">

**Author:** ![DNice](https://avatars.discourse-cdn.com/v4/letter/d/858c86/32.png) [@DNice](https://forum.inductiveautomation.com/u/DNice)\
**Post date:** [November 15, 2023, 3:11pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/4 "2023-11-15T15:11:07Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 3:13pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/5 "2023-11-15T15:13:47Z")

</div>

I think you can reduce the query to,

```auto
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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 15, 2023, 3:59pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/6 "2023-11-15T15:59:11Z")

</div>

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](https://forum.inductiveautomation.com/t/runnamedquery-vs-runprepquery/81268/6):
>
> 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…

---

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 4:13pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/7 "2023-11-15T16:13:31Z")

</div>

> [@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?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 4:20pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/8 "2023-11-15T16:20:06Z")

</div>

The expression binding would be something like this.

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

```

---

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 4:56pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/9 "2023-11-15T16:56:02Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/6/5674d4e453940a582df5c597a392f3431a5ebc60.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/9/c911dcbb3876fee6f813b8e55740708d9f9fde4c.png)

---

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 4:59pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/10 "2023-11-15T16:59:18Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/d/fdd9cfb75d0c45bc0d2f9471433ecb8b21b3ff51.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 4:59pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/11 "2023-11-15T16:59:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 5:17pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/12 "2023-11-15T17:17:17Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/5/7524c1447580313eb87933e95b9418a8e89d2918.png)

Here are the four values:

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

```

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

```

```auto

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

```

```auto
 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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 5:27pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/13 "2023-11-15T17:27:49Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [November 15, 2023, 5:32pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/14 "2023-11-15T17:32:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 5:42pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/15 "2023-11-15T17:42:21Z")

</div>

> [@bkarabinchak.psi](#):
>
> WHERE PartNumber LIKE CONCAT('%',:partNumber,'%')

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [November 15, 2023, 5:44pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/16 "2023-11-15T17:44:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2023, 5:52pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/17 "2023-11-15T17:52:45Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [November 15, 2023, 5:54pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/18 "2023-11-15T17:54:11Z")

</div>

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:

```sql
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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [November 15, 2023, 6:00pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/19 "2023-11-15T18:00:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Ryan\_Craig](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@Ryan\_Craig](https://forum.inductiveautomation.com/u/Ryan_Craig)\
**Post date:** [November 15, 2023, 6:03pm UTC](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922/20 "2023-11-15T18:03:01Z")

</div>

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:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/4/8451656b4e2e71bfd6271fef9894b4c8c1909043.png)  
But after typing in a value for the MachineID, it returns Nulls:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/e/ce14a002607a2c0a44f53abc806392f79b684cd1.png)

Any ideas?

[Next page](https://forum.inductiveautomation.com/t/help-reducing-a-query/81922.md?page=2)