Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
1
vote
1answer
50 views
Measuring latency of a long-distance SQL connection
We have written a simple "client" app for a customer that connects to their SQLServer database, runs about 360 queries against a "property", and returns that data as a .Net object we call a "cProp".
...
2
votes
1answer
25 views
MySQL when using WHERE BETWEEN, query hits all partitions
This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:
`ID` int(11) NOT NULL,
`target_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
...
0
votes
0answers
22 views
Should I split my database by schema or do a table partition?
I have a PostgreSQL database whose data comes from 800 different locations and right now consists of 4 billion rows in total (all locations combined). All this data is arranged in 3 tables (approx 1....
0
votes
0answers
19 views
Advise on indexing for bulk data harvesting, Postgres
I'm experiencing performance issues when transfering/duplicating data from remote machines to a central server. Essentially I need to upload all records from client_table to a similarly structured ...
0
votes
0answers
18 views
Performance impact from redundant casting of timestamp values in Postgres queries
I am inheriting some Postgres queries where there are some redundant casting for timestamp columns such as timestamp without time zone, timestamp, etc. for columns which will be implicitly be of ...
1
vote
1answer
67 views
Best way to speed up requests using date columns
Our database is receiving the following requests:
select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0)
This is a normal request that is made by our application. On ...
0
votes
0answers
16 views
Upgrade query with LEAD simulation in mysql
I'm here for ask how to upgrade this query localized in SQL Fiddle.
There, we have column Time_Stamp with dates of two days diferrents, and value column where values they vary between 0 and 1 ...
0
votes
1answer
20 views
Postgres 11 declarative partitioning by relative time
I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but ...
0
votes
1answer
26 views
MySQL under what scenario suitable to use WHERE EXISTS
I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.
select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
from `table_2`
...
4
votes
1answer
50 views
Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?
I have the following table in Postgres 9.6:
Table "public.TagNotifications"
Column | Type | Modifiers ...
1
vote
1answer
64 views
Improve Performance on GROUP BY - large table PostgreSQL
I have a Postgres table containing the following three columns:
origin (INT, b tree index)
destination (INT, b tree index)
minutes (SMALLINT, b tree index)
The table has ~ 1.4 billion rows. Each row ...
0
votes
0answers
26 views
Query time breakdown (parsing, “sub-fetching”, regular fetching)
Is there a way to distinguish the time a query spends on each of its execution stages (widely understood)?
My questions arises from the fact that I test two methods of data retrieval:
view vs. ...
0
votes
1answer
44 views
BETWEEN query on two indexed columns exponentially takes longer on minimal changes to LIMIT
This is a minimal example of my problem, but I reproduced the problem with this structure. I have the following table:
| column name | type |
|----------------+-------------|
| id ...
-1
votes
0answers
46 views
Does table size effect query performance? [duplicate]
I have two tables named table1 and table2, having a size of 9 GB and 200 MB respectively.
table1 has 200'000+ records and table2 has 700'000+ records.
I'm using the below query for both tables, BUT ...
0
votes
1answer
33 views
Loading 400 Million Records into MySQL Hangs on “Sending Data”
I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
3
votes
0answers
76 views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
1
vote
1answer
72 views
MySQL DB performance is degrading on connecting using multiple clients
I am facing weird behavior in our MySQL DB and need your expert guidance on this.
We have a query (simple select without join, usages index as well) which is executes in 5.8 seconds However, when we ...
1
vote
1answer
48 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
22 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 ...
1
vote
1answer
41 views
Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows
We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered ...
1
vote
3answers
62 views
Trying to reduce number of table scans when querying on the same column but different values
I'm having a mental block. I'm using a sample MySQL customer database...
http://www.mysqltutorial.org/mysql-sample-database.aspx
And would like to query for any customers that bought two products (...
0
votes
0answers
25 views
Getting Hash Match in query
We noticed recently that a production SQL server was creating a downstream application performance issue. We narrowed the issue down to a specific query:
SELECT ExistingSiteFlag = ' ', Invtid =...
2
votes
1answer
46 views
Efficient predictable ordering in PostgreSQL
I have a large table of locations. I would like to efficient paginate though the table. I had previously being using an OFFSET approach however the size of the table made that approach unusable. So I ...
0
votes
1answer
17 views
MySQL 5.6 - Query Performance Issue. Searching for Slug
I've a case where I don't know how to better the performance of this query.
This is a simple query to the Geonames.org database.
The query:
SELECT name, latitude, longitude FROM geoname
WHERE
...
0
votes
2answers
28 views
No 'Copying to tmp table' state in profiling a mysql query
I have a table with 1 lakh rows
CREATE TABLE `small_table` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varc` varchar(255) DEFAULT NULL,
`txt` text,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB ...
0
votes
1answer
32 views
Query perfromance debugging
I have recently installed a new module(paypal express) in Oxid 6.0.x backend.
After installation, the backend menu point "orders", in my backend, is timing out. Cause the query needs too long. I am ...
1
vote
1answer
41 views
Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)
I want to fetch up to 100 rows based on their id. The id is the primary key of the table.
The query that I had written looks like this:
select * from table where id = any ($1);
where $1 is ...
0
votes
1answer
47 views
What and where to index, rapid increasing table postgres
I work as a software engineer with python and django. Currently I am struggling with a design choice made before my time. We have a transaction table that logs all customer activity. Due to the ...
0
votes
0answers
23 views
same query different run times
I am running MySQL db with a table that has 81000 records, about 61000 of the records have the value Reg=1 while the rest have Reg=0.
When I run
select * from myTable where Reg=1
it returns the ...
0
votes
0answers
30 views
Index creation / Query Optimization : for a group by + SUM query on mysql
I have a partitioned table with the following schema:
CREATE TABLE sales_report (
sales_report_id bigint(22) NOT NULL AUTO_INCREMENT,
report_date date DEFAULT NULL,
cust_id int(11) DEFAULT ...
0
votes
2answers
20 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
30 views
Is it better to use IN(ids) instead of many single queries by id
I am wandering what is better to do, when you have big amount of connections with big amount of queries that are being executed by application.
Is it better to execute 100 queries where x goes from 1 ...
-1
votes
1answer
26 views
Improving performance of a query update that uses md5
I need to optimize this query, or change some configuration, because execution takes too much time.
Query
with rows as (
update hotmail.clean_us_usdataclean_1 set offers_excluded=case
when
...
1
vote
0answers
22 views
PostgreSQL - Speeding up Index scan in left join
I have two tables: Order Lines, Orders.
Orders table has a Status column, which is text. It can have values like 'Draft', 'Approved', 'Cancelled' etc. I need a query to return pending order lines for ...
0
votes
1answer
39 views
Efficient way to check for number of records more than a limit from a table in SQL Server
I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is ...
10
votes
1answer
340 views
What Rules Determine When SQL Server Use a CTE as an “Optimization Fence”?
A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:
Two Important Differences Between SQL Server and PostgreSQL
The first point (“...
0
votes
1answer
41 views
Costly HASH MATCH Aggregate
I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index)
Here is the view and the plan.
execution plan
CREATE ...
0
votes
2answers
32 views
Index columns on pre-production?
If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed.
Say your app is new and you have identified columns which are good candidates for indexing (based on ...
0
votes
1answer
35 views
Query Locks Table And Takes Long Time to Run
I have a query that can take a long time to run. I tried running the same query in a test server and it runs very quick.
However, in production it seems to hang and causes blocking of other queries.
...
0
votes
1answer
14 views
Finding road segements which are not split at intersection.(Query Optimization )
I have a query in which I am trying to find the road segments which intersect but are not splitted at intersection,I have also created gist index on geom column. Here is postgerSQL srcipt
SELECT a.id ...
0
votes
1answer
53 views
SQL optimization
I am trying to optimaze a query on mysql.
Here is the table structure:
TABLE FILE:
id: int(8) primary key auto_increment
filename: varchar(1024)
The ideia is to create a navigation struture like ...
1
vote
1answer
43 views
Speeding up group by and max query
In my iot use case, I have a table which stores sensor readings. I am using the timescaledb extension.
I have the following table definition:
CREATE TABLE readings (
id BIGSERIAL ...
0
votes
0answers
25 views
This query alternates between .8 second and 100+ seconds. Why? MySQL 5.7 on Mac OS
It's about a 50-50 split. I have 1GB reserved for MySQL in my.cnf, otherwise it's stock 5.7.
SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
FROM phpbb_acl_groups a, ...
0
votes
2answers
63 views
mySQL query optimizer not using indexes
In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment ...
2
votes
2answers
136 views
Force SQL Engine to prepare Non-Null plan only for a query that use variable
I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.
Approach 1
SELECT *,
SUBSTRING(IDAndCode,CHARINDEX ('$', ...
0
votes
1answer
33 views
how to optimise this group by on sys.dm_exec_query_stats?
I would like to know the last 1008 queries run in my systems and with this intent I have put this query together:
-- get the last 1008 queries run
SELECT top 1008
execquery.last_execution_time AS [...
2
votes
1answer
66 views
Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries
Issue:
I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type.
...
0
votes
1answer
30 views
Stored Proc Logical Reads Spiking Daily At The Same Time [closed]
I have a procedure that I optimized in a dev environment. It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds. I got it down to 96k reads and 8 seconds. ...
1
vote
0answers
35 views
Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan
I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing.
I am getting this warning in an execution plan and when i checked ...
1
vote
3answers
67 views
How can I speed up this query?
Here is the definition of the relevant table:
CREATE TABLE IF NOT EXISTS pricehistory (
id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
price INTEGER NOT NULL,
region ...
