I have a text field on a record but for some reason when I add the bolded WHERE statment it freezes and never loads the data. Take it out and it loads fast. Why would this cause an issue? AND wo.custrecord_dc_wo_eq_product_line_hidden = 1 It takes about 130,000 ms and not sure why that one addition does that. So my portlet times out.

Is there an unusually large number of records in the table?

@tdietrich so there are a lot of records and grouping and the query loads just fine initially. But it takes the 130,000 when you add this filter (which should make it return a lot fewer records)

Here's a suggestion... Modify the query a little, so that it becomes a subquery - and then put the filter on the results. Something like this:

SELECT * FROM ( *** YOUR QUERY HERE *** ) WHERE *custrecord_dc_wo_eq_product_line_hidden = 1 )*

I have a CTE which seems to do fine with that field as a filter, but it seems when I add the filter to a the select that utilizes the CTE it lags for like a minute

• In SuiteQL the order of the

WHERE

clauses might matter. Make sure that the most specific clause is first. In this case this condition should be added to the end of the

WHERE

clause. • Try changing the

LEFT JOIN

to

INNER JOIN

. • Ensure that you're not missing any parentheses if there's an

OR

condition somewhere • Try to find the simplest version of a query that produces the issue. • Custom records might be slower to query and filter

@Shai Coleman thanks for the advice. The query returns results in about 5s. The only change I make is adding this where clause

AND wo.custrecord_dc_wo_eq_product_line_hidden != 1

This should dramatically reduce the amount of results but causes the query to take about a minute. I moved it around in the where and same result.

Try adding the field to the SELECT instead of WHERE, and see how long it takes. If it also takes a minute, it means that NetSuite is just slow in accessing data from custom fields

Same result. As soon as I remove from the select, back to loading in 5s

Try adding a NetSuite built-in field from

wo

to the SELECT and see if it has the same effect

weird, i just added a basic field and then tried just a SUM with a number and both caused an issue. I don't know whats going on. Almost seems anything I add is going to mess it up

Maybe it's because of the query cache. The first time it's really slow, but then when you repeat the exact same query it returns in a few seconds the cached results. Try running the query above a few times (with the

AS

) and see if it gets faster on subsequent executions

Thanks for all the help. Ended up switching to a search with some work on the output to get what I needed and thats way faster than the query. It wasn't my query but its fine.