Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [query-performance]

Use this tag for questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
0
votes
1answer
43 views

Computed Columns - SQL Server Function

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that ...
3
votes
3answers
253 views

Query slow when using function in the WHERE clause

This is fast (49ms): v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf); select cd_cliente into v_cd_cliente from central.cliente where nr_cpf_cnpj = v_cpf_numerico; This is slow (15 seconds): ...
1
vote
2answers
110 views

Querying system health data becomes slow as event volume increases

We get the data from system health XE from SQL server and load into table for later querying. Part of that statements which gets pretty slow is INSERT INTO TABLE1 ( [object_name] ...
-1
votes
1answer
353 views

MySQL/MariaDB: Comparing performance between two queries

I am trying to compare the performance between two queries which give otherwise the same results. The idea is that I want to compare using Windowing Functions available to newer versions to faking ...
0
votes
1answer
199 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
2
votes
1answer
89 views

SQL server slow query

I have the following query that is taking a long time to execute (nearly 20 seconds). SELECT Count(*) FROM "workorder" "wo" LEFT JOIN "workorder_fields" "wof" ON "wo"."...
0
votes
1answer
72 views

Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right ...
1
vote
1answer
23 views

LOAD DATA keeps CPU usage high after it finishes

I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this: mysql.RegisterLocalFile(fileName) _, err = dbConnection.Exec(...
5
votes
3answers
1k views

looking for missing indexes in the cached execution plans

I have the following query that shows me what is cached in sql server: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS ...
-1
votes
0answers
22 views

Indexing strategy for optional predicates

Let me ask a similar question i asked before. I believe there is more scope of explanation on this one. CREATE TABLE [dbo].[SalesOrder]( [Id] [uniqueidentifier] NOT NULL, [PageId] [...
1
vote
2answers
52 views

How to get the oldest values over each id in a PostgreSQL table efficiently?

How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements? Let me explain the situation with a sample table: CREATE TABLE sensor_data( sensor_id INTEGER, ...
1
vote
3answers
2k views

MySQL: fetch multiple global status and system variable in a single query

When we execute SHOW global status; or SHOW variables; we get list of 291 and 278 records in the resultset. For performance perspective, only few of them are much important. I have to fetch these ...
3
votes
3answers
538 views

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

SELECT something FROM table WHERE primary_key = ? vs. SELECT something FROM table WHERE primary_key = ? AND other_key = ? Say that this is a scenario where the inclusion of other_key does NOT ...
0
votes
3answers
271 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
1answer
303 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
1
vote
2answers
151 views

How to find resource intensive SQL operations in Postgresql? High CPU usage spike

I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period. ...
-2
votes
3answers
29 views

Finding ids where all the IDs are smaller than some date

Considering a simple table with id-datetime field. I want to remove all records, where datetime is smaller than some constant, but keep ids which have at least one record with bigger date. My first ...
2
votes
2answers
248 views

SQL Query optimization - Long running query

I have a database with the following tables : CREATE TABLE [Document]( [IdDocument] [int] NOT NULL, [DocumentType] [int] NOT NULL CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)...
0
votes
1answer
27 views

Fetch correct stats from database

I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have ...
1
vote
1answer
30 views

Best Indexes For Group By + Join + Where Condition

I have a database that includes the tables shown below: CREATE TABLE metadata ( keyword_id bigint NOT NULL, campaign_id bigint NOT NULL, org_id bigint NOT NULL, keyword character ...
0
votes
2answers
26 views

Optimize complicated sql query by either changing query or indexing

I'm not great with SQL and I started working at a place and they have a query that is running slow. This SQL query is taking about 4 seconds to load and is coming from a table with about 160,000 rows....
0
votes
3answers
377 views

MySQL Connections slows down my website

I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database. I ran these queries show status like 'Connections'; +-----...
0
votes
3answers
68 views

Query takes too much time to execute

I add the Query Plan of my query. And this takes too much time to execute and give data. https://explain.depesz.com/s/4JtZ I want to reduce the execution time of this query. So require to optimize ...
0
votes
1answer
87 views

Optimize Large Query Performance

I have this query that I can't seem to speed up at all. My running theory is that I need to fix some indexes or something. I am running the database through the Azure and that does auto indexes, so I ...
2
votes
1answer
39 views

Improving the query standards and performance run time

For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i ...
1
vote
1answer
452 views

Query taking more than 5 minutes to execute

I've a query that runs in 2 identical environments: Production and QA. In QA server, it takes around 6 seconds and in production it takes 5 minutes or more! Query is as follows: mysql> SELECT ...
1
vote
1answer
22 views

How To Fix High DTU Usage on Specific Query

I have a query that runs every few seconds on a Microsoft SQL Azure Database. It is from a web job that checks if there are actions that need to be done based on rows in a specific table (its a ...
2
votes
1answer
95 views

How to rewrite CASE expression to short-circuit evaluation

I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase? Demo data: CREATE TABLE #...
0
votes
1answer
69 views

Worse query performance even after improving the query by making it to run in parallel mode

I have a SQL Query which gets generated from application using EFF. It uses a view and this view has joins to multiple tables in there. The query runs slow and when i looked at the execution plan,i ...
0
votes
0answers
63 views

Explicit conversion to avoid the implicit conversion warning [duplicate]

I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below. WITH X AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus, ...
0
votes
1answer
111 views

mysql speed multiple OR WHERE or multiple single queries, what is going to be fastest

I have an application at the minutes that loops through a multidimensional array and builds a query from it, currently the query looks like this, SELECT id FROM TABLE WHERE col_1 IN ("1,2,3") AND ...
2
votes
1answer
62 views

Understanding when to remove Order By or Sort operator from Query/plan

While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way. For below DB test schema-- ...
0
votes
2answers
40 views

Improve performance by combining two quieries

I have following submission table. A submission is an image. create table submission ( md5 varchar(255) not null constraint submission_pkey primary key, user_name varchar(...
76
votes
4answers
24k views

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...
2
votes
0answers
99 views

How can i tune query without creating index

I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not ...
1
vote
0answers
33 views

MySQL query optimization for a slow stored procedure

Edit: I am using MySQL 5.7.22 I have a stored procedure, when it is running it will have up to 1 millions rows examined. Which cause the 99% cpu usage. I want to optimize the query but I have no ...
1
vote
1answer
223 views

Intermittently Slow Query @ MySQL

Problem statement In a nutshell, the problem is that a query which has been smoothly running for months has started behaving weird. Particularly, the exact same query runs fast most of the times, but ...
0
votes
0answers
43 views

Comparing Performance impact between 2 Scalar UDF's

I have my developer team who uses the data from Change data capture from SQL Server to extract data and load to a different database system. To convert the LSN format within CDC they are using the ...
1
vote
1answer
46 views

Speed up 180M+ rows returned by simple SELECT with PostgreSQL

I have performance issues on a SELECT statement which returns 180+ out of 220+ of millions of rows on my managed PostgreSQL. The table structure is the following: CREATE TABLE members ( id ...
0
votes
1answer
114 views

Fastest way to check if record is related to a particular user in postgres

While implementing a sharelikes table I added a procedure to add a new record for a like. I wanted to add a check before insert that the item being liked does not belong to the users who submitted ...
9
votes
1answer
36k views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
-1
votes
1answer
130 views

Query performance question

Using Demo from here to reproduce my issue with changes in table structure as below and modifying demo partition function to datetime CREATE TABLE [dbo].[DemoPartitionedTable]( [DemoID] [int] ...
2
votes
1answer
57 views

Slow Postgres Aggregate Query

I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical ...
0
votes
0answers
23 views

Trying to identify if the query is long running or long waiting

I have a sql query which has multiple joins and uses a view which has multiple joins.I captured the query in sql profiler and i see few information in there. I am trying to understand whether the ...
2
votes
1answer
467 views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
1
vote
0answers
55 views

Designing a table with precalculated team values for fast read queries

My version of PostgreSQL is 10.7. As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a ...
5
votes
3answers
186 views

Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
2
votes
2answers
4k views

How to get rid of using temporary when using DISTINCT in MySQL?

When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary? http://sqlfiddle.com/#!8/f61a2/3/0 CREATE TABLE `test` ( `a` varchar(45) NOT NULL, `...
0
votes
2answers
23 views

SQL Server effectiveness of mirroring partial database

Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only ...
0
votes
0answers
28 views

Sqlite Join with 1 million records

I have 3 tables: Device: Id, Name. Machine: Id, P1, P2, date_time, IdDevice. Parameters: Id, T, V, I, date_time, IdDevice. In device there isn't a lot of records. But in the other way, inside ...