# ODBC query is slow because of casting to a GUID type

**URL:** <https://discourse.julialang.org/t/odbc-query-is-slow-because-of-casting-to-a-guid-type/5383>\
**Category:** Data\
**Created:** [August 15, 2017, 10:52am UTC](https://discourse.julialang.org/t/odbc-query-is-slow-because-of-casting-to-a-guid-type/5383 "2017-08-15T10:52:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![swt30](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/swt30/32/4667_2.png) [@swt30](https://discourse.julialang.org/u/swt30)\
**Post date:** [August 15, 2017, 10:52am UTC](https://discourse.julialang.org/t/odbc-query-is-slow-because-of-casting-to-a-guid-type/5383/1 "2017-08-15T10:52:00Z")

</div>

I’m reading in some moderately-sized tables (10^3–10^6 rows) from a PostgreSQL database. Because [PostgreSQL.jl](https://github.com/JuliaDB/PostgreSQL.jl) seems to be dead on v0.6, I’ve had to resort to installing the Postgres ODBC driver and using [ODBC.jl](https://github.com/JuliaDB/ODBC.jl). I’m pretty new to this database stuff, so getting this far is certainly an accomplishment!

The problem is that queries through `ODBC.query(...)` run slowly—somewhere around an order of magnitude slower than just running the same query through the psql command line. I tried profiling the query and it looks like the bottleneck is here (can’t figure out how to get it print the full function signature, annoyingly):

```nohighlight
Count File Line Function
 2191 ...v0.6/ODBC/src/Source.jl 198 cast!(::Type{ODBC.API.SQLGUID}, ::O...

```

which appears to be where it’s converting things from a Postgres GUID type into something else.

Can I speed up this slow type conversion? I know it can be done quickly because I’m recreating an R interface to the database, and that interface casts them all to strings with very little overhead 🙂

---

<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:** [August 15, 2017, 11:50am UTC](https://discourse.julialang.org/t/odbc-query-is-slow-because-of-casting-to-a-guid-type/5383/2 "2017-08-15T11:50:33Z")

</div>

Can’t you just change your SQL query? Something like:

```julia
ODBC.query(dsn, "select guid::varchar, .... from table where ....")

```

---

<div class="post-metadata">

**Author:** ![swt30](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/swt30/32/4667_2.png) [@swt30](https://discourse.julialang.org/u/swt30)\
**Post date:** [August 22, 2017, 10:18am UTC](https://discourse.julialang.org/t/odbc-query-is-slow-because-of-casting-to-a-guid-type/5383/3 "2017-08-22T10:18:58Z")

</div>

After doing that, it looks like most of the time is still being spent in various `cast!` methods, including casting to `Float64` and `WeakRefString` (which appears to be the Julia translation of a `varchar`). Perhaps I should take a look at PostgreSQL.jl and see what’s preventing that from working on v0.6.