The problem Using PostgreSQL 13, I ran into a performance issue selecting the highest id from a view that joins two tables, depending on the select statement I execute. Here’s a sample setup: What I found out I’m executing two statements which result in completely different execution plans and run…
Tag: performance
Merging Rows and put values in different columns
I have the following table: and want to get to this result: An ID value in the first table occurs max 4 times: Booked + 1 = was either marked booked the first time in database OR was either marked booked the last time in database Booked + 0 = was either marked not-booked the first time in database OR
SQL – How to find if the combination of column has occured before or not?
Following example demonstrates the question id location dt 1 India 2020-01-01 2 Usa 2020-02-01 1 Usa 2020-03-01 3 China 2020-04-01 1 India 2020-05-01 2 France 2020-06-01 1 India 2020-07-01 2 Usa 2020-08-01 This table is sorted by date. I want to create another column, which would tell if the id has been to th…
Speed up joins on thousands of rows
I have two tables that look something along the lines of: And a query to get data from t1. The query runs fine and takes a few ms to get data from it, although problems start appearing when I use joins. Here’s an example of one of my queries: Which can return a few thousand rows, that might look somethi…
How to combine 4 sql queries into a single query with good performance?
I have a problem to solve. First I split this problem into parts and so I wrote four queries separately but now I need to put them together as if it were a single call to return a single result. How can I do this? 1) I select purchases according to branch and store 2) I validate if the promotional
Copy table from one database to another in multiple threads in C++
There is a huge SQL table in Postgres Database. I’d like to copy the contents of it to another different database (SQL Server) using C++. I have written a single-thread application and it works fine. I decided to use multiple threads to increase the performance of reading and writing data. Here in the c…
How could I speed up this SQL query?
I have this query: With this explain plan: https://explain.depesz.com/s/gJXC I have these indexes: Is it possible to further optimise this? There are only 30 time intervals for this table so I feel like I should be able to get it faster. Answer A main limitation here (at least if you have CPUs to spare) is th…
SELECT INTO vs WITH AS: Who is faster in the temp table approach?
I have a little experience with these two structures. Is there a performance improvement using one or another to use temp tables? I know that SELECT INTO creates a table that persists after the query and is removed after some time. There is some problems when using the same query after a few seconds (SQL Erro…
speed up the query which is taking 2 seconds on 1000000 on active records also on PostgreSQL Sql
I’m creating a dynamic query on the server side beside the parameter. However, my query is taking 2 seconds to fetch the records. I’m passing the query through active records let me share the query and Active record rails code SELECT (custom_attribute_values.attributable_id) FROM custom_attribute_…
SPROC with Outer-Apply returns too many rows
I have a complicated enough SPROC, which I modified to return a few more columns (from additional tables). What I wanted is to have this SPROC still to return the same amount of rows and the same data, but to fill in new columns (if data exists) as well, for those rows. My modification does fill new columns, …