Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

0
votes
1answer
27 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 ...
-2
votes
3answers
138 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
34 views

Regarding Analyze table then performance improve

I used function which contain 3 table like a,b,c and table a contain index like ind1 on two column. Now I call store procedure which contain code and also get data from above function. And I also ...
32
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 ...
0
votes
0answers
40 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
139 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 ...
1
vote
1answer
42 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
54 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 ...
1
vote
1answer
210 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 = ...
4
votes
3answers
78 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 ...
2
votes
1answer
200 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
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
15 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
41 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
11 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 ...
14
votes
4answers
790 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
1answer
155 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 ------------ ... ...
2
votes
2answers
199 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
561 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
157 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 ...
1
vote
0answers
32 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
173 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
38 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 ...
3
votes
2answers
96 views

Release MySQL Memory and Performance Tunning

I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file. The server is a dedicated server for MySQL database and the ...
2
votes
1answer
69 views

Stack Dumping and performance degradation of sql server process [closed]

I would like to say that today i got first time SQL Server log message in My SQL Server 2012 Database. ***Stack Dump being sent to D:\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0012.txt * ...
0
votes
2answers
54 views

MySQL Change in execution plan after upgrade

We upgraded the MySQL 5.0 to MySQL 5.6 last week. After the upgrade most of the DMLs are very slow. Especially the inserts and updates using JOINs. There is a difference in execution plan of these ...
0
votes
2answers
39 views

Optimal database structure for fast inserts

I am using MySQL Percona server. This table needs to do fast bulk inserts using 'INSERT INTO ... VALUES' for smaller requests (about 100k records) and using 'LOAD DATA INFILE' for larger (up to 10kk ...
0
votes
2answers
46 views

More Efficient Use of Union

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list ...
1
vote
0answers
89 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
3
votes
3answers
190 views

Simple yet problematic update query

I have a rather simple update/query, that has been causing me a lot of grief over the years. in simplest form it is : update VillageSemaphore set TimeStamp = getdate() where VillageID in ...
0
votes
1answer
151 views

MySQL: How turning off the logging affect the performance?

OS: RHEL5 DB: MySQL 5.1.73 community edition DB size: ~1GB I have setup a testing environment and turned off all logging - general logs, binary logs, slow query logs etc. I am using Oracle ...
0
votes
0answers
354 views

MySQL 5.7 InnoDB is extremely slow on spatial data compared to MySQL 5.6

I have a table area which contains ~300,000 rows. It is defined as follows: CREATE TABLE `area` ( `area_id` bigint(20) NOT NULL AUTO_INCREMENT, `boundary` geometry NOT NULL, `name` varchar(255) ...
0
votes
1answer
194 views

Scale MySQL trigger for thousands of inserts per seconds

We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256) A row ...
0
votes
0answers
14 views

server response is getting slow day by day

MySQL server 5.5 > running in Linux Box is getting slow. MySQL server is being used by Java Web application, please help me to figure out the problem.
0
votes
1answer
37 views

Tuning read-intensive queries - Oracle

Now this might be very vague but Im sure I can provide more info on them.. my query is that is it recommended to use tablespaces if different block size because I have a table in which number of reads ...
3
votes
3answers
253 views

Can I find out which database is queried on the most?

We have a number of database which all have the same schema for each customer. I was wondering if there was a query which could select count(*),db_name from somesysview group by db_name; From a ...
0
votes
0answers
47 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
0
votes
1answer
60 views

Azure TempDB and Log files

I'm setting up an Azure D11 SQL Server to be used for a data warehouse. I can only attach 4 data disks and have done so, now I need to determine how to configure them and split the DB files. I'm ...
1
vote
0answers
76 views

Mongodb Huge Number of Open Connections and Write Locks

We are seeing around 800 open connections to our Mongodb. To clarify our issue: We have 5 different clients who write the data into Mongodb. Each of these clients - write four different type of ...
0
votes
0answers
29 views

Minimum Oracle table size making IOT advisable?

I have inherited an IOT that doesn't seem to merit it because: only 25k rows fairly pure join table, non-index fields rarely queried total index width only ~125 bytes total row width only ~160 bytes ...
0
votes
0answers
90 views

Why my mariadb slow when I select all from empty table?

I try to find why my database slow when I select all table . That's empty table... MariaDB [kohadata]> select count(*) from biblio; +----------+ | count(*) | +----------+ | 0 | +----------+ ...
1
vote
0answers
41 views

Howto measure improvement after create and move new filegroups in SQL Server [closed]

How can one measure the (hopefully) positive effect of moving couples of indexes to a different filegroup, so to say seperate them from the primary data filegroup, and move this filegroup to another ...
5
votes
2answers
2k views

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the ...
2
votes
2answers
58 views

Can moving tables to another MySQL database help performance?

I want to know if I using different databases can help performance. I have one big table support_chat with more than 5 million rows. I need to scan this all the time (3s with ajax) to check new ...
1
vote
0answers
52 views

Mariadb improve performance while import dumpfile

I have a dumpfile with millions data inside. I want to decrease time because I waited too long. I imported 10 million data (3 GB) and I waited 1:30 hours. I will import nearly 100 million data (30 GB) ...
0
votes
2answers
1k views

Optimize MySQL query_cache_min_res_unit

My query cache settings are the following: mysql> SHOW GLOBAL STATUS LIKE 'Q%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ ...
0
votes
1answer
46 views

Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow

I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA ...
11
votes
2answers
282 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
8
votes
1answer
203 views

Does RSS Profile have an effect on SQL Server with NUMA setup?

I'm curious whether the RSS Profile which is default NUMAStatic on Windows 2012 R2 could/should be changed for SQL Server with a NUMA setup. Has anyone done performance benchmarks on high load SQL ...