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
1
vote
2answers
87 views

The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

I have two tables with these structures: Table1(Date ,Code ,Name) Table2(Date ,Code) I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table....
5
votes
1answer
2k views

Why does adding ROWNUM to a query improve performance?

I have two queries: 1) This query has a ROWNUM column (takes 20 seconds to execute): SELECT ROWNUM ,ROAD_ID ,VERTEX_INDEX ,SDE.ST_X(ST_POINT) AS X ,SDE.ST_Y(ST_POINT) AS Y ...
3
votes
1answer
864 views

Database slows down at millions of rows, how to optimize schema and queries?

I am building a database to store Ethereum Transactions that are connected to specific addresses. Once the database table eth_addresses_tx starts hitting 3 million + rows, things start slowing down a ...
0
votes
2answers
243 views

How to improve performance of frequently updated table in mysql?

my table structure:: CREATE TABLE `gps_device_data`( `imei_no` bigint(15) NOT NULL DEFAULT '0', `ioelementpriority` int(1) DEFAULT NULL, `longitude` int(15) DEFAULT NULL, `latitude` int(15) ...
-1
votes
2answers
790 views

BLOB or VARBINARY to store simple Strings that are converted into byte[ ]?

I've read that In MySQL, BLOBs are often used to store image files, file path pointers, video & audio files, and any other big data objects. But can BLOBs be used for simple Strings that are ...
0
votes
0answers
33 views

Why query mysql 8.0 response too slow with respect to mysql 5.5

I have the following query which in mysql 5.5, original server runs in approximately 3 seconds but all the opposite with respect to mysql 8.0, which runs in about 3 minutes, a big difference!!!. This ...
5
votes
3answers
32k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
-1
votes
2answers
161 views

Is it possible to optimize this query? Or any recommendations to speed it up?

After looking at my mysql slow log file I see that almost every entry there is related to this query: SELECT n.nid AS nid, AVG(v.value) AS rating FROM node n LEFT OUTER JOIN votingapi_vote ...
0
votes
1answer
113 views

MySQL JOIN query takes too much time to get the result

I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 ...
0
votes
0answers
76 views

Operator used tempdb to spill data, spill level 1

Here I have a query which is spilling to tempdb. I understand that the cardinality estimates are wrong and causing this spill. I've read few articles that mentions updating the statistics and ...
1
vote
0answers
49 views

SQL Prepared statement reuse in plan cache

I'm working on minimising our plan cache usage, and I've read a lot around Adhoc queries and how we can parameterize and reuse rather than having many single use plans. I'm not seeing as much ...
0
votes
1answer
207 views

how to judge sql performance through these two explain?

If I give you 2 explain results from MySQL,how do you know which query is better? the first sql explain *************************** 1. row *************************** id: 1 select_type: ...
13
votes
1answer
540 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
0
votes
1answer
44 views

Computed Columns - SQL Server Function

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that ...
3
votes
3answers
259 views

Query slow when using function in the WHERE clause

This is fast (49ms): v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf); select cd_cliente into v_cd_cliente from central.cliente where nr_cpf_cnpj = v_cpf_numerico; This is slow (15 seconds): ...
1
vote
2answers
111 views

Querying system health data becomes slow as event volume increases

We get the data from system health XE from SQL server and load into table for later querying. Part of that statements which gets pretty slow is INSERT INTO TABLE1 ( [object_name] ...
-1
votes
1answer
373 views

MySQL/MariaDB: Comparing performance between two queries

I am trying to compare the performance between two queries which give otherwise the same results. The idea is that I want to compare using Windowing Functions available to newer versions to faking ...
0
votes
1answer
201 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
2
votes
1answer
90 views

SQL server slow query

I have the following query that is taking a long time to execute (nearly 20 seconds). SELECT Count(*) FROM "workorder" "wo" LEFT JOIN "workorder_fields" "wof" ON "wo"."...
0
votes
1answer
74 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
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(...
5
votes
3answers
1k views

looking for missing indexes in the cached execution plans

I have the following query that shows me what is cached in sql server: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS ...
1
vote
2answers
59 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, ...
1
vote
3answers
2k views

MySQL: fetch multiple global status and system variable in a single query

When we execute SHOW global status; or SHOW variables; we get list of 291 and 278 records in the resultset. For performance perspective, only few of them are much important. I have to fetch these ...
3
votes
3answers
576 views

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

SELECT something FROM table WHERE primary_key = ? vs. SELECT something FROM table WHERE primary_key = ? AND other_key = ? Say that this is a scenario where the inclusion of other_key does NOT ...
0
votes
3answers
292 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
1answer
342 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
-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 ...
2
votes
2answers
260 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
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
2answers
26 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
390 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
69 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
88 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
40 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
477 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
32 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 ...
2
votes
1answer
97 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
71 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
63 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
121 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
63 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(...
76
votes
4answers
25k 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
99 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
37 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
227 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
44 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
47 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 ...