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.
4
votes
4answers
375 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
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
3answers
6k 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 ...
4
votes
4answers
928 views
T-SQL INNER JOIN Optimisation
Well, I really have read so many articles, used different techniques and done some random things to do this.
My problem was that a have big tables (over one million rows) and some small (with few ...
4
votes
1answer
136 views
Stored procedure performance metrics
I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by ...
4
votes
3answers
269 views
Using DTA vs. evaluating DMVs?
Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.
Form How to determine if an Index is required or necessary I have ...
4
votes
3answers
1k 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 ...
4
votes
2answers
2k 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 ...
4
votes
4answers
100 views
In Memory Database for high transaction and volume spikes on a website [closed]
I am looking at the IA for a hypothetical application scenario, for example:
A high demand application such as an online ticketing website where there can be demand for 100,000 tickets to be ...
4
votes
1answer
367 views
In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?
I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc.
For example:
CASE WHEN ...
4
votes
2answers
440 views
Performance degradation after rebuilding indexes
I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
4
votes
1answer
124 views
Optimum configuration for 4 SSD drives
I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single ...
4
votes
2answers
572 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 ...
4
votes
2answers
717 views
Database design for dynamic Meta fields of an entity that shall be queried intensively
I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, ...
4
votes
2answers
213 views
What is the consensus on using NOT NULL constraints on all text-based fields in a table?
Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying.
I'm curious what the feelings are with using something like NOT NULL ...
4
votes
2answers
184 views
Best way(s) to time ad hoc queries and stored procedures
What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures?
Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?
4
votes
1answer
353 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 ...
4
votes
4answers
2k 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
1k 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?
4
votes
3answers
1k 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 ...
4
votes
2answers
131 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
4
votes
1answer
305 views
need suggestions to improve view performance
I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
4
votes
1answer
130 views
250 billion rows - computing interconnectedness on a really huge scale
I have a table of approximately 700k rows, each identified by a unique itemnumber.
Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
4
votes
3answers
1k views
How database size affects performance: Theory vs reality
There is a lot out there that says database size should not affect performance to any great degree. As long as indexes on tables fit in memory the database should remain performant.
However what is ...
4
votes
1answer
732 views
Analysing SQL Server Network I/O
A frequent request I get from clients is to help figure out what queries or clients are making the largest contribution to SQL Server saturating an Ethernet link.
Ideally I'd look at some counters ...
4
votes
1answer
819 views
SELECT with large WHERE IN clause taking long time
I'm pretty new at all this so be gentle :).
I have large medical DB where I need to extract data for patients based on selected doctor(s).
Right now I build a patient list by
SELECT patID
FROM ...
4
votes
2answers
584 views
How do I get the execution plan for a view?
I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used.
How do I do this?
I'd rather not have to copy and paste ...
4
votes
2answers
245 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
4
votes
2answers
715 views
SSAS queries time out when Windows File System Cache drops
We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the ...
4
votes
1answer
304 views
how to improve performance by using or not using table variables
I have a table variable:
DECLARE @to_process TABLE
(
[Id] [bigint] NOT NULL,
[SequenceId] [bigint] NOT NULL,
...
)
INSERT INTO @to_process
( Id
, SequenceId
...
)
SELECT
...
4
votes
1answer
756 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 ...
4
votes
1answer
122 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
4
votes
2answers
756 views
Is there a MySQL maximum database or table count?
I am in the process of creating a massive archive of bitboards stored in MySQL. In my schema, there are four linked sets of bitboards which--for query sake--are represented in four different tables. ...
4
votes
1answer
136 views
Is there a way to make SQL Server's Database Tuning Advisor use more than one core?
I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server.
For the "consuming workload" step, it appears to use all ...
4
votes
2answers
512 views
PostgreSQL: reuse of complex intermediate result in same query
Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in ...
4
votes
1answer
1k views
How can I monitor the call count per stored procedure per timespan?
In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
4
votes
4answers
142 views
Handling a big table in mysql
I have a really big table (approx. > 100.000.000 rows and a size of > 50GB) and it's getting a big performance kill right now. Beneath the primary key (id) it uses a fulltext key on a varchar(500) ...
4
votes
1answer
152 views
One CPU in a 32-core system is getting to 100% usage and hurting performance
I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and ...
4
votes
1answer
1k views
Why is DELETE so much slower than SELECT, then DELETE by id?
I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
4
votes
2answers
314 views
varchar performance impact
This question has been asked before ( speed impact of using varchar in MSSQL 2008 ), but the answers are not detailed enough to my satisfaction. Admitedly this is not a current issue and mainly for ...
4
votes
1answer
378 views
Terabyte data set
I have the feeling that most of the database systems originate in the '80s and stereotypically implement tables, ACID transactions and constraints. They were build having in mind the scarcity of ...
4
votes
1answer
625 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 ...
4
votes
1answer
102 views
.mdf vs .ldf Performance considerations
I just learned from an answer to this question that the .mdf and .ldf files that SQL Server uses are an important pair for the database. I previously thought the .ldf was just a non-critical log for ...
4
votes
1answer
70 views
Oracle BLOB store performance
LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise.
I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work?
I have a lot of ...
4
votes
2answers
5k views
way to prevent queries from waiting for table level lock
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
4
votes
2answers
2k views
Primary key vs. unique index: performance difference with foreign keys?
Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:
A primary key
A unique ...
4
votes
1answer
192 views
Getting ratio of reads to writes on SQL Server
Sorry I am not a DBA so bear with me. I wanted to get a better understanding of the ratio of reads to writes in our archiecture. The best to get this is at the database. We are using SQL Server ...
4
votes
3answers
1k 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 ...
4
votes
1answer
37 views
Performance impact of sp_configure blocked process threshold change
I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use
EXECUTE sp_configure 'blocked process threshold', 5
In combination with a server ...
4
votes
1answer
119 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 ...
