Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
2,335
questions
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 ...
