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
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, code-...
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: Provider=SQLOLEDB;server=NTSERVERNAME\SQLEXPRESS;database=...
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
9k 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
861 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
2k 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
552 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
898 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 transform/...
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
128 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
318 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
431 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 tag_post.post_id=...
23
votes
5answers
13k 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, sum(...
16
votes
4answers
37k 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 ...
9
votes
2answers
554 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
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 ...
6
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 ...
4
votes
2answers
3k 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 ...
10
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
22k 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
682 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 ...
4
votes
3answers
262 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 ...
6
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 ...
5
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?
6
votes
3answers
3k 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 nvarchar(...
19
votes
3answers
13k 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 ...
6
votes
1answer
190 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 /* ...
5
votes
1answer
114 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
340 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 ...
11
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
339 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
14k 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 reports....
3
votes
1answer
617 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, ...
20
votes
5answers
4k 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
1k 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. ...
3
votes
2answers
5k 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 ...
3
votes
2answers
3k 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 ...