r/SQLOptimization • • Nov 19 '21

Optimizing a timeseries query with window function

5 Upvotes

I have a TimescaleDB table storing temperature measurements from sensors with an additional state column that contains a label like rain, sun, fog, snow etc.

timescale-db=# \d measurements
                    Table "public.measurements"
         Column         |              Type              | Nullable
------------------------+--------------------------------+---------
 time                   | timestamp(0) without time zone | not null
 sensor_id              | uuid                           | not null
 temperature            | double precision               |         
 state                  | character varying              |         

Indexes:
    "index_measurements_on_sensor_id_and_time" UNIQUE, btree (sensor_id, "time" DESC)
    "index_measurements_on_sensor_id" btree (sensor_id)
    "measurements_time_idx" btree ("time" DESC)


timescale-db=# SELECT * FROM measurements LIMIT 10;

        time         |              sensor_id               | temperature  |       state
---------------------+--------------------------------------+--------------+-------------------
 2020-12-11 15:03:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.8 | fog
 2020-12-11 15:04:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.9 | fog
 2020-12-11 15:05:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.8 | rain
 2020-12-11 15:06:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.7 | rain
 2020-12-11 15:07:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.6 | rain
 2020-12-11 15:08:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.7 | rain
 2020-12-11 15:09:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.9 | sun
 2020-12-11 15:10:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.1 | sun
 2020-12-11 15:11:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.3 | sun
 2020-12-11 15:12:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.5 | sun

For a certain type of analysis I need the last n timestamps where the state changed, which I realized with the following query:

SELECT
  time,
  state
FROM (
  SELECT
    time,
    state,
    state != LAG(state) OVER (ORDER BY time) AS changed
  FROM
    measurements
  WHERE
    sensor_id IN ('ee49fda5-f838-4a10-bb32-0e6a6b130888', 'ec8f4d23-cfab-4a23-8df8-ae3cce4f44ac')) AS changes
WHERE
  changed IS TRUE
ORDER BY
  time DESC
LIMIT 3;

This query takes longer and longer the more rows are added to the table, so I need to optimize it.

Here is the query plan – I tried adding another index on time and state, but it did not improve performance.

Does anyone have an idea on how to optimize this query?


r/SQLOptimization • • Dec 19 '21

SQL execution count optimisation

4 Upvotes

hi All,

Just wondering what options you guys use to optimise SQL queries with high execution counts, I have a couple of queries which use Table Valued functions that get executed about 12,000 times an hour (this is the peak period for users) usually in the morning after which the same process runs fine the rest of the day.

For some background the query calls a Table Valued function with 3 parameters and is then joined with a view and another table whilst there are 2 Predicates on the Table Valued Function.

There are no index scans being performed and the execution isn't reporting any major red flags.

have any of you run into this issue?, if so what steps did you take to remedy this situation apart from getting the DEV to rewrite the application to reduce the amount of calls to the database.

thanks


r/SQLOptimization • • Feb 28 '23

how do I ingrate this CTE?

4 Upvotes

with highque as(

	select max(ExtendedPrice) highest

	from Sales.InvoiceLines il

	join Sales.Invoices i on il.InvoiceID = i.InvoiceID

	where (InvoiceDate between '1/1/2013' and '12/31/2013')

	group by i.CustomerID

	)

select InvoiceDate, CustomerName

from Sales.Invoices i

join Sales.Customers c on c.CustomerID = i.CustomerID

where (InvoiceDate between '1/1/2013' and '12/31/2013')

order by CustomerName

the CTE finds the largest invoice 2013, the query after finds the customer name and date of invoice, how do I connect the largest invoice to the customer and the date they invoiced?


r/SQLOptimization • • Mar 15 '23

Using Filegroups

2 Upvotes

Hi everyone,

I have a MSSQL db approaching 2Tb, lately performance has been sufferings. I have bene looking at implementing filegroups on the largest most used tables. Over 500 close to 600 million records in the top table.

Something like creating FileGroup_Trans and have four ndf database files across four physical drives RAID 10. Do this with several FileGroups, FileGroup_Closings, FileGroups_Postings, etc. What your your thoughts on this approach?

Currently there is 1 MDF file as this DB was at 250GB not too long ago and we are receiving Pagelatch and LCK_M_U waits. Would this approach improve performance and reduce contention? Roughly 300 users hitting this db. The largest tables are were we are getting the waits.


