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

9
votes
2answers
25k views

MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
9
votes
1answer
1k views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
9
votes
3answers
4k views

Database suggestion for a social network/ knowledge base community?

I am looking into various database types and DBMS's for a new project I am wanting to start in the summer. I have built systems in MySQL and postgreSQL , now I am wanting to expand my knowledge and ...
9
votes
2answers
9k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
9
votes
3answers
14k 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 ...
9
votes
2answers
4k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
9
votes
2answers
10k views

Slow Performance Inserting Few Rows Into Huge Table

We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
8
votes
3answers
2k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
8
votes
5answers
1k views

Inclusion of ORDER BY on query that returns no rows drastically affects performance

Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant ...
8
votes
2answers
7k views

How badly do SQL Compilations impact the performance of SQL Server?

I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so. I whipped out SQL Profiler ...
8
votes
3answers
529 views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
8
votes
1answer
689 views

Why adding SQL Server user to “Perform volume maintenance tasks” improves the speed of database resizing so much?

If I want to create 5GB database with CREATE DATABASE [test] CONTAINMENT = NONE ON PRIMARY ( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB ) LOG ON ( ...
8
votes
3answers
788 views

Worse performance on a new server

We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
8
votes
2answers
2k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
8
votes
3answers
2k views

SQL Server - Multiple running totals

I've got a base table with transactions and I need to create a table with running totals. I need them to be per account and and also have a few running totals for each account (depending on the ...
8
votes
4answers
4k views

TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters. Here is my take on it: If I am seeing a lot of SQl compilations, then ...
8
votes
3answers
8k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
8
votes
6answers
4k views

What could be causing strange query timeouts between PHP and MySQL?

I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
8
votes
4answers
2k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
3answers
5k views

How do I best measure the query performance?

I have 2 stored procedures, where the second stored procedure is an improvement of the first one. I'm trying to measure by exactly how much that is an improvement. 1/ Measuring clock time doesn't ...
8
votes
3answers
5k views

How to boost performance of virgin queries in MS SQL Server?

I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
8
votes
2answers
333 views

Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
8
votes
2answers
2k views

Why you want to avoid Dynamic SQL in stored procedure?

I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
8
votes
2answers
657 views

Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting

While writing a query other day a thought came to me and have stuck in my mind. What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...
8
votes
2answers
5k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
8
votes
2answers
533 views

How to implement paging in an effective way?

I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by ...
8
votes
1answer
944 views

IO_STALL question and understanding

I am collecting IO_STALLS from sys.dm_io_virtual_file_stats every 5minutes and then doing a delta to see which files are being affected most by IO. In one 5min period I get a delta of 5826331 ms ...
8
votes
1answer
2k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
8
votes
1answer
2k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
8
votes
2answers
3k views

More CPU cores vs faster disks

I'm part of a small company so as usual covering a number of different roles. The latest of which is procuring a dedicated SQL Server box for our .NET web app. We've been quoted on a dual Xeon E5-2620 ...
8
votes
1answer
5k views

What is the usage of performance_schema in MySQL 5.5?

I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list. What is the usage of this new schema..? How can we monitor using this schema..? What are the ...
8
votes
2answers
13k views

MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
8
votes
2answers
2k views

In Google App Engine, what is the most effective many-to-many join model?

The BigTable design rejects many of the philosophies of standard relational models, explicitly preferring denormalization to a big host of tiny tables. One of the larger areas where this is a problem ...
8
votes
2answers
263 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 ...
7
votes
4answers
717 views

At What Point Should I Split or Partition a Very Large but Simple Table

Our site has some large but simple (INT, INT, DATE) tables for stats. Each table has up to 300,000,000 rows, and gets bigger every day. The hosting provider has suggested that we split or partition ...
7
votes
4answers
845 views

DDL contention on TempDB

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
7
votes
3answers
1k views

Performance benefit of taking unused databases offline

I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to ...
7
votes
3answers
720 views

More Databases Per Sql Server 2005 Instance

Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate. Database size ranges from 50mb to 40-60gb in the extreme, ...
7
votes
3answers
1k views

InnoDB import performance

I'm struggling with bulk importing a quite big InnoDB-Table consisting of roughly 10 Million rows (or 7GB) (which for me is the biggest table I've worked with so far). I did some research how to ...
7
votes
2answers
2k views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
7
votes
3answers
5k views

Performance Hit using CAST() in TSQL (SQL Server)?

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). So, if myField is of type NVARCHAR, we ...
7
votes
3answers
194 views

Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
7
votes
2answers
4k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
7
votes
2answers
892 views

What can I add to a server to make SQL restores faster?

I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored ...
7
votes
2answers
12k views

SQL Server gets slow over time until we have to restart it

We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
7
votes
1answer
264 views

Estimating IO Requirements for Bursty Usage

We have an application that queries a SQL database periodically throughout the day. There are periods of zero or only light activity, interspersed with individual requests for relatively large ...
7
votes
2answers
4k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
7
votes
3answers
3k views

SQL Server 2008 R2 High CPU

We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3. Our database works very hard (we run around 600,000 queries in 1 minute) and ...
7
votes
3answers
5k views

Multiple Oracle Instances — is this a good practice?

One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're ...