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

2
votes
2answers
258 views

dm_db_missing_index_details - does the order of equality_columns matter

I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view. MSDN says that this ...
5
votes
1answer
2k views

Storing hex values as strings or integers?

I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints. I'm ...
3
votes
3answers
775 views

Best database choice for a highly scalable web ticketing system?

Essentially, we are developing using Ruby on Rails and want to make a highly scalable ticketing system. Essentially we will have a small database (containing event details and tickets sold) that will ...
5
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
1
vote
1answer
582 views

Performance problem caused by joining with the result of a scalar function?

In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join. When I store the result of the left side into a temporary table and ...
6
votes
3answers
4k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
22
votes
2answers
7k views

How do I properly perform a MySQL bake-off?

I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
3
votes
2answers
585 views

Degrading PostgreSQL 8.4 write performance

Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when throughput of the database significantly degraded over time from thousands of write transactions ...
9
votes
1answer
9k views

Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?

During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...
5
votes
1answer
834 views

Need help with ASYNC_NETWORK_IO

We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here. The ...
4
votes
5answers
2k views

Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment. However, none of us are formal DBA's so a lot of what we are doing is ...
6
votes
1answer
718 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 ...
14
votes
2answers
6k views

Reasons for occasionally slow queries?

We are running MySQL 5.1 on Windows Server 2008 R2. We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
7
votes
4answers
1k views

How can I tell if my SQL Server DB performance is hardware-limited?

Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
4
votes
1answer
602 views

Sql Server Performance Degradation in moving from 2005 to 2008

I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
9
votes
2answers
14k 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 ...
5
votes
3answers
1k views

Suggestion required for Large Database Optimization and performance

We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
1
vote
2answers
1k views

How fast should I expect to load data on a high performance computer?

I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
7
votes
4answers
14k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
11
votes
1answer
3k 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 ...
7
votes
2answers
1k 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) , ...
4
votes
1answer
922 views

Linked tables versus stored procedure performance in MySQL

Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form). Option 1 is to ...
4
votes
1answer
1k views

Ms-access ODBC calls for linked table behaviour

I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables. The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise ...
1
vote
2answers
740 views

Performance tuning OLTP query

I have following sqls executed million times a day, single execution isnt an issue but executing million times causing some performance issues..Thanks in advance for your help! NOTE: data can change ...
3
votes
1answer
336 views

Badly performing query plan after adding ORDER BY to Full Text CONTAINS search

The following query is proving beyond my abilities to fix - hope someone can help. SELECT top 100 Id, Name, InsertDate FROM Foo WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"') ORDER BY ...
38
votes
6answers
38k views

About single threaded versus multithreaded databases performance

H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded. My question is: when does a multi-thread database become more interesting than an ...
9
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, ...
3
votes
1answer
4k views

sqloledb vs sqlncli - Performance difference or just feature set?

I've been running through some Classic ASP code recently and was wondering if there was a performance difference between connecting using: ...
2
votes
1answer
1k views

Inner cursor performance issues

Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this: DECLARE ...
5
votes
3answers
8k 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 ...
6
votes
1answer
843 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
5
votes
1answer
1k views

Indexing to improve performance of range queries

I've got a relation in a Postgres 8.2 database similar to this: CREATE TABLE foo ( foo_id varchar(160) NOT NULL, bar_id varchar(160) NOT NULL, created bigint NOT NULL, PRIMARY KEY ...
5
votes
4answers
544 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. ...
2
votes
1answer
2k views

Tips for Optimizing Oracle Warehouse Builder 11.1 with Oracle DB 10.2

Are there any tips for improving performance of Control Center and Design Center in Oracle Warehouse Builder? At the client side it is very slow to open and refresh Design center and control center ...
2
votes
2answers
865 views

SQL datatype choice for flagging a read/ unread message?

I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value. for example if a message is ...
17
votes
3answers
4k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
5
votes
3answers
4k views

Query to normalize table/combine row text

I have a table (call it oldTable) with columns like so: ID (int),Rank (int),TextLineNumber (int),SomeText (varchar) The primarykey is multi-part: ID+Rank+TextLineNumber. I'm trying to ...
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 ...
1
vote
3answers
3k views

How to choose the last recorded added without sorting

I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int) I need to run a simple query: SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC ...
4
votes
2answers
125 views

How hard would it be to have PostgreSQL run a test to adjust the cost parameters?

By using EXPLAIN one can see the cost of running a query. The cost is in artificial units, and they are based on pre-defined parameters. 1 is one disk fetch and a CPU cycle is 0.01 units, I believe. ...
4
votes
3answers
1k views

Most suitable DB Architecture for real-time high-volume processing API

I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5. The DB consists of only a few tables without high usage ...
3
votes
2answers
317 views

Comparing two XML query plans

Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
6
votes
3answers
422 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 ...
22
votes
5answers
12k views

In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets. The query in question, I have three ANDs in the WHERE clause Does the order of the clauses matter? ...
8
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, ...
14
votes
4answers
33k views

Methods of speeding up a huge DELETE FROM <table> with no clauses

Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
12
votes
3answers
6k views

How do indices impact query performance?

Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
11
votes
3answers
7k views

SQL Server performance sudden degradation

I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
7
votes
4answers
1k views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
16
votes
2answers
4k views

Index vs. partition

Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course ...