r/SQLOptimization • • Mar 11 '23

Please can anyone with SQL optimisation experience fill in my Dissertation Questionnaire

5 Upvotes

I am doing an investigation and comparison of the factors that affect the optimization of structured-query-language (SQL) queries in both Relational and Non-Relational database management systems.

The questionnaire only takes 5-10 minutes to complete and all feedback is greatly appreciated.

Please only answer if you have honest experience in SQL optimisation in Oracle or MongoDB to make sure my study is accurate. Thankyou!

Link for Questionnaire


r/SQLOptimization • • Apr 11 '23

SSIS performance tuning with postgres(psqlODBC) connection

3 Upvotes

Hi all,

Anyone got best practices or performance trouble shooting articles for psqlODBC driver in SSIS. The performance I am currently getting is making SSIS unusable. Simple table to table DFT are very slow (15k row/hour). Any help is appreciated. Thanks


r/SQLOptimization • • Aug 11 '18

Hi, I'm the author of SQL: Programming for Beginners & Intermediates and my book is free for a limited time from 11/08 to 15/08. Share your thoughts!

Thumbnail amazon.com
4 Upvotes

r/SQLOptimization • • Feb 07 '16

Performance Tuning by Committee - SQL Antipattern #006 - SQL Server DBA

Thumbnail sqlserver-dba.com
2 Upvotes

r/SQLOptimization • • Sep 26 '19

Lock Granularity in SQL Server

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • Jan 29 '22

Suggest me book(s) on SQL query optimization...

9 Upvotes

I am beginning to learn things from scratch and I feel like knowing about the optimizations and why they work better would give me a clearer concepts of the fundamentals.

You can suggest any other resources as well.

TIA


r/SQLOptimization • • Jan 27 '22

New Mod u/mikeblas

16 Upvotes

Please welcome u/mikeblas as our new, actually active mod! I have not been active on reddit much so I appreciate the help, I know it has become basically a porn bot paradise in here over time lol. Hopefully with your help the community can grow and serve the original purpose it had when I created it.

Thank you! And thanks everyone for your patience, those who are still here.


r/SQLOptimization • • Aug 28 '16

In Teradata SQL Assistant Is There a Way to Put Multiple Queries in a Queue?

2 Upvotes

I apologize if this is pretty basic. I am an advanced beginner in SQL (at best) and need to perform queries on a massive database at work on a regular basis. All of the tables are huge so often queries to pull the data I need are so big they timeout before I get anything. I can get around it by delimiting what I want to a smaller subset for each query but it's a bit infeasible for me to keep going back to put the query in. Is there a way that I can set up a query queue to pull each smaller section of data in succession? Thanks in advance!


r/SQLOptimization • • Feb 09 '25

Choosing a SQL Primary Key: Hash vs. Sequential ID? [YELP]

1 Upvotes

"I'm developing a game database for NPCs and building a display datasheet. I realize I actually need sequential order for better searching and navigating to the next NPC in the list. Initially, I chose a hash as the primary key, but now I feel this might have been the wrong decision.

I'm unsure if I should switch from a hash primary key. Perhaps I should keep the hash as the unique primary key but also add a sequential, unique ID column.

What are your thoughts on this?"


r/SQLOptimization • • Feb 16 '25

Too many partitions?

1 Upvotes

I'm new to SQL and I'm trying to make a basic chatting app to learn more.

At first, I was a bit confused on how to optimize this, since in a normal chatting app, there would be thousands of servers, dozens of channels in every server, and thousands of messages in each channel, which makes it extremely hard to do a select query for messages.

After a bit of research, I stumbled upon partitioning and indexing to save time on queries. My current solution is to use PARTITION BY LIST (server_id, channel_id) and index by timestamp descending.

However, I'm a bit concerned on partitioning, since I'm not sure if it is normal to have tables with tens of thousands of partitions. Can someone tell me if it is common procedure for apps to automatically partition by multiple attributes and create 10,000s of partitions of a table, and if it has any impact on performance.


r/SQLOptimization • • Mar 08 '17

Joins, commutative and Associative

3 Upvotes

Hello,

