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,004
questions with no upvoted or accepted answers
0
votes
1
answer
86
views
How can I improve this nested postgresql query?
I have a mildly complex query that is having rather poor performance:
UPDATE
web_pages
SET
state = 'fetching'
WHERE
web_pages.id = (
SELECT
web_pages.id
FROM
...
- 1,326
0
votes
1
answer
1k
views
MySQL uses more than 500% of the CPU
Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU.
I'm getting a lot of these errors:
1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file ...
- 135
0
votes
1
answer
1k
views
Speed up a MySQL BETWEEN query
SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo
I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind,
dateFrom, ...
- 1
0
votes
2
answers
63
views
Improve CASE select performance on view creation
I am new to SQL, and the below view query is taking a long time to return values because of the case statement, can someone help in identifying how I can speed things up?
CREATE OR REPLACE FORCE VIEW ...
- 9
0
votes
1
answer
173
views
Postgres: How to optimize time constrained wildcard search?
I'd like to optimize Postgres response times for a query which looks for
select
orders.id,
orders.created
from
orders left join order_items on order_items.order_id = orders.id
where
...
- 113
0
votes
1
answer
3k
views
Linked Server to Oracle seems to be slow
we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider.
The enabled options for provider are:
Dynamic parameter
Nested queries
...
0
votes
2
answers
924
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) ...
0
votes
1
answer
277
views
How to configure my.cnf in 5.6.17?
On production server(linux machine mysql 5.6.17), I tried to find my.cnf to change the system variables, but my.cnf was not in /etc/my.cnf location. It was in usr/ directory.
Also that file contains ...
- 213
0
votes
1
answer
835
views
optimize big sql query
My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
- 1
0
votes
2
answers
784
views
Performance issues on a MySQL database with 150 million rows
Current setup:
A managed vps server
InnoDB storage for big tables
10.0.36-MariaDB
18gb ram
8 x 2.40 GHz CPU.
I have a pretty big MariaDB database table which is used for Business intelligence ...
- 103
0
votes
3
answers
268
views
Best Indexing for Performance - MySQL
I have a question related performance DB MySQL. I have querying like this with big data inside :
SELECT
*
FROM
tableA a
INNER JOIN tableB b ON a.id = b.id AND a.code = b.code
...
- 17
0
votes
1
answer
230
views
intel p3600 SSD no increase in read speed - postgres rdbms vs hdd
We run a custom application that produces analyzable data-sets for medical research.
the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in ...
- 1
-1
votes
1
answer
35
views
Httpd is blocked at 512 connections on dedicated Server
I'm having serious performance problems with Httpd on dedicated server. In traffic peaks httpd stucks on 512 connections and without any reason all connections are slow and files which are downloading ...
- 1
-1
votes
1
answer
618
views
Postgresql uses slower constraint over faster index
I have a few tables (25m records +) which have a very INSERT, UPDATE and DELETE biased workload (typically 50,000 to 100,000 operations a day) where the query planner seems to make an odd choice of ...
- 1
-1
votes
2
answers
51
views
Factorising out repeating data and replacing it with an integer key
I have noticed in my SQLite database that some tables contain columns where many rows usually repeat the same data.
Transaction
key
from_account_key
to_account_key
timestamp
amount
data_type
data
...
- 1
-1
votes
1
answer
122
views
MySQL - Which options in the configuration file have an impact on memory usage?
I've been wondering how to manage MySQL memory usage since by default it takes up to 350MB while idle on my machine, I don't have memory issues, I honestly just wondered how could it be done.
I found ...
- 101
-1
votes
1
answer
426
views
Migrate MySQL to another partition in same server
I have a instance of MySQL in a local server. To improve the performance, I put a SSD just for MySQL.
Now I need migrate the MySQL instance, with my databse (a data has 25GB) for the SSD.
What the ...
- 19
-1
votes
2
answers
192
views
MySQL Select Query not using Required Index
I have a Table which has 2 Million Records and No primary or Unique Keys.
I added Two Indexes and I'm expecting an Index to be used for my Select Query.
When I saw the Explain Plan , Optimizer is not ...
- 11
-1
votes
1
answer
2k
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' ...
- 63
-1
votes
2
answers
140
views
SQL Server performance issue
I set up an online dedicated windows server in Godaddy hosting. The server has 32GB RAM, 2TB Storage, and 4 CPU Cores @ 3.1 GHz.
I installed SQL Server 2017 Enterprise Edition. Problem is this the ...
- 107
-1
votes
1
answer
739
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
-1
votes
3
answers
823
views
SUM value in table with timestamp column where timestamp is between start and stop timestamps in different table
I have a situation where there are network hosts whose monthly bandwidth is summed based on a start date that varies for each host.
The number of hosts is quite small (< 5000) but the history ...
- 345
-1
votes
1
answer
1k
views
Query takes too long to query with OR clause but their parts are very quick
I am querying two tables, each with ~1M rows indexed by their IDs, using the following query:
SELECT t.* FROM transactions t
INNER JOIN integration it ON it.id_trans = t.id_trans
WHERE t.id_trans = '...
- 293
-1
votes
2
answers
2k
views
MySQL Very Slow Performance Using Order by Clause
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT ...
- 135
-1
votes
2
answers
79
views
Index Seeks cost of OR operator
I have a query which is slow and it takes 15 seconds to execute.
The query fetches data from a view and in the where clause, I have few conditions which includes OR and AND.
exec sp_executesql N'...
- 1,305
-1
votes
1
answer
155
views
Managing Performance and Querying very large and constantly growing table in SQL Server
I have a table in SQL Server 2016 for which I am receiving data from an API. The data was in XML format so I have to break it into columns and bulk saving it into SQL. There are 32 columns in the ...
- 1
-1
votes
1
answer
246
views
SQL Server comparing server performance
I have SQL Server 2016 Standard installed on two different physical servers. Can you suggest a way I can compare the overall performance of these two servers?
For example, is there a query I can run ...
- 169
-1
votes
1
answer
146
views
MySQL Huge DELETE
I'm trying to delete 10000 rows from my database on MySQL 5.7 but it's been stuck for 7 hours.
Query:
DELETE FROM article WHERE id in (52109548,52109558,52109560,52109564,....).
This are my buffer ...
-1
votes
1
answer
159
views
MySQL Performance Problems
Started having issues with our central production MySQL Database Server today.
We have 1 central production MySQL 5.7.11-log server which is bare metal with 32GB mem and 12 core CPU with 2 SSDs in ...
- 201
-1
votes
1
answer
2k
views
Insert into temp table is taking longer than temp variable
http://pastebin.com/cx4hFSYb
I have a issue with a query that inserts data into multiple temp table ( total 4 )
from the select statement, suddenly it is slow when I try using temp variable it works ...
- 113
-1
votes
1
answer
449
views
postgresql reindex frequency and monitoring
What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
- 49
-1
votes
1
answer
101
views
Mysql: slow down with order by, global database problem
i create nasty query for testing reasons. Only for speed testing.
EXPLAIN SELECT m.post_id
FROM wp_postmeta m
LEFT JOIN wp_postmeta mi ON
m.`post_id` = mi.`post_id`
LEFT JOIN wp_postmeta md ON
(md.`...
- 99
-1
votes
1
answer
488
views
Rebuild an index in SQL Server 2012
When I execute this query, my .MDF file grows by 120 GB:
ALTER INDEX ALL ON TABLE_NAME REBUILD WITH (FILLFACTOR = 90)
or
ALTER INDEX ALL ON TABLE_NAME REBUILD
Can you tell me how to avoid ...
Keshav Bhandure
-1
votes
2
answers
920
views
Optimize query or split in two queries and process result separately?
I have this DB schema:
As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table:
SELECT COUNT(*)...
- 1,710
-1
votes
1
answer
505
views
Performance of MySQL BLOB fields processing
Suppose, I have large (about 1 GB) strings in ASCII encoding. I have to choose whether to store them as separate blob fields or split each one of them into smaller pieces and then store them as ...
- 99
-1
votes
1
answer
495
views
Query optimization and takes less execution time
CASE-1:
SELECT * FROM A
JOIN B ON B.CustomerID=A.CustomerID
CASE-2:
SELECT * FROM A
JOIN (SELECT * FROM B) C
ON C.CustomerID =A.CustomerID
Which CASE is efficient ?
wasifravian
-1
votes
2
answers
233
views
MySQL Image Queries
I have table employees in MySql database with Id, Name, Gender, HireDate, Photo. Photo field is blob.
Now when I run this query:
SELECT id FROM employees WHERE Photo IS NULL ;
it takes forever to ...
- 1
-1
votes
2
answers
221
views
SQL Performance: COUNT or extra attribute?
If I want to count the number of a multivalued attribute, which way is faster:
a) using COUNT(attribute) on a whole table
or
b) adding an extra attribute which counts these stored values (to be ...
-1
votes
1
answer
144
views
Complex query on big table
I have a big user table (3M), of structure:
age (int)
country (FK)
city (text)
gender (text)
active (bool)
My objective is to run a search finding 15 users of given age area, gender, city and ...
- 139
-1
votes
1
answer
3k
views
Very Slow MySql Stored Procedure
I'm working with a web app running on MySQL. It uses stored procedures for all database-related tasks. The original web app and the database were designed by another developer; I'm only maintaining it ...
- 111
-1
votes
1
answer
2k
views
Having too many connection problem
We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads.
1) We have 100 connectoin open in mysql.
2) ...
- 1
-1
votes
1
answer
150
views
When, if ever, is it justified to break normalization rules and replicate a field for efficiency?
Suppose I have two simple models, Sentences and Mentionss, where each Mention is a reference to a mention of a in a Sentence. Let's say they have the following normalized schema:
CREATE TABLE ...
-1
votes
3
answers
8k
views
How to delete millions of records from table
I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success.
Query ...
- 101
-1
votes
1
answer
585
views
SQL Server Memory Cap reached its maximum state and threw an Out Of Memory Exception
We have set a memory cap limit of 43GB in SQL server instance, so thereafter it keeps 42 GB near about and keeps near about 1 GB for its processing operations.
But then we received near about 500,000 ...
-1
votes
2
answers
172
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 ...
- 1
-1
votes
3
answers
105
views
Looking to improve query performance
This is my query:
select S-Num.Title,
jobs.JobNum,
jobs.JobPart,
Part.PartNum,
jobs.RevisionNum,
jobs.Seq,
jobs.Parent,
Part.PartDescription,
...
- 101
-2
votes
1
answer
54
views
RMAN ORACLE issue
I have the issue when I run the command RMAN as mentioned below :
Please any help
- 1
-2
votes
1
answer
528
views
VARCHAR vs TEXT performance when data fits on row
mysql> desc temp1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| value | ...
- 115
-2
votes
1
answer
44
views
How does 'Top SQL - By Application' values from SQL sentry correlates
I have run a performance simulation on my application server and pull the sentry report for my analysis. And While going through the top sql -by application, I found difficult to correlates the values ...
-2
votes
1
answer
1k
views
Optimize a query using same temporary table in outer apply
I have a query using outer apply on the same temporary table again(written by someone long back).
It is performing very slow.Please suggest ways to improve.
Thanks in advance.
UPDATE fb
...
- 25