The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
30 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
votes
2answers
40 views

Is there a way to increase the speed of the following insert without having to create an unclusterd index being suggested by sql server? [on hold]

Below is a query that we are running that takes a while to run. Table 1 has about 400+ million records with 80 columns (this is a db warehouse table). The index being suggested is on the columnx with ...
-1
votes
0answers
21 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
27 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, ...
0
votes
0answers
14 views

Partioning a big table, or splitting it into seperate tables for speeding up queries (PostgresSQL 9) [on hold]

At work we are using Postgres 9, and I currently have to optimise a page, which loads very slowly (like 70 seconds). This happens mainly due to the queries executed against the database and that ...
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(...
0
votes
1answer
24 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 ...
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
3answers
66 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 ...
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
1answer
84 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
90 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
63 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 ...
2
votes
1answer
37 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 ...
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, ...
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
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(...
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
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 ...
2
votes
0answers
98 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 ...
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
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 ...
2
votes
1answer
55 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 ...
-1
votes
1answer
122 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] ...
4
votes
3answers
174 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 ...
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
23 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 ...
-1
votes
1answer
41 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 (...
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 ...
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, ...
1
vote
1answer
45 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 ...
1
vote
1answer
57 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 ...
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 ...
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 ...
1
vote
2answers
56 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 ...
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 ...
-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 ...
0
votes
0answers
31 views

Effect on concurrency of breaking queries into multiple statements

Let's say I have a paginated search query which takes a long time (when the offset is large) due to a large number of rows in the joined tables. The purpose of the query is to search through accounts, ...
0
votes
0answers
53 views

SQL Server : Memory pressure,Spiked user connections and CPU usage

I have the below sql server environment. SQL Server 2016 standard edition. 128 GB RAM 96 GB alllocated to SQL Server 8 tempdb's with 1 GB each. Have a major database with 2.5 TB size. Reporting also ...
0
votes
0answers
22 views

select * from schema.table takes longer than explain analyze select * from schema.table from pgAdmin 3 [duplicate]

I have a table which contains 300.000 rows. I am trying to select * from schema.table from pgAdmin and psql. Execution time from the pgAdmin: explain analyze select * from schema.activity; "Seq ...
0
votes
0answers
39 views

Optimizing range join (x between a and b) in MariaDB

My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly. Query EXPLAIN SELECT g.id, g.name, c.id AS child_id, c.date ...
2
votes
1answer
39 views

More efficient group by to count items in group

My query is: SELECT COUNT("EventType"."id") AS "eventCount", "EventType"."id" AS "EventType.id" FROM "events" AS "Event" INNER JOIN "event_types" AS "EventType" ON "Event"."eventTypeId" = "...
-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' ...
1
vote
1answer
21 views

PostgreSQL planner over estimates and self join query large table optimization

PostgreSQL 11.5 4 core, 32gigs 250gig SSD Not using default configuration, tuned to fit system memory and cores. Table+index size 14Gigs I'm working on a query that self joins to a table via a ...
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 ...
2
votes
1answer
53 views

Improving MySQL query performance for a large database

have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values ...
0
votes
1answer
57 views

sqlite - very slow performance of WHERE EXISTS without index on EXISTS

I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause. For example: UPDATE tbl1 SET col1 = "Y" WHERE EXISTS (SELECT ...