Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

3
votes
1answer
868 views

What does “cleaning up” mean in MySQL InnoDB transaction status and how to prevent it?

In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load: > SHOW ENGINE innodb STATUS; ... ------------ TRANSACTIONS ------------ ... ---...
1
vote
1answer
70 views

Why am I seeing OPTION (MAXDOP 1) in my Actual query plan even though I've not set it in the stored proc?

SQL Server is set with MAXDOP 0 by default. In the stored procedure I'm testing I keep seeing WITH (MAXDOP 1) in the Actual Execution Plans even though I've not specified any hints other than ...
0
votes
0answers
26 views

How to minimize locking / maximize update throughput on small table

I have problem on SQL Server. I have a very, very small table which has a very high update frequency. CREATE TABLE [dbo].[counters]( [id] [int] NOT NULL, [val] [bigint] NOT NULL) Table has ...
-3
votes
0answers
12 views

how to analyze explain plan in oracle 11g [on hold]

hi please can you tell how to analze explain plan in oracle 11g . where to look and what to change . please can you provide me any document reg tuning . thanks List item
1
vote
1answer
270 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
0
votes
0answers
78 views

Query fast, but slow from procedure - or - why my new index is not being used? [closed]

In my wonderful job of tuning stored procedures, often times when I remove the query from inside the stored procedure it generates a very good plan, and when I put the same query wrapped inside a ...
1
vote
1answer
44 views

Time spent on the server vs network

Is there a way i can track for a given query how much time was spent by SQL server vs time spent in transferring data in network? I am learning performance tuning, hence this question. I am looking ...
0
votes
0answers
28 views

In DB2 , what steps need to be followed to improve bufferpool hit ratio for particular table? [closed]

I have read Scott's article on Bufferpool hit ratios and folly . I am more interested in experts views on steps they follow when the bufferpool hit ratio drops down, to say, 60%. Also, any specific ...
1
vote
1answer
162 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
6
votes
2answers
93 views

SQL perf issue - configure server correctly first or troubleshoot issues one by one?

This is for SQL Server 2012. I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see ...
3
votes
1answer
72 views

Cache query that is not stored in QueryCache

One of our customers has an online-shop running xt-commerce 4.2. This version of the e-commerce-software has a very expensive query for the category-tree. The query adds about 3 seconds delay and is ...
1
vote
1answer
147 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
3
votes
1answer
229 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
0
votes
1answer
45 views

What happens when the buffer pool size is insufficient in mysql

Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer ...
1
vote
1answer
23 views

Storing email inbox information

I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure ...
3
votes
0answers
156 views

Best procedure to measure query optimization effects?

I could not find valuable answers or explanations to the following question: How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps. ...
1
vote
1answer
118 views

Good encryption performance

I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow. I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table). ...
0
votes
0answers
20 views

Reporting Services Maintenance and Performance Best Practices

I'm simply looking for what most folks do to keep their SSRS servers in top shape. Beyond performing backups of the databases, symmetric key, custom extensions, configuration files, IIS settings, ...
0
votes
0answers
21 views

Example Database for tunning

I've been searching for so long and I've still have not found what I'm looking for ,is there any database or similar resource that people uses for tuning a database, or optimize queries?. I would be ...
18
votes
2answers
11k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
5
votes
2answers
144 views

reporting space consumption to files in filegroup - how to reverse-engineer the algorithm spreading data across files

I would like to know how to calculate the distribution of data consumed per file in a filegroup, back to the index (HEAP, CLUSTERED, NONCLUSTERED) storing it. My intention is to define which I/O goes ...
1
vote
1answer
63 views

Explain plan gives different results depending on schema

I have a query that joins a multi million row table to one containing hundreds of thousands of rows. Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both ...
-1
votes
1answer
50 views

Server settings with 36GB handling app+data

I'm setting up a server to handle a multi-site configuration on a beta application for a partner in a new business venture. Trying to assess sane values for MySQL. Currently I have set: ...
0
votes
0answers
18 views

What is Fastest way to export 400m rows DB2 table

I want to export table data in a file to load it in memcached. This table has 400m rows. Right now I am running db2 export command and it takes 1hr 10min. This table has 8 columns, 1 int, 2 timestamp ...
0
votes
0answers
9 views

PostgreSQL 8.2 giving inconsistant exexcution times

We are using PostgreSQL 8.2 and we are seeing a very strange behavior when we run queries in multiple threads. We are running the same query with 5 simultaneous threads for a period of 1 hour. When ...
0
votes
0answers
18 views

