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
0answers
11 views

MySQL - too large temporary table on disk

I have two servers with identical MySQL versions (5.5.50, pretty old), identical my.cnf, identical SHOW VARIABLES and three identical tables with identical indexes and different data. And I have a ...
-2
votes
3answers
26 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
234 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
24 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
23 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
350 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
64 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
69 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
35 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
425 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
19 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 ...
0
votes
3answers
218 views

SUM value in table with timestamp column where timestamp is between start and stop timestamps in different table

I have a situation where there are network hosts whose monthly bandwidth is summed based on a start date that varies for each host. The number of hosts is quite small (< 5000) but the history ...
2
votes
1answer
89 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
61 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
62 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
95 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
61 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
39 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(...
74
votes
4answers
23k 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
97 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
32 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
219 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
42 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
44 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
98 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 ...
8
votes
1answer
35k 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
119 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
52 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
434 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
51 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 ...
4
votes
3answers
170 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
22 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 ...
0
votes
1answer
31 views

Postgres: strange long request: A is fast, B is fast, A WHERE IN B is slow

There is a lot of rows in table "logs" (the Request A FROM). Request A is quick and Request B is quick. Put together, Request A WHERE IN Request B is very long. Is that normal? Request A is very ...
-1
votes
1answer
40 views

How to optimize a slow select query with tuples in WHERE clause

I am currently trying to optimize a select query which takes 5-10 seconds on tables with 50k rows (and will grow 100x this soon). The purpose of this query is to find which tuples from a given set of (...
2
votes
1answer
249 views

Optimize a query reducing logical reads

I have a SQL Server 2016 query that returns 127K rows. You can find the query and query plan here. Let me know if you also need tables structure. I need to join with a table that has only 20 rows, ...
0
votes
1answer
38 views

why there is no performance differences if I use where clause inside of inner join?

Basically I have two different types of query in BQ. the first one: select q2.name, q1.* , q2.val1 from table1 as q1 inner join (select name,val1, val2 from table2) as q2 on q1.name = q2.name and ...
-2
votes
1answer
63 views

How to optimize below update query in SQL Server

I have the below update query, its taking around 3 minutes to execute, I have around 1.5 million records present in "Data" table and 100000 records present in "Report" table, can someone help me in ...
1
vote
1answer
56 views

Creating a view that calculates percentages

I have this table getting populated with top10 destination ports every ten minutes along with a "total" entry that records the total number of bytes and packets transmitted for each port for the time ...
1
vote
1answer
44 views

How to use parent table column inside subquery JOIN condition?

I am writing a SQL Query for pulling records from multiple tables for sending the update or added new values email notifications to the user. In below SQL Query I am getting all records which are ...
0
votes
1answer
75 views

Same Query plan, no new query competition, same hardware, but stored procedure runs slowly

We have an SSRS report that is based on about 8 stored procedures which was running fine up until a few days ago. The report used to run in about 2 hours. Last Sunday (September 15 2019) it suddenly ...
2
votes
2answers
1k views

How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?) SELECT cars.*, users.*, makers.*, places.* ...
10
votes
2answers
24k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
0
votes
1answer
70 views

Understanding partition elimination

I am trying to understand the below query being run from our developers on read only database which just runs forever. Table here is partitioned table on partition column trickey per day. Query is ...
1
vote
2answers
54 views

join vs virtual table in query performance

Suppose I have two tables linked by a foreign-key example DDL's CREATE TABLE raw_collection ( info_datetime DATETIME NOT NULL, asset_id INTEGER NOT NULL, price FLOAT, PRIMARY KEY (...
0
votes
1answer
30 views

Is a Clustered Index Scan bad for performance?

I have a complex query (see below) which performs badly on a large database. I'm now analyzing the query by removing the joins, look at the actual plan and step by step add the joins back. I see ...
-1
votes
1answer
62 views

Simple “SELECT” query taking too long

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this: SELECT tstamp AS "time" dvalue AS "Weight" FROM tablename WHERE tstamp BETWEEN 'time1' ...