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

3
votes
2answers
118 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. ...
3
votes
3answers
984 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
316 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 ...
5
votes
3answers
413 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 ...
20
votes
5answers
11k 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
29k 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 ...
10
votes
3answers
6k 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 ...
6
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 ...
15
votes
2answers
3k 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 ...
8
votes
2answers
549 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 ...
7
votes
3answers
6k 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 ...
5
votes
1answer
4k 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 ...
3
votes
2answers
2k views

PostgreSQL: SET-ing from psql arguments

Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution: SET statement_timeout TO '1min'; This is great, but ...
8
votes
2answers
2k 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 ...
2
votes
2answers
18k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...
4
votes
1answer
631 views

What can be done, if anything, to tune a SharePoint database?

SharePoint uses a single table (i.e. dbo.AllUserData) with columns like nvarchar1, nvarchar2, ..., nvarchar64 or int1, int2, ..., int16 (193 columns altogether) to store all items from all lists in ...
5
votes
3answers
1k views

Is the ADO.NET Entity Framework known for generating poorly performing queries?

This question is inspired by the comment posted to the latest ServerFault blog post: Are you guys still using LINQ to SQL? I know I can use SQL Server Profiler and/or the ToTraceString method to ...
3
votes
3answers
260 views

Does separating VARCHAR values based on size have a performance gain?

I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
5
votes
4answers
5k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
4
votes
5answers
2k views

What is the best use of memory in MySQL?

I have a MySql server with 2Gb of memory. What's the best configuration to use then with best results? How can I "distribute" this memory fine to MySql server?
5
votes
3answers
2k views

Building Dynamic SQL-Server Where Clause

Let us review this dba.exchange Oracle question for SQL-Server. This is SaUce's code, after a little formatting: CREATE PROCEDURE GetCustomer @FirstN nvarchar(20) = NULL, @LastN ...
19
votes
3answers
10k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
6
votes
4answers
5k 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
188 views

Is my function nondeterministic one

I have two functions: fn_Without_Param and fn_With_Param CREATE FUNCTION [dbo].[fn_Without_Param] ( ) ... INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 ) and /* ...
4
votes
1answer
112 views

Data transformation implementation

My company's database, I am working with, makes available a data to a lot of external applications. So I need to transform the same data to a lot of dynamic views. I can see that a former database ...
6
votes
1answer
335 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 ...
10
votes
2answers
6k views

SQL Server 2008 - Cross database performance on same physical machine and server instance

Is there any performance hit when doing a select across another DB on the same physical machine? So I have 2 databases on the same physical machine running within the same SQL 2008 instance. For ...
17
votes
4answers
2k views

SSDs with Oracle

We've been looking into using SSDs with Oracle to speed up our test migration runs. It currently takes 12-18 hours to complete a migration run, depnding on the volume of data (we're obviously doing ...
5
votes
1answer
336 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 ...
10
votes
3answers
11k views

How do you clear out all old query plans from within Microsoft SQL Server?

We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these ...
3
votes
1answer
610 views

MySQL CREATE statement are really slow (Mac OS X / Macport install) [closed]

I recently noticed that recreating the database for the web application I am working on is taking a lot of time. I had just reinstalled Mysql from Macport... I am getting really lousy performances, ...
18
votes
5answers
3k views

How far should you go with normalization?

I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there ...
18
votes
4answers
948 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. ...
2
votes
2answers
4k views

MS SQL Server 2008: How to improve performance after deleting transaction log file

Due to the size of transaction log file (it grew up to 200 GB) I had to delete it because the disk was running out of space. I know this is not a good practice but anyway after that the performance is ...
2
votes
2answers
2k views

Is it better to store calculated values or recalculate them on request? [duplicate]

Possible Duplicate: Storing vs calculating aggregate values At an old job, we had this database, it wasn't normalized rigth, some tables wheren't used anymore, it was patch after patch. In ...
54
votes
5answers
19k views

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
20
votes
3answers
2k views

Which collation should I choose for a muiti-language website?

Does a collation have any influence over a query speed? Does the size of a table change depending of the collation? If I want to build a website that must support all possible languages (lets take ...
7
votes
3answers
196 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 ...
31
votes
5answers
45k views

How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables What are the common symptoms of a mistuned/misconfigured system? What configuration parameters do you most commonly change from ...
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 ...
17
votes
2answers
598 views

Get and Put performance testing on Google BigTables (and other integrated DBs)

What are some effective ways to perform programmatic performance testing on database operations, especially in environments where the databases themselves do not offer dedicated tools? For example, ...
74
votes
6answers
50k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
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 ...
12
votes
1answer
236 views

What are the performance implications of polymodels versus normal BigTable models in google app engine?

What produces the best performance in regular google app engine use, a polymodel or a normal "BigTable" model? The polymodel, effectively, creates a column in the parent table called "class" which ...
98
votes
7answers
99k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
3
votes
1answer
3k views

To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to ...
1
vote
3answers
1k views

SSIS 2008 painfully slow to import CSV

I am using SSIS to import a 25,000 row CSV to an empty table. The table has a primary key that is populated as the data is inserted. I am using an ADO.Net destination. What reasons are there why ...
1
vote
2answers
312 views

What is wrong in debunking myths about clustered indexes?

What is wrong in code. or its results, illustrating that clustered index is evil [1]? and how to de-debunk, i.e. to return to customary myths and best practices? [1] Debunking myths about ...