CD
Cloudflare Developers•2w ago
alaister

Perf difference on prepared statements

Hey, I made the original post on the forum and just want to add some additional details: I think my assumption that it wasn't caching at all was incorrect. The responses just seem to be a lot slower... For example my worker with: 
await sql`select 'hello' as "message";`
await sql`select 'hello' as "message";`
usually responds in 20-50ms whereas: 
await sql`select ${'hello'} as "message";`
await sql`select ${'hello'} as "message";`
usually responds in 200-300ms. The reason I say my assumption was incorrect is the cached percentage in my hyperdrive dashboard is now showing a high percentage, so it must be something to do with how the caching is working. I've tried with both prepare: false and prepare: true and neither seem to make a big difference to the times... Any help here would be appreciated! My app is able to cache a large percentage of the data, so I could see some serious performance gains if I can work out what's wrong.
3 Replies
AJR
AJR•2w ago
Hello! Let me take a look this morning and confirm what the differences are in actual protocol messages sent, there. Okay, so there's some pretty big driver difference in behavior there. TLDR, there's two round trips in the second version. I'm surprised to see that make such a big difference though. 2x cache hit should still be <20ms or so. I want to check whether the second version is serving one of the two round trips from origin, I'm going to do that next. The way the protocol messages break down is like so: 
await sql`select 'hello' as "message";`
message=b"P\0\0\00x78i0h8g3bs1\0select 'hello' as \"message\";\0\0\0"
message=b"D\0\0\0\x12Sx78i0h8g3bs1\0"
message=b"B\0\0\0\x18\0x78i0h8g3bs1\0\0\0\0\0\0\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"

await sql`select ${'hello'} as "message";`
message=b"P\0\0\0.qjucjjr5bo1\0select $1 as \"message\";\0\0\x01\0\0\0\0"
message=b"D\0\0\0\x11Sqjucjjr5bo1\0"
message=b"H\0\0\0\x04"

message=b"B\0\0\0 \0qjucjjr5bo1\0\0\0\0\x01\0\0\0\x05hello\0\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"
await sql`select 'hello' as "message";`
message=b"P\0\0\00x78i0h8g3bs1\0select 'hello' as \"message\";\0\0\0"
message=b"D\0\0\0\x12Sx78i0h8g3bs1\0"
message=b"B\0\0\0\x18\0x78i0h8g3bs1\0\0\0\0\0\0\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"

await sql`select ${'hello'} as "message";`
message=b"P\0\0\0.qjucjjr5bo1\0select $1 as \"message\";\0\0\x01\0\0\0\0"
message=b"D\0\0\0\x11Sqjucjjr5bo1\0"
message=b"H\0\0\0\x04"

message=b"B\0\0\0 \0qjucjjr5bo1\0\0\0\0\x01\0\0\0\x05hello\0\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"
prepare: false would disable caching in this scenario, so I'm pretty surprised to see that it didn't make a big difference Ok, I see the issue. This is a bug causing cache misses on the client node on the first round trip, the PDH there. It's still serving from cache, but only on the endpoint node, which is adding a network hop for you. That explains the outsized perf difference you're observing. I'll fix this and get it out in the next release. Thank you for bringing this to our attention.
alaister
alaisterOP•2w ago
Wow thanks for diving into this one! Glad you found the problem 🙌 Generally (even after this is fixed), would you recommend using prepared statements with hyperdrive?
AJR
AJR•2w ago
It's a bit of a mixed bag. They provide extra security by allowing the database to help you in preventing SQL injection. They do have some perf overhead, though, as they aren't really designed to be helpful for a serverless-style access pattern with lots of short-lived connections. Overall I think security is worth it, so I vote yes.

Did you find this page helpful?