Skip to content
🔮 Sanity Create is here. Writing is reinvented. Try now, no developer setup
Get help on Slack

Query optimization for slow datetime comparisons in Sanity.io

15 replies
Last updated: Jul 10, 2020
Hey! Me and my team is having issues with slow queries towards Sanity. We discovered that when querying this one takes 3000ms
*[
   !(_id in path("drafts.**")) && (_type=='campaignLandingPage' || _type=='landingPage')
     && (!defined(launchAt) || launchAt < now())
   	&& (!defined(endAt) || endAt > now())
   ]
This one takes 20ms

*[
   !(_id in path("drafts.**")) && (_type=='campaignLandingPage' || _type=='landingPage')
   ]
Do you have any idea why the datetime comparisons are that slow? Or any suggestion how we could optimise our query?
Jul 10, 2020, 9:59 AM
it was slow for me too a few minutes ago but it passed
Jul 10, 2020, 10:00 AM
I don't think this is related to current issues. We've been having these problems for at least a week
Jul 10, 2020, 10:02 AM
Hi User, could you try wrapping 
launchAt
and 
endAt
in 
dateTime()
to check if this makes things more speedy?
*[
   !(_id in path("drafts.**")) && (_type=='campaignLandingPage' || _type=='landingPage')
     && (!defined(launchAt) || dateTime(launchAt) < now())
   	&& (!defined(endAt) || dateTime(endAt) > now())
   ]
Jul 10, 2020, 10:14 AM
Hmm, getting some new issues with dateTime for null value, but it seems to be faster 😅
Jul 10, 2020, 10:21 AM
It didn't work with the comparison to now() without also wrapping it in 
dateTime(now())
just for reference
Jul 10, 2020, 10:27 AM
Thanks, that makes sense, sorry for the confusion 🙂
Jul 10, 2020, 10:28 AM
Forgot that 
now()
returns a string. Are you seeing any better results?
Jul 10, 2020, 10:29 AM
Yeah, but I can't seem to get the query to work 100%. Still getting
No function dateTime() defined for arguments (null)

Jul 10, 2020, 10:30 AM
This clause doesn't seem to work 
(defined(launchAt) && dateTime(launchAt) < dateTime(now())))
Jul 10, 2020, 10:31 AM
Still complains about getting launchAt null 😞
Jul 10, 2020, 10:31 AM
&& (!defined(launchAt) || (defined(launchAt) && dateTime(launchAt) < dateTime(now())))
that's the clause in entirety
Jul 10, 2020, 10:31 AM
Feels like that should cover all bases, right?
Jul 10, 2020, 10:31 AM
Also tried
*[
   !(_id in path("drafts.**")) && (_type=='campaignLandingPage' || _type=='landingPage')
     && select(!defined(launchAt) => false, dateTime(launchAt) < dateTime(now()))

   ]
With the same results
Jul 10, 2020, 10:41 AM
Was able to fix this with
*[
   !(_id in path("drafts.**")) && (_type=='campaignLandingPage' || _type=='landingPage')
     && dateTime(select(!defined(launchAt) => now(), launchAt)) <= dateTime(now())
     && dateTime(select(!defined(endAt) => now(), endAt)) >= dateTime(now())
   ]
and now we have fast queries again, thanks a lot!
user M
Jul 10, 2020, 11:16 AM
Great work fixing the query itself. Appreciate you sharing the solution here, thanks User!
Jul 10, 2020, 11:58 AM

Sanity– build remarkable experiences at scale

Sanity is a modern headless CMS that treats content as data to power your digital business. Free to get started, and pay-as-you-go on all plans.

Get started for freeExplore the demo

Was this answer helpful?