This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

I’ve been working on some performance improvements for a SuiteQL query which uses date filters on a datetime field. I’m seeing some massive performance improvements based on how I pass in the date parameter. It seems to be specific to datetime column types and does not affect fields with just a date column type. I’m curious if this is a known thing and if anyone else can reproduce it. I’m not an Oracle DB expert by any means, so this might be common knowledge. I’m assuming the first two are requiring Oracle to convert the field values and the last is not.

The first 2 queries below take about 20 seconds to run. The 3rd query takes about 50ms.

```SELECT
tr.id
FROM Transaction as tr
WHERE tr.createdDate &gt;= '2023-05-20'


SELECT
tr.id
FROM Transaction as tr
WHERE tr.createdDate &gt;= sysdate-7

SELECT
tr.id
FROM Transaction as tr
WHERE tr.createdDate &gt;= TO_DATE('2023-05-20','YYYY-MM-DD')```

I suspect the first queries produce text fields, which then get converted to the underlying date value every transaction.

The last query produces a date value and therefore skips that stage.