Questions tagged [performance]
An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
1,003
questions with no upvoted or accepted answers
2
votes
1
answer
676
views
MariaDB: Querying a timestamp partition
I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
- 121
2
votes
0
answers
324
views
Update queries randomly slow on INNODB Mysql
I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine.
The issue is that, ...
2
votes
1
answer
83
views
Tuning MySQL query from explain
I am working on a search query in MySQL. I am getting this from explain:
Original screenshot
These are my indexes:
Table definition:
Original screenshot
Query:
SELECT
`t`.`id` AS `t0_c0`,
...
- 181
2
votes
0
answers
333
views
MySQL memory is keep on increasing , any reason?
On one of our production host MySQL memory is keep on increasing day-by-day.
I tried reboot of MySQL instance but still no avail.
System has 16Gb of RAM.
TOP output
top - 13:05:13 up 35 days, 1:...
- 3,046
2
votes
0
answers
114
views
Transaction log bloat when writing to table on replicated database with parameterized filter
We are observing abnormally large log writes whenever we update a table that is used for parameterized filter in a merge replication. There are a number of join filters as well.
When executing a ...
- 166
2
votes
1
answer
369
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
- 141
2
votes
0
answers
3k
views
Foreign table not using index
I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this
...
- 125
2
votes
0
answers
1k
views
Tuning a Postgres INSERT only database
I am designing a database which will have many inserts and selects, but no updates.
Are there any considerations to take (in order to have high query performance) while knowing no tuple updates will ...
- 811
2
votes
0
answers
392
views
Improving performance of a weighted search query in SQLite3
For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight:
CREATE TABLE search_index (
id INTEGER(7) REFERENCES other_table,
...
- 121
2
votes
0
answers
905
views
Why is this MySQL query "using where" instead of "using index"?
Could you help me with this query, please?
SELECT p.patid, MAX(c1.eventdate) as eventdate
from patient as p
left join op_adv_effects._clinical as c1 on p.patid = c1.patid
left join op_adv_effects....
- 21
2
votes
0
answers
774
views
PostgreSQL DB freezes with large inserts
I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am ...
- 121
2
votes
0
answers
49
views
What is a scaleable database design for querying items and only returning those a particular user has not already seen?
I'm looking at database architectures that will allow the storage of a list of items a user has already seen so that I could subsequently query the items table and only show the user items they had ...
- 21
2
votes
0
answers
533
views
Performance Implications of Explicit Collation Conversions across Linked Servers
I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have ...
- 75
2
votes
0
answers
519
views
Is it possible to trace queries that never complete with SQL Server 2008 R2?
Running SQL Server 2008 R2. We have some queries that typically take less than one second to complete, but, occasionally, they are taking longer than 30 seconds which causes a client timeout.
...
- 113
2
votes
0
answers
1k
views
Case Statements, subqueries, and datediff
First time posting on this website so please let me know if you require any other information. This first section of code worked perfectly and resulted in correct information.
[Field 1] = (CASE
...
- 21
2
votes
0
answers
200
views
SQL - Optimize table population with dynamic queries
I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
- 21
2
votes
1
answer
3k
views
Postgresql: Partition table by hour
I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 ...
- 123
2
votes
1
answer
534
views
Slow ranking values in MySQL
I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers.
I'm supposed to display well over 70 different rankings ...
- 121
2
votes
0
answers
399
views
How to optimize inefficient query in PostGIS
I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow).
This query takes about ...
- 121
2
votes
0
answers
140
views
How to query against legacy data that needs to be cleaned up?
I have an existing SQL Server DB with 3 tables (note: this is a made up example, but modelled after a real problem).
Artists : contains music artists
MusicRecords : contains information on music ...
- 121
2
votes
0
answers
5k
views
Multiple user roles with different attributes
I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
- 121
2
votes
0
answers
59
views
How to identify whether a log backup or job executing a SP is causing High Page Reads/sec
For one of my servers( SQL server 2008R2) we can continuously witness High Page Reads/sec during business hours say: from 7 AM to 9 PM EST.
I kept the counter settings to monitor the same and found ...
- 7,129
2
votes
0
answers
999
views
Excessive CPU Usage caused by long running query?
We have been getting intermittent excess CPU usage at 100%. There is not a whole amount running on the system.
We also get warning of Batch Requests/sec above threshold.: 1030, which would seem to ...
- 1,394
2
votes
0
answers
4k
views
UPDATE/DELETE performance on partitioned table
Hi I'm using table partitioning in my Postgres 9.3.
I've notice that the DELETE and UPDATE queries are much slower when I'm doing them via the parent table.
I see that the constraint_exclusion is ...
- 215
2
votes
0
answers
1k
views
SQL Server spending a lot of time creating sp_dbmmonitorupdate
I have an SQL Server 2008 R2 Standard with around 80 databases, 18 of which are a synchronous mirror primary.
I'm having a lot of complaints about database speed lately, so I've started digging with ...
- 561
2
votes
0
answers
943
views
Multiple databases with medium tables or Single database with huge tables
I've developed a shop management software using Postgresql and JAVA. I would like to know which of the solutions below is the best one for my specific situation:
Only one database with huge tables (...
- 123
2
votes
0
answers
280
views
Can High CPU be triggered by Login Failures?
On our SQL Server (used for TFS) we have recently had the CPU pegged at 100% (with a few occasional dips) for nearly a day and a half.
Looking at the SQL Logs, there appears to be a correlation in ...
- 241
2
votes
0
answers
543
views
Window function sorting performance
For background, I am on an instance of SQL Server 2012 Enterprise Edition.
I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in ...
- 131
2
votes
0
answers
159
views
Almost identical queries but large time difference in execution
Exhibit A
SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at >...
- 121
2
votes
0
answers
101
views
Extended Events missing documentation
I have been looking pretty hard for the documentation regarding the RingBufferTarget
<RingBufferTarget truncated="0" eventsPerSec="1370714" processingTime="7" totalEventsProcessed="9595" ...
- 582
2
votes
0
answers
629
views
Significant difference between duration for querys in SHOW PROFILE and SHOW PROFILES?
I have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes.
mysql> SHOW PROFILES;
+----------+-------------+-----------------------------------------------...
- 21
2
votes
0
answers
718
views
Oracle: separate column histograms for partitions
I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY). When I collect stats on the table, I end up with a HEIGHT BALANCED histogram ...
- 434
2
votes
0
answers
1k
views
group by and order by making query very slow in mysql?
Query works fast:
select SQL_NO_CACHE
users.id, users.name
from useres
left join user_statistics
on users.id = user_statistics.user_id
where users.is_deleted = 0
group by users.id
...
- 121
2
votes
0
answers
2k
views
mariadb out of memory crash
I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider.
Both machines have 2GB RAM and 1GB swap....
- 121
2
votes
0
answers
759
views
Any way to speed up my queries in MariaDB with tables less than a 4 GB in size?
I'm doing some text mining work and a bit frustrated that queries are taking 6 to 10 minutes to run.
My workstation has a 3.5ghz Xeon, 32GB of RAM, and 2 SSD as RAID 0.
Is it possible to do ...
- 121
2
votes
0
answers
347
views
Does Oracle ADO.NET varchar parameter size influence query plan reuse?
I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
- 500
2
votes
0
answers
502
views
MySQL aggregation count performance
I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year.
The table structure looks like:
id | year | amount
The query ...
- 121
2
votes
0
answers
491
views
PostgreSQL 9.2 (PostGIS) performance problem
A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow.
Well, for example, i have table:
CREATE TABLE sometable
(
gid serial NOT NULL,
geom ...
2
votes
0
answers
473
views
MySql Very Slow Writes & Out of memory error
I am having a rds db instance in AWS which is used by Drupal 7 instatllation.
I am getting the below error messages in the log:
> 130912 11:57:39 [ERROR] /rdsdbbin/mysql/bin/mysqld: Out of memory
&...
- 133
2
votes
0
answers
100
views
designing mysql for large tables
Currently I've been assigned the task to research ways to speed up an existing project. It's going to be rebuild so we can start from scratch, the current implementation has been build by another ...
2
votes
0
answers
1k
views
Properly index magento mysql tables to improve specific query performance
I am running magento community edition and its db performance is horrible. I have the following query:
SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM `...
- 131
2
votes
0
answers
113
views
Performance of database with transactional data
There are lots of row modifications (several thousands of rows per minute) in our PostgreSQL database that stores transactional data. We have a problem because PostgreSQL runs vacuuming process and ...
- 121
2
votes
0
answers
1k
views
How to calculate running total based on dynamic values?
I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
- 121
2
votes
0
answers
1k
views
SQL Server Database Engine Tuning Advisor vs DMV queries
What is the best way to tune the indexes on my production database? I normally use the following query :
SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
...
- 305
2
votes
0
answers
230
views
Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id
I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend....
- 1,710
2
votes
0
answers
2k
views
SQL query optimization in Prestashop e-Commerce solution
I'm working on a layered navigation module within Prestashop e-Commerce solution.
I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
2
votes
1
answer
1k
views
Query very slow on innodb
I'm using mysql 5.7 on ubuntu server 14.04, and engine innodb.
I have a table that are very huge (about 66 Go), I insert and delete from it without problem, but some query of select are taking lot of ...
- 85
2
votes
1
answer
457
views
Two queries usually run fast but get slow when joined
There are those 2 tables
CREATE TABLE IF NOT EXISTS `media` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`user` int(11) NOT NULL,
`order_type` int(11) NOT NULL,
`media_id` varchar(50) COLLATE ...
- 173
2
votes
1
answer
99
views
Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit
We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
- 121
2
votes
1
answer
640
views
Copying to tmp table slow for query ... but only sometimes
I have a query, and I admit it may not be the prettiest query in the world. Sometimes this query runs fairly fast (e.g. 200ms) and other times it runs super slow (e.g. 30s). I don't know why, and ...
- 347