# Why is there a \`sleep\` in ODBC.jl

**URL:** <https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562>\
**Category:** Performance\
**Created:** [January 26, 2023, 9:12am UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562 "2023-01-26T09:12:05Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 26, 2023, 9:12am UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/1 "2023-01-26T09:12:05Z")

</div>

Hi all 🙂

Just reposting this here  
[# By removing the sleep - query executes much faster #360](https://github.com/JuliaDatabases/ODBC.jl/issues/360)

> <https://github.com/JuliaDatabases/ODBC.jl/blob/4e0545de10b9d6f43a5955c873180870b3551836/src/API.jl#L66-L70>

Does anyone know why there is an sleep?  
Does something horrible happen if I remove it?

Cheers

---

<div class="post-metadata">

**Author:** ![Sukera](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@Sukera](https://discourse.julialang.org/u/Sukera)\
**Post date:** [January 26, 2023, 10:35am UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/2 "2023-01-26T10:35:24Z")

</div>

The `sleep` is there because the `ccall` right before that may return, even though the query is still executing. Some very fast queries may be bottlenecked by that `sleep`, while for other longer running queries that `sleep` is essential for correctness. Note that this is happening in a loop, which is only broken out of once the query truly is already done.

---

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 26, 2023, 12:39pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/3 "2023-01-26T12:39:18Z")

</div>

For some reason I’m quite bottlenecked by this sleep.

- Why can the `ccall` return, even though the query is still executing
- Does something horrible happen if I remove it though?

---

<div class="post-metadata">

**Author:** ![Sukera](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@Sukera](https://discourse.julialang.org/u/Sukera)\
**Post date:** [January 26, 2023, 1:35pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/4 "2023-01-26T13:35:39Z")

</div>

> [@Impressium](#):
>
> Why can the `ccall` return, even though the query is still executing

I do not know - it’s just the design of the C-library being called, presumably in an attempt to allow the calling code to do some other useful work in a multitasking environment, while the database crunches on the query.

> [@Impressium](#):
>
> Does something horrible happen if I remove it though?

Well, your program would no longer be correct, as the wrapper is built with the assumption that the function with the `ccall` inside only returns once the query is actually completed.

---

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 26, 2023, 2:26pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/5 "2023-01-26T14:26:35Z")

</div>

> [@Sukera](#):
>
> Well, your program would no longer be correct, as the wrapper is built with the assumption that the function with the `ccall` inside only returns once the query is actually completed.

But this is not guaranteed - `ccall` could take longer than `0.001` seconds. Should there be an way to wait for the `ccall` to `finish`?

---

<div class="post-metadata">

**Author:** ![mbauman](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mbauman/32/31082_2.png) [@mbauman](https://discourse.julialang.org/u/mbauman)\
**Post date:** [January 26, 2023, 2:36pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/6 "2023-01-26T14:36:49Z")

</div>

> [@Sukera](#):
>
> Well, your program would no longer be correct, as the wrapper is built with the assumption that the function with the `ccall` inside only returns once the query is actually completed.

I don’t know the ODBC libs, but I don’t think it would affect correctness. It’ll just burn lots of CPU time repeatedly asking “you done yet?” without actually doing any meaningful work, blocking other threads/tasks/programs on your same machine from doing other things and potentially making the SQL query itself slower.

---

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 26, 2023, 2:40pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/7 "2023-01-26T14:40:50Z")

</div>

In that case souldn’t there be a way to disable the `sleep`?  
(By deving and disabling the sleep I go from 10min to 3sec)

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 26, 2023, 3:00pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/8 "2023-01-26T15:00:36Z")

</div>

A good method would be to start small and exponentially increase delay, until some limit or even perhaps fail then. Currently this loop might not exit.

Definitely recommends a code change.

```julia
            while true
                ret = ccall( ($func, "odbc32"), stdcall, SQLRETURN, $args, $(vals...))
                ret == SQL_STILL_EXECUTING || break
                sleep(0.001)
            end

```

[https://github.com/JuliaDatabases/ODBC.jl/blob/4e0545de10b9d6f43a5955c873180870b3551836/src/API.jl#L66-L70](https://github.com/JuliaDatabases/ODBC.jl/blob/4e0545de10b9d6f43a5955c873180870b3551836/src/API.jl#L66-L70)

---

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 30, 2023, 11:09am UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/9 "2023-01-30T11:09:00Z")

</div>

> <https://github.com/JuliaDatabases/ODBC.jl/pull/361>
>
> \#360

This could be a fix. What do you think?

---

<div class="post-metadata">

**Author:** ![quinnj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/quinnj/32/11_2.png) [@quinnj](https://discourse.julialang.org/u/quinnj)\
**Post date:** [January 30, 2023, 3:46pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/10 "2023-01-30T15:46:13Z")

</div>

Hi, sorry for the slow responses here; I’m the primary author of ODBC.jl.

> (By deving and disabling the sleep I go from 10min to 3sec)

This doesn’t make sense to me. The code in question is:

```julia
while true
    ret = ccall( ($func, "odbc32"), stdcall, SQLRETURN, $args, $(vals...))
    ret == SQL_STILL_EXECUTING || break
    sleep(0.001)
end

```

i.e. we perform the ODBC ccall, then _only call `sleep`_ if the return code from the ccall is `SQL_STILL_EXECUTING`, which from the spec, has this to say:

> When the application executes a function with a statement or connection that is enabled for asynchronous processing, the driver performs a minimal amount of processing (such as checking arguments for errors), hands processing to the data source, and returns control to the application with the SQL\_STILL\_EXECUTING return code. The application then performs other tasks. To determine when the asynchronous function has finished, the application polls the driver at regular intervals by calling the function with the same arguments as it originally used. If the function is still executing, it returns SQL\_STILL\_EXECUTING; if it has finished executing, it returns the code it would have returned had it executed synchronously, such as SQL\_SUCCESS, SQL\_ERROR, or SQL\_NEED\_DATA.

> Whether a function executes synchronously or asynchronously is driver specific. For example, suppose the result set metadata is cached in the driver. In this case, it takes very little time to execute SQLDescribeCol and the driver should simply execute the function rather than artificially delay execution. On the other hand, if the driver needs to retrieve the metadata from the data source, it should return control to the application while it is doing this. Therefore, the application must be able to handle a return code other than SQL\_STILL\_EXECUTING when it first executes a function asynchronously.

There’s perhaps something fishy going on w/ the specific driver you’re using and it not doing something correctly perhaps wrt asynchronous execution? But if you’re disabling the sleep, it doesn’t seem possible that you can guarantee things are valid, according to spec. It also seems suspicious that the timing is going from 10min to 3sec by disabling a 1millisecond sleep? Something doesn’t seem to add up in all this.

---

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 30, 2023, 4:48pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/11 "2023-01-30T16:48:59Z")

</div>

With the sleep

- 1331.935151 seconds (3.17 M allocations: 530.445 MiB, 0.01% gc time, 0.07% compilation time)  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/5/a/5a30e7a7c1acca7d84c132f33447fc4aa45cb1fa.png)

Without

- 59.397179 seconds (371.87 k allocations: 399.236 MiB, 0.39% gc time)  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/4/b/4b62bf769285678377ae3b093761449f93a9e822.png)

---

<div class="post-metadata">

**Author:** ![mbauman](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mbauman/32/31082_2.png) [@mbauman](https://discourse.julialang.org/u/mbauman)\
**Post date:** [January 30, 2023, 8:24pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/12 "2023-01-30T20:24:31Z")

</div>

I would guess this is a corner-case where the SQL execution takes on the order of 10µs to complete (but is asynchronous) and is called 100,000 times?

---

<div class="post-metadata">

**Author:** ![Impressium](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/impressium/32/19575_2.png) [@Impressium](https://discourse.julialang.org/u/Impressium)\
**Post date:** [January 30, 2023, 8:53pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/13 "2023-01-30T20:53:45Z")

</div>

I tested it for a couple of queries and all of them are slower and sleep. I used the same snowflake odbc driver in python with the pyodbc PKG and it didn’t had these problems

---

<div class="post-metadata">

**Author:** ![kristoffer.carlsson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kristoffer.carlsson/32/22_2.png) [@kristoffer.carlsson](https://discourse.julialang.org/u/kristoffer.carlsson)\
**Post date:** [January 30, 2023, 9:11pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/14 "2023-01-30T21:11:45Z")

</div>

Reminds me of [ramp up the sleep time when connecting to the socket by KristofferC · Pull Request #169 · sciapp/gr · GitHub](https://github.com/sciapp/gr/pull/169). Doing some ramp up of that sleep time might work well.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 30, 2023, 9:26pm UTC](https://discourse.julialang.org/t/why-is-there-a-sleep-in-odbc-jl/93562/15 "2023-01-30T21:26:10Z")

</div>

The logic in

> [@kristoffer.carlsson](#):
>
> [ramp up the sleep time when connecting to the socket by KristofferC · Pull Request #169 · sciapp/gr · GitHub](https://github.com/sciapp/gr/pull/169).

is pretty much what is needed. Note that the logic there supports failing after a maximum number of retries, which should apply here too. Exponential growth with a 2x factor (or some hard-coded `const` factor) sounds reasonable.

Final note, there is a bunch of repeated code as the PR from @Impressium shows. Perhaps it is an opportunity to refactor the retry loop and avoid the almost identical 3 times repeated code.