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

0
votes
1answer
52 views

Get statistics of slow running queries

My database application runs many different queries. I have set log_min_duration_statement to 1000. But queries logged there are not always slow, most times they just take a few ms. Is it possible to ...
-1
votes
1answer
42 views

Query optimization and takes less execution time

CASE-1: SELECT * FROM A JOIN B ON B.CustomerID=A.CustomerID CASE-2: SELECT * FROM A JOIN (SELECT * FROM B) C ON C.CustomerID =A.CustomerID Which CASE is efficient ?
0
votes
2answers
63 views

Best database for a real time application [closed]

I am a student doing my Final Year Project where I need to build an android application for on the road navigation. The problem is the application has a feature that alerts the user of nearby incoming ...
0
votes
1answer
44 views

How to define the table structure for measured data in sqlite

I have an embedded system with an sqlite database. How should I define the table structure for the measured data from 4 sensors? Each sensor give us every second an numeric measured value. The ...
2
votes
0answers
28 views

Performance of bitwise operators on byte column vs individual bit columns

I'm designing a database schema at the moment, but have a performance query. Our table needs to have (at least) two flags, or boolean values. We can call these IsComplete and IsExportable. The ...
0
votes
1answer
61 views

Slow ranking values in MySQL

I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers. I'm supposed to display well over 70 different rankings ...
1
vote
0answers
82 views

Database performs differently on the two same Xeon E3 1270-v3 Quad Core machines [closed]

Configuration: RAM: 16 GB DDR3. HDD: Seagate 500 GB 7200 3Gb/s 3.5 inch SATA - idential disks. Postgres 9.4, tried with 9.4.1 also Windows 2008 R2 Web Tried these tunings: work_mem = 32MB ...
2
votes
1answer
102 views

Indexing nvarchar(128) in SQL Server

Have no idea how indexing works in SQL Server. Working with a SQL Server 2012 Intel Xeon 2.2 GHz dual core 8 GB RAM. Will indexing a nvarchar(128) in addition to int primary key cause any ...
3
votes
0answers
148 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
3
votes
1answer
138 views

Is a clustered index on a child table in a parent/child relationship the most optimal index?

A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a ...
0
votes
2answers
38 views

Is it a good idea to add a reserved field to a large MySQL table?

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible. Is it a good idea to add a text column to each table (say, meta) to save some ...
1
vote
2answers
181 views

Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server

So this is what I have so far: --Read xml content into a XML data type variable DECLARE @FileData XML SELECT @FileData = CONVERT(XML, BulkColumn) FROM OPENROWSET(BULK '\\file_path\test.xml', ...
1
vote
1answer
44 views

Does the length of the index name have any performance impact?

This may seem silly, but in trying to stick to my standard nomenclature when naming indexes in our DB, the latest one was a little lengthy, and like all things DB I wondered if it in any way could ...
0
votes
2answers
45 views

MySQL Image Queries

I have table employees in MySql database with Id, Name, Gender, HireDate, Photo. Photo field is blob. Now when I run this query: SELECT id FROM employees WHERE Photo IS NULL ; it takes forever to ...
0
votes
1answer
62 views

Application is hanging when database upgraded from SQL Server 2000 to SQL Server 2008 R2

Presently we are working with SQL Server 2000, the database is perfectly working with our software for retail sales counter application developed with VB 6.0. But after upgraded to SQL Server 2008 R2, ...
0
votes
1answer
143 views

MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB)

I'm struggling with this issue and just can't figure out why this is happening. CREATE TABLE sitecache._test ENGINE=MEMORY SELECT * FROM website.gallery_likes WHERE 1=0; Every time I execute this ...
1
vote
1answer
212 views

Non-sequential Primary Key performance in PostgreSQL 9.3

Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g. ...
5
votes
0answers
169 views

Timestamp-based index

I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered ...
0
votes
0answers
37 views

redshift performance effect if use 32 character long unique string as Primary key instead of auto incremented int

In my analytics application I am creating 32 char long unique string for each hit as user_session. In current implementation, I am storing user_session in tbl_sesssion in session column and I am ...
0
votes
2answers
25 views

Can I extrapolate from the progress reporting in MyISAM ALTER?

I'm migrating a 300 million row MyISAM table to add some indexes. Just because it might be relevant, here's my ALTER statement: ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ...
2
votes
1answer
94 views

Oracle 11g connect overhead

Currently one of our new applications is connecting to an Oracle 11g instance over 30,000 daily (at every user request) via their application server, using the same account. (it will be significantly ...
0
votes
0answers
43 views

Multiple subqueries performance issue

I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer. The query looks like that: SELECT c.Id ...
0
votes
1answer
31 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
1
vote
1answer
120 views

Query on 250 million records

I have a table with below structure CREATE TABLE [dbo].[ColumnValues]( [Id] [int] IDENTITY(1,1) NOT NULL, [ColumnId] [int] NOT NULL, [Value] [nvarchar](max) NULL, [OutputArea] ...
0
votes
0answers
73 views

PostgreSQL Performance - Many Large Databases

I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
0
votes
1answer
42 views

Query running slow on script and producing error 500 when run in phpmyadmin

I have a query that seems to take a good 30 seconds to execute even when the query produces no results. There are 4000 lines within the database and queries that produce all results run within ...
3
votes
1answer
42 views

io_stall question

I just installed SQL Server on new hardware and ran a test script to show io_stall information by file. Nothing else has been loaded yet, just a fresh install of SQL Server 2012. Why are the ...
0
votes
1answer
47 views

Improving lots of Left Joins ( SQL Server 2008 - procedure )

This query is running with 8 minutes to complete. I'm trying to help the programmer with this task. I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us. ...
0
votes
0answers
36 views

Performance/Maintenance Implications of a Write Heavy Table In An Otherwise Normal “CRUD” Database

We have a small-ish (<100,000 rows) MySQL database (on Amazon RDS). Its supporting a fairly simple read heavy CRUD web application. Currently we're building out a web analytics system. It will ...
1
vote
1answer
404 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
1
vote
2answers
231 views

OLAP or OLATP - How to determine

I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP. There is no analysis server installed, the DB is ...
0
votes
0answers
68 views

SELECT query on mySQL taking 20 seconds to execute

Many points to note: This SELECT statement is happening both on my site, terminal, and on phpmyadmin, so the problem isn't a coding problem. If you delete the 11,000,000 rows and keep 20,000 rows ...
6
votes
1answer
564 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
0
votes
1answer
122 views

MySQL performance of applying unique constraint single column vs combination column

I have a large MySQL InnoDB table containing around 137 million rows. I need to add a unique constraint to the table. We have two options: 1) A combination index on three columns, one of which will ...
6
votes
2answers
210 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
0
votes
1answer
146 views

MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

I have this mysql table: CREATE TABLE `codes` ( `code` bigint(11) unsigned NOT NULL, `allocation` int(11) NOT NULL DEFAULT '0', `used` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), ...
3
votes
2answers
91 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
3
votes
3answers
206 views

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
2
votes
1answer
133 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
2
votes
0answers
139 views

Excessive CPU Usage caused by long running query?

We have been getting intermittent excess CPU usage at 100%. There is not a whole amount running on the system. We also get warning of Batch Requests/sec above threshold.: 1030, which would seem to ...
1
vote
1answer
304 views

What does a “Buffer cache hit ratio” of 9990 mean?

I got this query from a blog post: SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Buffer cache hit ...
2
votes
1answer
279 views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
1
vote
0answers
34 views

Replication / Bulk Update between Servers

I'm looking to update several tables between two servers daily to have a replica of the data available. My question is, what is the least resource heavy and most effective method to implement this? ...
1
vote
2answers
190 views

Oracle nologging impact for REDO Generation

Oracle database is on noarchivelog mode and tablespace is configured with nologging option keeping there will not be a situation to recover any data in mind. Following scenario was occured during ETL ...
0
votes
1answer
367 views

Compare two big tables in PL/SQL

I need to implement comparing 2 tables by set of keys (columns of compared tables). By this comparing i should check equal records (equal = equal by set of defined keys (columns in these input ...
0
votes
1answer
114 views

Identity/Primary Key columns pattern for performance in SQL?

I was discussing about database layout/design with a friend and I got some questions about performance and best practices. I'm mainly a software programmer, so I may lack "common sense" regarding ...
7
votes
4answers
672 views

At What Point Should I Split or Partition a Very Large but Simple Table

Our site has some large but simple (INT, INT, DATE) tables for stats. Each table has up to 300,000,000 rows, and gets bigger every day. The hosting provider has suggested that we split or partition ...
2
votes
1answer
66 views

Resource cost of a blocked query

I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking. The reports are not business-critical, and they are frequently killed to alleviate the blocking. ...
4
votes
2answers
1k views

Join on dm_db_index_physical_stats very slow

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and ...
2
votes
0answers
55 views

Can CTE sub-statements be prevented from executing at all?

Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary. The reason why is that upon ...