Tag Info

Hot answers tagged

7

Out of the box, no. You'd need to hook into the event stream for extended events and then take action based on that. Tom Stringer has a good overview and sample code to do this!


4

I think the question as stated (on 2015-04-20, "Which collation [...]") is not what is meant, given that the accepted answer talks about encoding rather than collation. Let me answer the stated question rather than the intended one, just because I think it is interesting :-) Wikipedia says "Collation is the assembly of written information into a standard ...


4

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc). In other cases, a soft delete is not a performance ...


3

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will: Have the same effect on dead tuples - it physically re-writes the whole table Retains any existing indexes Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering ...


2

Here's something to test out. You can't force the order of execution in an OR, but you can in a CASE, so you can short-circuit extra processing if you put the most common scenario at the top of each CASE. EDIT: Since you aren't using any fields from orders (just testing that the order isn't deleted or in need of review), I moved that part to an EXISTS in ...


2

To get all unique pairs of elements from an array of arbitrary length: WITH a(a) AS (SELECT '{A,B,C,D}'::text[]) -- provide array here , i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i) SELECT ARRAY[a[i1.i], a[i2.i]] AS pair FROM i i1 JOIN i i2 ON i2 > i1 , a; You can then join to the message table. Without knowing any ...


2

QUERY #1 Each time you do an INSERT, you are doing this under the hood SET @sql = 'insert t select null'; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times. QUERY #2 Running insert t select null from(, you fully ...


2

After asking on pgsql-performance list, Jeff Janes figured out that the cause was associated to the default collation used by Postgres (see this link for more informations). MacMini was using the much performing collation while Dell T420 was using the en/US collation. T420 (Postgres 9.4.1) List of databases Name | ...


2

Assuming we are talking about 1:1 relationships among all tables. Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


1

If the data needs to be clean and relational before being presented in your UI, I'd use SSIS to handle reading, cleaning, inserting your data into SQL Server, handling rejects, and archiving the input files. If your data does not need to be clean/relational before presenting in the UI, then a NoSQL solution might work for you. By the way, Excel is horrible ...


1

I use something like this to create dynamic pivots with a dynamic number of columns. Perhaps it will help you with your solution. It's a single statement to avoid looping or cursors. declare @strSQL as varchar(max) declare @columns as varchar(max) declare @columns1 as varchar(max) set @columns = (select stuff ((select ',cast(' + quotename(columnname) + ' ...


1

Adding an example to Michael's answer. The problem with the PIVOT is two fold. 1st, it wants to aggregate. You can get around this by defining your dataset to be distinct and using MAX or MIN functions. But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question. You would end up with only one ...


1

You can put the table in KEEP Pool cache and this may have a positive impact on your queries. I have implemented it in my system for a 16 GB partition and we did get good performance gains. However putting a table in cache does not mean that its data is loaded into memory automatically. It only means that since we are using a separate buffer pool for these ...


1

It achieves the same effect as a VACUUM FULL except that it can be executed within a transaction block. Of course, any indices or constraints on the original table would be lost in the given example. I would need to know some additional information to help explain the performance increase: Are there any indices or constraints on the original table? Is ...


1

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report See this link for an explanation about the why and ...


1

There are so many 3rd party tools, most popular are MySQL Enterprise Monitor MONyog for DBAs Percona Toolkit Both MONyog and MySQL Enterprise Monitor shows CPU usage information in Linux systems However, if we tune certain parameters in my.cnf, high MySQL CPU usage may reduce innodb_read_io_threads innodb_write_io_threads innodb_log_buffer_size ...


1

Views are a good way to accomplish what you want to do. If your views take advantage of existing indexes or are 1:1 against the underlying tables, then queries against them will use the indexes. Since you're expecting periodic updates, you'll want to script the view creation, and in that same deployment script you can always add indexes if you need more. ...


1

Query You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN ...


1

You'll have to write your own, it's not a native feature of oracle, it's something that has been implemented in Oracle tools. Grab the plan from v$sql_plan for your SQL_ID and then grab the delta of session stats before and after you run your query. It's a little more involved in that, but that's the basic mechanics.


1

There is an art and a science to database design. The science is normalisation. If you're intent on learning about databases you will need a solid understanding of it. The art is in deciding just what exactly the "things" are that are going into your database. Defining them in a why that is comprehensive and precise is a skill to learn. In your ...


1

Upgrade to 5.6 and use multi-threaded replication. But... This is limited to different threads for different databases. Use Galera -- it does not have the tight coupling between threads and databases, so it does (in theory) a better job of parallel execution. Read this for more thoughts. Also, let's see some of the more naughty queries, plus associated ...


1

InnoDB Architecture (from Percona CTO Vadim Tkachenko) What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)? Increase innodb_log_buffer_size to 256M Increase innodb_log_file_size to 2G I wrote about increasing these to accommodate TEXT/BLOB fields in the past Apr 20, 2011 : MySQL ...


1

Q1. Is storing computed bitorderstring are good way or maybe let user wait a little? =) (I guess, result set will be much more than I tested) MySQL supports columns of type bit. These only use one bit each, in sets of 8, as you would expect. This will give the same disk and memory density as you're currently getting, but with much easier query ...


1

WHERE (1 << CVbits) & ConcVal AND CVbits != 0 -- (to "exclude the zero bit")


1

I have a simpler way to put it. "Batched INSERTs and LOAD DATA run 10 times as fast as single-row INSERTs." By "batching", I mean INSERT INTO t (a,b) VALUES (1,2), (2,3), .... The optimal number is between 100 and 1000 rows per INSERT. Beyond that, you get into diminishing returns. Here are some issues that impact performance, especially for Batched ...



Only top voted, non community-wiki answers of a minimum length are eligible