Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

2
votes
1answer
217 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 ...
1
vote
1answer
122 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' ...
-1
votes
1answer
41 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
11 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
7 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
35 views

which approach gives better performance ? using while or foreach in sql data [closed]

I have a question regarding performance , our code fetch data from remote server and do calculation and generatete csv file and then zip all the csv ; it took too much time. can you throw some ...
0
votes
0answers
16 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
525 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 ------------ ... ...
3
votes
1answer
61 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
49 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
133 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 ...
1
vote
1answer
254 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 = ...
2
votes
2answers
65 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
60 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
24 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 ...
17
votes
2answers
10k 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 ...
1
vote
0answers
49 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
89 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 ...
1
vote
1answer
154 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 ...
0
votes
1answer
30 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
65 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 ...
36
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 ...
5
votes
2answers
131 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 ...
4
votes
2answers
46 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
49 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 ...
2
votes
1answer
50 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
35 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
73 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
77 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
98 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
45 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
254 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
45 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
63 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
47 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 ...
1
vote
0answers
92 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
4
votes
3answers
143 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
1
vote
0answers
15 views

how columns ordering in sql server database table impact performance? [duplicate]

As I read about database design from logical model to physical databases, I noticed that the column ordering may need to be changed the sequence recorded in the logical data model. The DBMS is SQL ...
0
votes
0answers
17 views

Difference between execution times [duplicate]

I managed to make a more efficient my query disabling the sequential scan, but the time in the explain analyze is really different from the real execution of the query, "Execution time: 4854.186 ms" ...
0
votes
0answers
45 views

Reducing the time when retrieving all records from large databases

I have to retrieve most of the records from large table, I'm talking about approximately 3 millions rows. I'm even using this site as a guide for tuning the database. It is not enough, it takes an ...
0
votes
0answers
38 views

Why dropping TokuDB partition so slow

I have defined a simple time series table partitioned by UNIX timestamp (into 32 days). I then loaded ~17K entries, which were distributed among 8 partitions. There were at most 2880 rows in any ...
15
votes
4answers
1k views

Should the index on an identity column be nonclustered?

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column? The reason is other indexes will be created for queries. A query which uses a ...
1
vote
1answer
2k views

How to change page size in an existing sybase database

I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page ...
3
votes
2answers
271 views

Are scripts like sp_BlitzIndex and the others accurate on restored backups?

I am a developer trying to get a better grasp of things. I am wondering if using the scripts from BrentOzar.com will work on restored backups? For example if I have the DBA give a backup of ...
4
votes
1answer
587 views

Do I have a memory issue?

I ran sp_Blitz that pointed me to a memory issue saying: Memory Dangerously Low The server has 32755 megabytes of physical memory, but only 235 megabytes are available. As the server runs ...
7
votes
2answers
216 views

How to measure Key Lookups per Second on SQL Server

I am working on reducing the amount of transactions doing key lookups on our SQL Servers. First I want to define a baseline for the normal or tolerable number on that metric and as we constantly ...
2
votes
0answers
45 views

SQL Server Trace Flag 834 on VMware

It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware? The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't ...
-1
votes
4answers
194 views

What MAXDOP setting should be used for MS SQL Server 2012? [closed]

I am getting a performance degradation error in one of my cluster production server. The error is like that A significant part of sql server process memory has been paged out. This may result in ...
4
votes
1answer
44 views

Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked ...