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

10
votes
2answers
913 views

Strategy to handle an SQL Server DB with too many files (BLOBs) in it?

Scenario: SQL Server 2005 database servicing an ASP.NET application (on a separate web servers). Database: DB has about 5GB of "normal" data in it, and about 15GB of "files" (e.g.: a 200k PDF stored ...
194
votes
15answers
76k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
5
votes
2answers
418 views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
2
votes
1answer
166 views

Optimising a query using hint locks

I have a stored procedure that is being called very frequently. For some database servers it is as often as 20 times per second. Unfortunately because different clients call this procedure, I can't ...
4
votes
2answers
5k 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 ...
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 ...
0
votes
2answers
1k views

Some queries slower in MySQL 5.5 than 5.0?

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran. We recently updated ...
6
votes
2answers
736 views

What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
3
votes
1answer
272 views

Adding new key(s) to enable partitioning, INSERT performance?

I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
1
vote
1answer
289 views

Database inserts/updates slow only in Production

We have a int/test/staging/production Mysql database setup. A Java application(using Hibernate) that periodically generates up to 200,000 updates/inserts (mainly updates by 20 to 1) completes in 10 ...
6
votes
1answer
1k views

What should every DBA know? [closed]

As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
6
votes
2answers
1k views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
1
vote
1answer
183 views

Hard Drive Configuration for my setup

I have a fairly large transactional database (100GB) with a lot of users. The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun ...
5
votes
1answer
1k views

NESTING_TRANSACTION_FULL latch

Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
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 ...
1
vote
1answer
731 views

Powershell Format Table Combine Values

I have an awesome script that gives vital data for running worker processes. What I'm attempting to do is to get these three values into a table so that they can be displayed all pretty-like. I ...
2
votes
4answers
2k views

One table with many rows or many tables with a few rows

I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at ...
0
votes
3answers
3k views

Will using bigint vs mediumint have a performance impact?

I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I ...
2
votes
3answers
630 views

Tuning Question

Every so often our box is HAMMERED to a crawl %Wait goes to 40 or 50% and connections grows to 300 or so. i think its due to a query running, but i wanted to see if any of the settigns on the mysql ...
1
vote
2answers
2k views

MySQL Cluster On Cloud vs MySQL Cluster On Dedicated Cluster

I saw this interesting article : http://mikaelronstrom.blogspot.com/2012/02/105bn-qpm-using-mysql-cluster-72.html and it made me get interested in MySQL Cluster. After little chat with hosting ...
1
vote
2answers
192 views

Perf of query of large table by date?

I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like: entry_id (pk), sensor_id (fk), measurement_value, created_at I ...
15
votes
6answers
43k views

How to efficiently check EXISTS on multiple columns?

This is an issue I come up against periodically and have not yet found a good solution for. Supposing the following table structure CREATE TABLE T ( A INT PRIMARY KEY, B CHAR(1000) NULL, C ...
1
vote
2answers
93 views

Trying to optimize a MySQL query that has sub selects

I have the following sql SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate, (SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as ...
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 ...
2
votes
3answers
33k views

SQL Server 2008 R2 High memory usage

I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround ...
2
votes
1answer
2k views

Prevent a single query from hogging all resources

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server? The ...
15
votes
2answers
20k views

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old ...
2
votes
1answer
92 views

MySQL Unions/Subselects not utilizing keys from associated tables

I've noticed by doing EXPLAINs that when a MySQL union between two tables is used, mysql creates a temporary table, but the temp table does not use keys, so queries are slowed considerably. Here is ...
5
votes
3answers
1k views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
8
votes
3answers
528 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, ...
7
votes
1answer
2k views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
2
votes
1answer
83 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
3
votes
2answers
525 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
5
votes
1answer
208 views

How do I find out who is the resource hog in SQL Server 2008?

We have a SQL Server 2008 installation on a machine with 24+GB of RAM and 16 cores. Plus fast drives, etc. A large machine by our standards. Anyway, that machine is 100% devoted to SQL Server and ...
1
vote
2answers
333 views

Explicitly update a table after sp_tableoption “text in row” advices wanted

I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set ...
3
votes
1answer
3k views

mysql: deleted performance_schema, is it a problem?

Been following this tutorial http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 in the process I deleted performance_schema along ...
2
votes
0answers
36 views

Splitting a wide table [duplicate]

Possible Duplicate: Database Design: New Table versus New Columns I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important ...
3
votes
1answer
1k views

Need advice for a 'tag' table design

My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design: 2 tables option : 1 table for the ...
3
votes
1answer
344 views

PostgreSQL memory spike upgrade from 8.2 to 9.1

We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers. Everything seems well with the restore of the databases, ...
1
vote
5answers
3k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
2
votes
1answer
2k views

Connection to localhost dramatically slower than to network server

I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB. When I'm on the network at work, the connection is almost instantaneous. When I am at home working with ...
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 ...
3
votes
1answer
584 views

Will an index that is a partial key of the begining of another index perform any better than the longer index?

If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches ...
2
votes
1answer
780 views

Poor MySQL for Drupal on Shared Hosting

My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly. Here are some of the flagged issues that I can see... In ...
2
votes
1answer
1k views

performance issues when concurrent requests

We are using SQL Server 2008 R2 on a Windows Server 2008 server. We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it. I'll take one SP as an ...
6
votes
2answers
673 views

Is it possible to give the optimizer more or all time it needs?

Given that the optimizer cannot take al the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off. I was ...
5
votes
2answers
758 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 ...
3
votes
1answer
522 views

Optimization around static cursor use

I'm currently in-house support for a vendor-supplied application which uses static cursors to crawl through a list of data. I've leaned on the vendor as much as I can to fix their cursor use with no ...
1
vote
4answers
583 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
3
votes
4answers
14k views

Cause of High Disk Queue Length in SQL Server

I have a fairly high-throughput application that occasionally decides to collapse on me. It's not very often - about once every ~3 weeks or so. When it does, if I check out perfmon, I see 100% "Avg. ...