How can I improve this nested postgresql query?

I have a mildly complex query that is having rather poor performance: UPDATE web_pages SET state = 'fetching' WHERE web_pages.id = ( SELECT web_pages.id FROM ...
3
votes
1answer
78 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
1
vote
1answer
67 views

Same query plan, different data set, very different query duration SQL Server 2012

This may be a terrible question, because I'm not sure how information I can include to help. We have data segregated by customer. One customer apparently has higher volume of data. The same query ran ...
0
votes
1answer
134 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
2
votes
2answers
75 views

Filtering a result set on a very large table (using INCLUDE index in SQL Server)

Consider a very large table having hundreds of millions of rows defined as -- Here ObjectIdName and ObjectTypeName together are unique. -- Due to their size they aren't indexed, but rather they are --...
0
votes
1answer
66 views

Hashed and heap indexed object storage table insert and query performance

This question is partly to ask advice and assure this design is sane. I state the the desired properties upfront here, then go in more detail in the rationale and follow up with the design and example ...
1
vote
0answers
25 views

MYSQL Created_tmp_disk_tables value increases on almost every query

Should I be concerned that tmp_disk_table # is growing even when I run a simple query like elect id from table with 1000 rows where id is an integer and is primary key) I've fiddled with other ...
1
vote
0answers
58 views

sys.dm_db_index_physical_stats slow on first run after rebuilding indexes

I have a 10GB database in SQL Server 2016 RC1 on Windows Server 2012 R2 where I just performed many insert and delete operations. Afterwards, I ran: EXEC sp_MSforeachtable @command1="SET ...
1
vote
0answers
109 views

Basic PostgreSQL 9.x Performance Optimization Strategies for Reads/Analysis

Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries ...
0
votes
1answer
32 views

How to prevent locking and possible performance issue [closed]

I'm working on a website that lets users and admins to generate reports, and those reports require many database queries - they can take over one minute to complete. I noticed when these reports are ...
1
vote
2answers
98 views

Select the minimum of a calculated distance value without sorting

I have a query dealing with geo distances. The query is super fast, returning in roughly .1175 seconds on my 2.24 million row table. However, I only need the lowest distance, and using the built-in ...
38
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
4
votes
2answers
48 views

A lot of ad-hoc queries impact on server

We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc ...
0
votes
1answer
55 views

getting the total of amount of money of each user between two dates even if no record is found

I am trying to get the total amount of money which the user spent between a certain period. The code that I have come up with can successfully get the result but I also want to show the users who didn'...
2
votes
1answer
55 views

How to start indexing a new data warehouse

I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds. I plan to use an index tuning tool (...
0
votes
0answers
40 views

Improving slow queries, slow query log attached

I am running MariaDB under Cpanel and hosting Wordpress websites. My slow query log has reached ~400MB in size, and I would like to know how to eliminate the DB issues. Link to the slow query log. ...
0
votes
1answer
98 views

Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog?

I changed values for my Mysql parameters 1. innodb_flush_log_at_trx_commit=2(before it was 1) 2. sync_binlog=0(before it was 1) This has changed some of my parameters on AW RDS drastically ,here ...
2
votes
2answers
78 views

Performance improvement after table analyze

I used a function that contains 3 tables a,b, and c. Table a has an index ind1 on two columns. I call a stored procedure that contains code and gets data using the above function. I analyzed my ...
4
votes
1answer
101 views

How do I know that the high number of compilation/sec is because of memory pressure not bad queries?

My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of ...
3
votes
1answer
89 views

Can performance be improved by breaking databases up?

Performance is my over-riding concern, as I have a heavily recursive CTE that is the engine room of my application. This CTE may have to work on 4 columns of up to 7 million rows. Queries can take ...
1
vote
1answer
47 views

How can I 'further' improve my table and queries

This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query. I also changed my application so that unused filters weren't ...
2
votes
2answers
297 views

Speeding up insert in SQL Server

I have a stored procedure which inserts some records in a few tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to ...
0
votes
1answer
49 views

Retrieving most recent X rows for each given user from a table

We've got a seemingly simple query that's running extremely slowly, but I can't quite figure out the best solution to the problem. The scenario is as follows. We have an inbox table, which has the ...
0
votes
0answers
73 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
1
vote
1answer
49 views

Does varchar(n) affect the number of records which can be stored per page even if you store less than n?

I'm struggling to find enough documentation to help me answer my own question. The common story seems to be that it doesn't make a difference to actual database size when you use a much higher value ...