Help to build a django-like orm in julia

General Usage Performance
1

I’m spending some time these last days studying the viability of building a Django-like orm in julia. I made some progress, but I haven’t had much luck with the performance. I’ve used the searchlight with the start point. Anyone interested in helping?

An example:

a = object("tb_fat_visita_domiciliar")
.values("co_dim_tempo__dt_registro__y_month", "co_seq_fat_visita_domiciliar", "co_fat_cidadao_pec__co_fat_cad_domiciliar")
a.filter(Qor("co_seq_fat_visita_domiciliar__isnull" => true, Q("co_dim_tempo__dt_registro__y_month__gte" => "2023-01", "co_dim_tempo__dt_registro__y_month__lte" => "2025-01")))
a.query()

┌ Info:  Query returned:
│ SELECT
│   to_char(tb_1.co_dim_tempo, 'YYYY-MM') as co_dim_tempo__dt_registro__y_month,
│   tb.co_seq_fat_visita_domiciliar,
│   tb_2.co_fat_cidadao_pec as co_fat_cidadao_pec__co_fat_cad_domiciliar
│ FROM tb_fat_visita_domiciliar as tb
│  LEFT JOIN tb_dim_tempo tb_1 ON tb.co_dim_tempo = tb_1.co_seq_dim_tempo
│  LEFT JOIN tb_fat_cidadao_pec tb_2 ON tb.co_fat_cidadao_pec = tb_2.co_seq_fat_cidadao_pec
└ WHERE (tb.co_seq_fat_visita_domiciliar IS NULL OR (to_char(tb_1.co_dim_tempo, 'YYYY-MM') >= '2023-01' AND to_char(tb_1.co_dim_tempo, 'YYYY-MM') <= '2025-01'))
  0.004621 seconds (48.53 k allocations: 1.661 MiB)

I think that 0,005 seconds is very time to build a simple query and this memory allocation is even more limiting.

The function with I’ve problem is the _build_row_join and stays in src/QueryBuilder.jl

2

You have a lot of allocations there…

People here will absolutely help make this a few orders of magnitude faster, but you need full runnable MWE to get them interested.

(like all self contained here, not in your package)

1 Like
3

In case you’re interested, there are already a couple Julia packages for programmatically creating SQL queries:

1 Like
4

Hi @CameronBieganek, in fact, those packages are cool, and this approach is used for many other languages (frameworks), but django’s approach is good to more simply query, but that uses many joins, common in backend servers.

So I decided to try something, but if I couldn’t improve performance, I’ll stop here.

5

I think an integration into Prequel prql for Julia might be helpful if you needed to use it beyond the existing duckdb extension. It might help to transition logic between languages.

2 Likes