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
-2
votes
1answer
49 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
0
votes
0answers
65 views

Reproduce Deadlock in SQL Server

I have identified a deadlock by using Solarwinds, I have noticed that it is happening in a specific table, that table only has a primary key, which tells me that the deadlock makes sense since every ...
2
votes
2answers
152 views

Usage of NON Clustered Index in SQL Server

I have 2 Index: CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA] ( [Column1] ASC, [Column2] ASC ) CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA] ( [Column3] asc ) Now I have 2 ...
2
votes
1answer
91 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
0
votes
1answer
15 views

Slow Query not using index in one of the tables

My Rails table structure is like this: a User has_many Contacts, which have one Reservation each, which have many ReservationNotes each. I'm trying to get all the ReservationNotes for the contacts ...
1
vote
1answer
19 views

How to skip prefix column check in multi column index in MySQL?

Below is my demo table structure: CREATE TABLE `table1` ( `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent', `postLocation` smallint(6) NOT NULL, `postArea` smallint(6) ...
6
votes
0answers
94 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
1
vote
1answer
105 views

Slow join performance SQL Server

I am struggling a bit to improve performance on one of my queries. What I had is basically all of those 4 tables for reporting without any indexes, PKs, etc. I was thinking that if I copy them (...
0
votes
0answers
105 views

SQL Server Query Timeout with NOT IN clause but IN works fine

So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server. Works fine on Test and Dev and if we backup and restore the production DB to either of ...
1
vote
0answers
27 views

Read performance of SQL Server data files data from Azure BLOB

I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here. The interesting ...
0
votes
1answer
18 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
0
votes
1answer
29 views

Proc runinng slow once in a while [duplicate]

I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min. For past couple of weeks it happens once in ...
1
vote
1answer
118 views

Insert statement performance in SQL Server [duplicate]

I have the below Insert statement query as part of an stored procedure that is taking some time to be complete (20-30) minutes, when normally in another server with same hardware specifications takes ...
0
votes
0answers
36 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
1
vote
1answer
28 views

Predict how much hard drive space a query might cost?

Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My ...
0
votes
0answers
19 views

How does the MongoDB $filter (aggregation) stage work internally?

I was asked a question by a colleague today whether the MongoDB $filter aggregation stage is implemented efficiently. The aggregation stage in question: $project: { absences: { $filter: {...
0
votes
0answers
23 views

How can i remove filesort for this subqueires?

This is the Query: SELECT A.* FROM messages_thread_user mtu, LATERAL (SELECT m.*, u.userName FROM messages m JOIN ...
1
vote
1answer
43 views

MySQL order of execution

I am running a very long query described below. It fetches the next needed action for each account on an automation system. I.e. SELECT Account.id, (IFNULL(**Should send message query**, ...
1
vote
1answer
29 views

Why does performing a query on a second environment, it takes forever to run, and scans hundreds of millions of rows?

I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run. I ...
2
votes
2answers
44 views

How to find exact string from a sentence

I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
0
votes
1answer
52 views

Why the difference in query time for these two queries?

I have a MySQL 5.7 database with a large table that has about 70,000,000 rows. There are 4 columns: Id, Time, Value, Monitor_Id. Id is the primary key, Time is indexed and Monitor_Id is a foreign key. ...
0
votes
1answer
60 views

Is IF ELSE better in this stored procedure than a Parameter? [duplicate]

I found a stored procedure in our sql server 2016 db Create PROCEDURE XXX @complicatedQuery bit AS BEGIN SELECT some fields FROM Table A join Table B on something JOIN Giant Table C ...
0
votes
1answer
54 views

Part of parallel execution plan has serial portion

I have a big linq to sql query which runs in parallel mode. It is returning results in 8 seconds and it is pretty good. I noticed in the execution plan that the extreme right part(from where execution ...
0
votes
0answers
31 views

How can I obtain a list of our applications' SQL statements that do not make use of indexes?

We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement ...
1
vote
2answers
42 views

Why is this query sometimes slow, and what can I do to make it faster?

I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database. ...
3
votes
1answer
3k views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ,...
3
votes
2answers
4k views

Amazon Redshift - The difference between Query Slots, Concurrency and Queues?

We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and ...
1
vote
1answer
74 views

Why is parallelism not good in this query?

I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias: Finding Key Lookups inside the Plan Cache and from there I borrowed the ...
0
votes
1answer
49 views

`cube` query performance issues with ordering

I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the But ...
16
votes
3answers
5k views

Wrapping query in IF EXISTS makes it very slow

I have the below query : select databasename from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt' and not exists(select 1 from dbo.smalltable c where c.source=l.source) ...
0
votes
1answer
33 views

How to increase timing performace of a SQL query

I'm using postgresql 9.6 on the 16GB Ubuntu-PC with 4 cores & SSD with following params: max_connections = 200 shared_buffers = 6GB work_mem = 256MB ...
2
votes
3answers
1k views

Clustered index scan in query with non clustered index filter

I have an Operations table with a nonclustered index on my foreign key to a Organizations table; however, when trying to run the following query, SQL Server does a clustered index scan instead of ...
1
vote
1answer
218 views

Slow SQL query from app, fast from SQL Studio

I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS. Here is the query : SELECT T0.pk FROM products T0 WHERE (( ( T0.pk IN (...
0
votes
1answer
33 views

Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query: SELECT * FROM category_member ORDER BY (p::float8/q) DESC LIMIT 10; I am ordering ...
1
vote
1answer
54 views

Sargability question - order of variables

I have a stored procedure that has a few lines: AND ( @StartDate IS NULL OR @StartDate <= r.ReferralDate ) AND ( @EndDate IS NULL OR @EndDate >= r.ReferralDate ) Should it be re-written as: ...
0
votes
2answers
252 views

Speed up slow query which uses ifnull, if, sub-query, join and order in MySQL

I have one SP which generates a final query (like the one below) which is taking too much time to execute. Basically it has the following parts: IFNULL to check that if value is null then send ...
2
votes
1answer
935 views

Mongo queries are slow and yielding/waiting for locks thousand times

We are using mongo setup with replica sets on AWS. Setup Details: 1 Primary node: r3.8xlarge 4 secondary nodes: r5.xlarge Mongo version: 3.0.8 (WiredTiger) Database size: 358GB We have configured ...
0
votes
1answer
51 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
2
votes
2answers
108 views

Simple query returning < 10000 rows taking 3-4 seconds

I'm having some trouble diagnosing this very simple query which seems quite slow to me, any help would be much appreciated. The table of relevance for the query at issue is defined as follows: ...
0
votes
1answer
30 views

Three WHERE slow query down

I have a table adjacency, which stores information about connected lines and their angle to each other is build by CREATE TABLE public.adjacency ( currentedge integer, currentgeom geometry(...
0
votes
0answers
18 views

MySQL view extremely slow despite indexes

I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading. The view is: https://pastebin.com/ZgB7rr91 channel_data_60 table contains about 1,200,000 records: ...
0
votes
1answer
28 views

Query to filter a table based on a comparison applied to the same table?

Case & Problem: Consider a corporate building with some rooms and departments that need authorization to enter. Employees have to scan their badges to be authorized. If the employee is inside (...
1
vote
1answer
81 views

How to improve simple update statement causing blocking issues, inneficient index scan in query plan

Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, ...
3
votes
2answers
125 views

Index Seek scanning whole table dependent on parameter value

I have a query: SELECT Id, ColumnA, ColumnB FROM MyTable WHERE ColumnA = @varA OR ColumnB = @varB The table is defined as CREATE TABLE MyTable ( Id INT IDENTITY(...
0
votes
2answers
142 views

same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)

I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
0
votes
1answer
49 views

SELECT optimization in Postgres 10

I want to select last unique rows based on time, so: SELECT DISTINCT ON (track) * FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
2
votes
2answers
42 views

Postgres query very slow when using index

So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records. Here's the query ...
1
vote
1answer
69 views

How to make delete duplicates faster?

On a mysql table with about 1.7M rows, I tried to delete duplicates posts: delete a FROM comment a INNER JOIN comment a2 WHERE a.id < a2.id AND a.body = a2.body; The result was: ...
3
votes
1answer
224 views

Can low memory cause Table Spool operators in plan [duplicate]

I was trying to optimize a query and found that the plan had table spool(eager) in it. But when I ran it on another server which had much more memory(256GB compared to 64GB) than the other one the ...
3
votes
1answer
69 views

Reused Nested Decision Logic - CTE vs Copying Code

Question I have a query which outputs one column, created through a series of CASE statements. That same column is used as part of a CASE logic for a 2nd column in the same SELECT statement. If I ...