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

5
votes
2answers
468 views

Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes). The data in the database is highly (and not arbitrarily) normalized. It very efficient ...
5
votes
2answers
793 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
5
votes
2answers
901 views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
5
votes
3answers
3k views

Why does this Entity Framework query perform so bad in MySQL?

Let me give you a little setup. We have a InnoDB table on MySQL 5.1 with close to 20 million records, no foreign keys, and proper indexes for queries we do. We are using the latest 6.3.5 MySQL ...
5
votes
1answer
283 views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
5
votes
1answer
1k views

Troubleshooting SOS_SCHEDULER_YIELD wait

Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems. After performing the same activities in both the development and ...
5
votes
1answer
188 views

Sql server snapshot isolation overhead when read_committed_snapshot is enabled

When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation? Snapshot ...
5
votes
1answer
462 views

Denormalized table designs in normalized database?

There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
5
votes
3answers
967 views

CPU usage on RDS instance monotonically increasing with no change to query volume

I have two RDS instances: a R/W master and a read-only replica. On 29th June, the replica stopped registering replication data - not sure if this is related. On July 3rd, the master's CPU usage ...
5
votes
1answer
305 views

How can I benchmark the performance of UDFs to figure out which one is faster?

Basically I have two scalar UDFs that output the same data, one that uses a cursor and one that uses a recursive CTE. I want to determine which one should be used and discard the other; I'd prefer to ...
5
votes
1answer
171 views

RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits? The last two weeks one of our applications has had downtime due to ...
5
votes
1answer
625 views

Oracle aggregate functions slows query down massively

I have a table with ~12M rows of data in it. Here is the table structure: SYSTEM_ID BATCH_ID MEASUREMENT_INDEX, PARAMETER_ONE, PARAMETER_TWO The primary key is made up of the first three columns. I ...
5
votes
2answers
550 views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
5
votes
3answers
8k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
5
votes
1answer
2k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
5
votes
3answers
427 views

Just how much is a query supposed to slow down the whole system?

This is similar to my earlier question but more generic. I have a system with 16GB of memory, 16 cores, supposedly quality hard drives, RHEL 5, MySQL 5.0.x. Yes, I know, we're working on upgrading ...
5
votes
1answer
93 views

Why would SQL cast have to be expensive if used on a join column?

I see that sql join internally functions always on some of the three basic operations : nested loops join, merge join and hash join. From multiple articles on the internet I see SQL casting is said ...
5
votes
1answer
154 views

How do I find out who is the resource hog in SQL Server 2008?

We have a SQL Server 2008 installation on a machine with 24+GB of RAM and 16 cores. Plus fast drives, etc. A large machine by our standards. Anyway, that machine is 100% devoted to SQL Server and ...
5
votes
1answer
1k views

Oracle's Merge v. Select->Update/Insert

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value? I'm trying to optimize a database, but I haven't found any speed tests comparing the two. I found ...
5
votes
1answer
468 views

Indications from obscenely high Operator Costs in Query Plan?

I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes. The ...
5
votes
3answers
347 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON ...
5
votes
1answer
374 views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
5
votes
1answer
159 views

Sudden increase in log_file_sync waits

I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion. Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
5
votes
1answer
316 views

PostgreSQL: Recommendation size for shared_buffers

I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago. I am looking for help about the proper memory values for the configuration file. Currently, I ...
5
votes
2answers
549 views

Teradata: How to design table to be normalized with many foreign key columns?

I am designing a table in Teradata with about 30 columns. These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc. It is bad design to store ...
5
votes
2answers
450 views

Large accounting table, best way to partitioning by two dates

I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
5
votes
2answers
2k views

What are the potential causes for a sudden MySQL slowdown?

A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms... It lasted about an hour and then the speed was normal again. What ...
5
votes
1answer
3k views

MySQL INNODB SELECT query hanging on copying to tmp table

I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website ...
5
votes
1answer
318 views

MySQL and Solid State Disks on EMC

Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace ...
5
votes
1answer
407 views

TPS too high in PostgreSQL

I created a table: CREATE TABLE foo ( id integer primary key, bar integer NOT NULL ); and inserted one row: INSERT INTO foo (id, bar) VALUES (1, 1); and ran a pgbench of the following ...
5
votes
1answer
1k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
5
votes
1answer
784 views

NESTING_TRANSACTION_FULL latch

Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
5
votes
1answer
760 views

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
5
votes
0answers
249 views

SQL Server 2012 performance issues (maybe due to linked server) [closed]

Summary of the problem We've spit our one database server environment into two servers. We are now having intermittent performance issues I am hoping someone can offer clues - perhaps direction ...
4
votes
3answers
131 views

How do you see which database on a Server uses most resources

I have a database server with a couple of databases on it. How can I see where any resource pressure may come from? I would like to get a table of: Database Name Batch Requests per second CPU ...
4
votes
3answers
488 views

Is a surrogate key better than a natural key in this case

I copied this code from here: CREATE TABLE records( email TEXT REFERENCES users(email), lat DECIMAL, lon DECIMAL, depth TEXT, upload_date TIMESTAMP, comment TEXT, PRIMARY ...
4
votes
3answers
399 views

Usage of SQL Server 2008 R2 Express. Please Advise

I'd like you all to give me hints and advise on the following scenario: We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application. The Web Application won't have ...
4
votes
3answers
912 views

For every insert statement I see sp_executesql in SQL Server Profiler

Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows. I saw for every insert I called, a call was made to ...
4
votes
3answers
1k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? ...
4
votes
3answers
650 views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
4
votes
2answers
397 views

GROUP BY with MAX versus just MAX

I am a programmer, dealing with a big table which the following scheme: UpdateTime, PK, datetime, notnull Name, PK, char(14), notnull TheData, float There is a clustered index on Name, UpdateTime ...
4
votes
5answers
2k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
4
votes
3answers
3k views

How to reduce the log file size without shrinking it in SQL server

In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple ...
4
votes
4answers
425 views

How much will using a varchar(15) PK affect my tables performance?

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
4
votes
3answers
2k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
4
votes
3answers
2k views

PostgreSQL - Help Optimizing SQL performance - full text search

I have a big table ~15M records called entities. I want to find top 5 entities matching "hockey" in their name. I have a Full text index built for that, which is used : ...
4
votes
3answers
2k views

Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL? I understand that where platform interoperability and/or backwards compatibility may be ...
4
votes
5answers
2k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
4
votes
2answers
918 views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
4
votes
3answers
9k views

Optimizing InnoDB default settings

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...