Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

How to improve Query when a Table has 2 partitions.1 partition has 99% data & the other 1%

Recently i was asked the following Question in 1 interview . They have 1 table with 1 TB of data for 10 years . They partitioned the table i.e. only latest week current data in 1 partition . Rest in ...
0
votes
1answer
18 views

Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution

We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the ...
1
vote
1answer
16 views

How to optimise query fetching most recent report for each entry in another table?

I am attempting to optimise a query that displays the last reported movement for a set railroad locomotives. The query currently takes 8-12s to run for a common query to generate a report on a webpage,...
1
vote
2answers
54 views

Disable AUTO_UPDATE_STATISTICS

We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the ...
2
votes
2answers
198 views

Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?

I have the following table: CREATE TABLE [dbo].[MP_Notification_Audit]( [id] [bigint] IDENTITY(1,1) NOT NULL, [type] [int] NOT NULL, [source_user_id] [bigint] NOT NULL, [target_user_id]...
3
votes
2answers
238 views

Why doesn't SQL Server use my index in this SELECT … WHERE?

I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]): CREATE TABLE [dbo].[...
2
votes
3answers
104 views

Slow Parallel SQL Server query, almost instant in serial

I have a SQL Server query as follows (obfuscated): UPDATE [TABLE1] SET [COLUMN1] = CAST('N' AS CHAR(1)) FROM [TABLE1] WHERE (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR ...
2
votes
0answers
18 views

MariaDB - Query to find unused indexes

I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
0
votes
1answer
31 views

Query Optimization with INTERSECT [closed]

The point is to transfer data between 2 databases. This query is checking if anything is changed since last time. We use this to do things only when there is a change, so we will not import the same ...
1
vote
2answers
71 views

Speed up Key Lookup (clustered)

I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%. Is there anyway I can speed this query up? Execution plan: https://www....
0
votes
0answers
30 views

Storing every 5 seconds data in MySQL MariaDB

I have a system to store and display the MQTT Messages that came from IoT to Server. The data comes in every 5 seconds and there are multiple data types in a single message. Therefore, I have thought ...
0
votes
0answers
72 views

Slow query SQL Server 2016

I have a query that is taking more than 7 hours and doesn’t finish, as it doesn’t finish I can’t generate the actual execution plan. Here's brentozar's Paste The Plan It has a temporary table, and ...
1
vote
0answers
41 views

Postgres Ignores Index When Ordering By Column From Joined Table

I am trying to figure out why Postgres is not using index when querying with ORDER BY clause with a SELECT query and 2 simple LEFT JOIN statements. I will explain my relevant database structure first: ...
1
vote
2answers
38 views

Best practice for “recycling” rows on PostgreSQL (UPDATE vs INSERT + DELETE)

I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected. My question is centered on a somewhat niche use case. Let's say you ...
1
vote
2answers
59 views

Should I use a temp table or join

I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so ...
2
votes
1answer
80 views

Slow query SQL Server

I have a query that is taking too long to run. I executed set statistics io on and set statistics time on, and I noticed that Scan count and logical reads are very high Table 'TABLE_01'. Scan count ...
0
votes
1answer
27 views

Why is an unindexed range operator (<@) faster than using BETWEEN with an index?

NB This is the same setup as this question, where here I'm asking specifically about something I was specifically not asking about over there. I've got a table with a column utc timestamptz, with a &...
0
votes
1answer
31 views

Monitoring Query which executes thousand of run/minute and is generally fast

I am looking for some advise here on one of our SQL database server with below behavior- Queries running for this databases are generally considered good with avg run time of 20 ms. Suddenly on some ...
1
vote
1answer
17 views

InfluxDB: selecting last() values from different tags

I have a bucket with the raw data of several sensors, things like temperature, air pressure and etc. The sensors don't send their data at the same time, which means that a given timestamp might have ...
1
vote
2answers
14 views

MySQL: Updating large data set including time aggregation

I am having a large MySQL (5.7) table with millions of rows (contains data for each second). Based on these values calculations should be performed, in some cases for large data ranges. Therefore, I ...
0
votes
3answers
29 views

Performance of selecting strings: substring IN vs regular expression

I have a large table with 1 billion+ records. These are the important columns: CREATE TABLE BigTable ( id BIGSERIAL PRIMARY KEY NOT NULL, hexid VARCHAR(255), -- usually 24 or 48 characters ...
0
votes
1answer
67 views

Cannot fetch a row from OLE DB provider “SQLNCLI11” for linked server

I have two SQL Servers on two different machines somewhere in the cloud (maybe Azure). One is Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 16:43:40 Copyright (c) ...
0
votes
0answers
83 views

Improving the run time of a DW query

I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes. This is not a store proc , he is doing it manually from his machine to get data and later after ...
1
vote
2answers
50 views

Time based sampling

Is there any way to do sampling on time series data in PostgreSQL (v11) based on a fixed time interval? For example, we are storing 1 record per second for 24 hours in a given table. This means ...
1
vote
1answer
32 views

information_schema.columns slow performance

we have system that quite a lot and often asks database about: SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, ...
-1
votes
2answers
55 views

Mysql group by and count queries are slow

I have searched for a long time (over 35 days) before write here. I optimised my mysql database as much as I can but unfortunately in configuration part I could not go further. Website is a classified ...
0
votes
2answers
39 views

Optimize performance for query with lots of OR's

I have the following query select Id from Table1 where column1 = @inputparameter1 or column2 = @inputparameter1 or column3 = @inputparameter1 or column4 = @inputparameter1 or column5 = @...
0
votes
1answer
16 views

Get nested procedure and sql execution oracle

In my Oracle 11g environment, I got performance issue with a procedure named : myProc(). In myProc() it's called to others proc named myChildProcA(), myChildProcB(),myChildProcC()......( ~10 child ...
-1
votes
0answers
45 views

How to optimize MySQL InnoDB engine [closed]

I have 2 million rows of time series data in one table let's say items. I am trying to generate an analysis dashboard, but the read query is very slow. btw I also did indexing for my table. I am using ...
2
votes
2answers
170 views

MySQL - Can I have quick queries in a table with 200 million rows?

THE PROBLEM I have following trivial SELECT statement: select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500; The query is pretty simple (no join statements, no ...
0
votes
1answer
18 views

Optimizing regex based query in sqlite

I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 ...
1
vote
0answers
27 views

Postgres row estimate is very low for hash join

I have a query that keeps thinking that the result of a hash join will be 1 and I don't know which knob to turn to fix it. The query takes a form field values in a revision of a document and maps them ...
1
vote
2answers
47 views

Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows

There is a table with TEXT or NTEXT data type column and I need to improve the performance of queries with WHERE clause filtering out NULL or NOT NULL rows in this column. Unfortunately, I cannot ...
1
vote
0answers
36 views

How to optimize MySQL VIEW performance?

All my years of development an this is my first post, so I hope I get this right. I hoping someone can help, my SQL skill is sufficient enough to get me by most of the time, but I'm having poor ...
0
votes
1answer
40 views

How to avoid constantly querying a server [closed]

I want to compare 2 table each on their own server. I want to avoid constantly querying between the 2 server. Would something like a temp table work well in this case? Do I need to use linked ...
1
vote
3answers
93 views

Very Simple SELECT Takes Too Long (300k rows, 60s)

Scenario I have a table that holds aggregated data to support a PowerBI report. The table is only 8 columns, and uses INT, VARCHAR and DATETIMEOFFSET types. There's a regular PK int identity column. ...
0
votes
1answer
19 views

Speed-up lookup of a row with prefix pattern

Say I have these rows in one UNIQUE column: 202101XXXXX 202101XXXXX 202102XXXXX 202102XXXXX 202103XXXXX 202103XXXXX The first 6 digits are year and month (YYYYMM), and the XXXXX part are random ...
0
votes
1answer
27 views

Multicolumn GIN index not fully facilitated by query planner

I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage. After defining an index on two columns (a,b) of a large table and executing a query ...
1
vote
1answer
40 views

What is the best way to find rows where a datetime is included in the range of two datetime columns [duplicate]

I'm using SQL Server 2016 Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) Jul 31 2020 18:47:07 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows ...
1
vote
1answer
21 views

Execution plan favorize joins over sort

recently I ran into an issue on our production server (Pg 9.6) and even though I found solution for my problem I would like to understand better the reason behind it. For easier explanation I am using ...
1
vote
2answers
117 views

Help with tuning this UPDATE Query or possible re-write

I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write: Declare @status_D INT, Declare @StatusIP INT, Declare @Status_F INT,...
-4
votes
1answer
37 views

Query Optimization for Subquery

Please help to optimize below MySQL query SELECT * FROM ( SELECT u.name,u.avatar,u.occupation, (SELECT GROUP_CONCAT(revenue)FROM LOGS WHERE user_id = u.id AND TYPE ='conversion') AS ...
0
votes
1answer
38 views

Slow query / wrong query plan for SORT BY LIMIT LEFT JOIN

I have a following 3 tables a,b,c all with compound unique key (application_id, tid) however all those tables are not related with foreign key (it's a limitation of our solution). All tables has ...
2
votes
4answers
763 views

Get first and last record of a selection without using min() max()

My energy-meter is writing the (accumulated) consumtion every minute to a db. In order to get my todays consumption I use: SELECT (MAX(energy_kwh) - MIN(energy_kwh)) AS kwh_today FROM logging....
1
vote
1answer
63 views

PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table

I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done. I have a table: create table test(action_date date, item_num int, <...
0
votes
0answers
22 views

MySQL compare multiple values in WHERE clause, find subset

I have a table segments with column comp_id, segment_id. comp_id: 1 segment_id: 23,11,223,3331; comp_id: 2 segment_id: 231,11,223,3331; I have another table: comps with segment_id as well. comp_id: 1 ...
0
votes
0answers
5 views

MongoDB numerous slow query logs with zero keysExamined

using a MongoDB version 3.6 Replica Set, I am wondering why a query that resulted in no key examination and document examination would result in being slow. For example, I have numerous slow query ...
1
vote
1answer
47 views

Decrease Insert and Update performance on bigger database

Have two partitioned tables (32 partitions / 16 files / 8 file groups). First table with 56 columns and Second table with 5 columns. One of requirment of application is to test procedures for future ...
0
votes
1answer
42 views

Does ' /*+parallel(<Specific Table >,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

I have three tables with below structure: create table customer_info (customer_num number, customer_firstName varchar2(100), customer_lastName varchar2(100), branch_code varchar2(...
3
votes
1answer
50 views

Ordering CONNECT BY siblings

I have an hierarchical query that uses CONNECT BY: select wonum, parent, classification, classstructureid, division, worktype, status, glaccount, fircode, ...

1
2 3 4 5
49