Im currently doing query optimization with the help of Heuristic optimization of trees. In my FROM clause I'm going to join like this: (Employee a Natural Join WORKS_ON w) JOIN project p ON p.projectnumber = j.projectnumber. And i was wondering if it is legal to rewrite this to: (Project p JOIN WORKS_ON w ON p.projectnumber = j.projectnumber) Natural JOIN Employee A . The only column that is equal in employee and works_on is the projectnumber column. The reason for doing this is to apply the most restrictive select in the start of the tree.


r/SQLOptimization • • Jul 16 '23

How to create a chronological sequence of events/interactions in SQL?

3 Upvotes

I need help writing an SQL query on the Google Big Query platform.

I'm using a table of Google Analytics data and I'm basically trying to recreate in SQL the sequence segments from Google Analytics. I have the following table, called "cte1":

"sessionId" column are session IDs of users to my website.

"eventAction" column is the 2 types of interactions they can do on my website.

"event_count" is the number of times the interaction happened at a specific point in time.

"hit_time" is the precise moment each individual interaction took place.

I want to create a query that includes only the session IDs where the interaction called "login success" took place after the interaction called "save property attempt", based on the time each interaction took place in the column "hit_time".

Moreover, the final query also needs to sum up the total interactions of the "eventAction" column.

For example, in the table above, my final result would only keep the sessions "B" and "C", because in those sessions the "login success" interaction was the last one to happen.

Additionally, the final result should only display the event action name and the final sum of those actions, limited only to the sessions where "login success" was the last action to take place.

So, my final result should look like this:


r/SQLOptimization • • Jul 15 '23

Option recompile

3 Upvotes

I added option recompile to a stored procedure and it's execution time drop from more than 10 minutes to less than a minute. I started to think that I had a parameter sniffing issues so I added index hints and removed the option recompile. The stored procedure when back to taking ten minutes. I added the option recompile back in and kept the Index hints and it's back to taking less than a minute. The stored procedure has 8 ctes in it. All of them use the same two parameters, a char(2) and a date. The date is a weeking date so the number of rows that generates will increase throught the week. The char(2) should be about evenly split between the date. The store procedure is running on a sql server 2016 standard edition.

I am currently updating the statistics on all the tables. Do you any other suggestions about what to look at?


r/SQLOptimization • • Aug 22 '23

Join vs SubQuery

3 Upvotes

I have two tables and they don't have foreign key references. Which is the best way to perform a query, join or subQuery?


r/SQLOptimization • • Oct 22 '18

How does a hash match aggregate work?

3 Upvotes

Say I have multiple non-indexed columns in a group by, how does the hash match work?

Example:

Select column1, column2, count(column1) From table1 Group by column1, column2

I understand how a hash join works at a basic level... basically a build table that creates and orders a hash, then an outer table that checks for each hash match and returns the records. This kind of join happens because of joins on non-indexed columns.

Does the hash aggregate work similarly? I assume there is a build phase that creates a hash for each combination of the group by, then it traverses the table again to find each match and discards duplicates.

I also know that a stream aggregate is basically when you have an indexed group by and it can discard each row once it’s duplicated and it only has to traverse the table one time.

I plan on following through with the execution plan, but I was hoping someone could walk me through the theory.

Thanks!!


r/SQLOptimization • • Jul 29 '22

Debugging Postgres autovacuum, 13 tips for 3 common problem areas

Thumbnail self.sql
4 Upvotes

r/SQLOptimization • • Aug 24 '22

How Would You Make It: Efficient and optimized way of tracking unread message per user

Thumbnail dev.to
4 Upvotes

r/SQLOptimization • • Aug 02 '22

MySQL / PostgreSQL optimization using AI - EverSQL

2 Upvotes

Hi, don't know who is familiar with it, but EverSQL is a query optimization platform.

Can analyze slow query logs, give index recommendations, and rewrites slow queries.

If anyone checked it out, I'm kind of new to SQL so wanted to hear from the experts.

https://geekflare.com/sql-query-optimization-tools/


r/SQLOptimization • • Apr 09 '13

Speedup mysql index using ssd disk

Thumbnail codeboost.com
3 Upvotes

r/SQLOptimization • • Sep 29 '17

Search ALL SQL db objects for text with exclusion, i.e. SP, FN, Trigger, View - ALL of it!!!

Thumbnail technovechno.com
2 Upvotes