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.

learn more… | top users | synonyms

1
vote
3answers
85 views
+50

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
1
vote
1answer
19 views

Non-sequential Primary Key performance in PostgreSQL 9.3

Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g. ...
1
vote
3answers
2k views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
0
votes
0answers
29 views

Application is hanging when database upgraded from SQL Server 2000 to SQL Server 2008 R2

Presently we are working with SQL Server 2000, the database is perfectly working with our software for retail sales counter application developed with VB 6.0. But after upgraded to SQL Server 2008 R2, ...
23
votes
1answer
3k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
1
vote
3answers
321 views

MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?

I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and ...
1
vote
2answers
157 views

Oracle Compatible Parameter to set

Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?
4
votes
0answers
49 views

Timestamp-based index

I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered ...
8
votes
2answers
244 views

Minimizing Indexed Reads with Complex Criteria

I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 ...
0
votes
0answers
14 views

redshift performance effect if use 32 character long unique string as Primary key instead of auto incremented int

In my analytics application I am creating 32 char long unique string for each hit as user_session. In current implementation, I am storing user_session in tbl_sesssion in session column and I am ...
1
vote
1answer
354 views

Internal query on CDEF$ and CON$ very CPU bound

I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the ...
0
votes
1answer
18 views

Can I extrapolate from the progress reporting in MyISAM ALTER?

I'm migrating a 300 million row MyISAM table to add some indexes. Just because it might be relevant, here's my ALTER statement: ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ...
2
votes
1answer
60 views

Oracle 11g connect overhead

Currently one of our new applications is connecting to an Oracle 11g instance over 30,000 daily (at every user request) via their application server, using the same account. (it will be significantly ...
0
votes
1answer
38 views

Improving lots of Left Joins ( SQL Server 2008 - procedure )

This query is running with 8 minutes to complete. I'm trying to help the programmer with this task. I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us. ...
0
votes
0answers
26 views

Multiple subqueries performance issue

I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer. The query looks like that: SELECT c.Id ...
0
votes
1answer
22 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
1
vote
1answer
65 views

Query on 250 million records

I have a table with below structure CREATE TABLE [dbo].[ColumnValues]( [Id] [int] IDENTITY(1,1) NOT NULL, [ColumnId] [int] NOT NULL, [Value] [nvarchar](max) NULL, [OutputArea] ...
0
votes
1answer
265 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
0
votes
0answers
33 views

PostgreSQL Performance - Many Large Databases

I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
2
votes
1answer
35 views

io_stall question

I just installed SQL Server on new hardware and ran a test script to show io_stall information by file. Nothing else has been loaded yet, just a fresh install of SQL Server 2012. Why are the ...
4
votes
1answer
3k views

SELECT with large WHERE IN clause taking long time

I'm pretty new at all this so be gentle :). I have large medical DB where I need to extract data for patients based on selected doctor(s). Right now I build a patient list by SELECT patID FROM ...
0
votes
1answer
13 views

Query running slow on script and producing error 500 when run in phpmyadmin

I have a query that seems to take a good 30 seconds to execute even when the query produces no results. There are 4000 lines within the database and queries that produce all results run within ...
1
vote
2answers
113 views

OLAP or OLATP - How to determine

I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP. There is no analysis server installed, the DB is ...
0
votes
0answers
12 views

Performance/Maintenance Implications of a Write Heavy Table In An Otherwise Normal “CRUD” Database

We have a small-ish (<100,000 rows) MySQL database (on Amazon RDS). Its supporting a fairly simple read heavy CRUD web application. Currently we're building out a web analytics system. It will ...
20
votes
4answers
17k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
0
votes
1answer
214 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
3
votes
2answers
336 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
0
votes
2answers
951 views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
0
votes
2answers
178 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
1
vote
1answer
183 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
0
votes
1answer
589 views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
2
votes
1answer
222 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
3
votes
2answers
402 views

SQL server query is slow *without* including execution plan

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my ...
0
votes
1answer
94 views

Oracle 11g max login fail attempts workaround

My problem with database starts with situation where I cant really modify anything in database. My project specialist has limited time to help me. Here is the thing: My user in Oracle database has ...
10
votes
4answers
5k views

What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better: delete rows and insert new ones, or update already existing rows For me ...
0
votes
1answer
191 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
0answers
41 views

SELECT query on mySQL taking 20 seconds to execute

Many points to note: This SELECT statement is happening both on my site, terminal, and on phpmyadmin, so the problem isn't a coding problem. If you delete the 11,000,000 rows and keep 20,000 rows ...
6
votes
1answer
460 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
5
votes
2answers
154 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
3
votes
1answer
425 views

SQL Server 2012 Local SSD TempDB Slow

I have a production deployment using local SSDs for tempDB. I have 2 SSDs in a RAID1 configuration. I am seeing average reads of 1-2ms but the average writes are showing as 1377ms on all four of my ...
3
votes
2answers
81 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
0
votes
1answer
28 views

MySQL performance of applying unique constraint single column vs combination column

I have a large MySQL InnoDB table containing around 137 million rows. I need to add a unique constraint to the table. We have two options: 1) A combination index on three columns, one of which will ...
15
votes
4answers
781 views

When using an ORM, what are some things to watch out for in your database design

What are some database design gotchas to watch out for when you know the database will be accessed using a Object Relational Mapper (ORM) Wikipedia? Also see Entity Framework NHibernate or LLBLGenPro. ...
0
votes
2answers
124 views

Slow INSERT/UPDATE on InnoDB

I have a problem with the performance of my MySQL server, let me explain: I instaled a MySQL server, version 5.5.20 Server version: 5.5.20-log Source distribution SELECT queries runs fast on ...
0
votes
1answer
112 views

How to track IOPS statistics?

Is there a way to access statistics about IO operations per second in DB2 9.7? I am not interested in the buffer pool hit ratio, just IOPS.
1
vote
1answer
135 views

Handling a chat room in a database

I have been searching all day to find a better answer to my question but those I found was useful but do not critically analyse my question. I am building a chat room using php, mysql, jquery and ...
0
votes
1answer
96 views

Difference between FIND_IN_SET and LIKE

is there a difference in behavior or performance in choosing FIND_IN_SET over LIKE for a operation that looks like: SELECT * FROM my_table WHERE column_1 LIKE %string% vs SELECT * FROM my_table ...
0
votes
1answer
58 views

MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

I have this mysql table: CREATE TABLE `codes` ( `code` bigint(11) unsigned NOT NULL, `allocation` int(11) NOT NULL DEFAULT '0', `used` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), ...
2
votes
1answer
66 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
0
votes
2answers
151 views

MySQL/MariaDB Limitations

we're running MariaDB on our DB server. We have to install multiple databases on each DB instance (in fact each DB server runs multiple MariaDB instances) because each database must be separated. ...