Tag Info

Hot answers tagged

6

I think the script in question is sp_BlitzIndex. The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question: Is it valid for those columns to all really contain nulls? When tables are ...


5

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation. This created an artificial limit on the number of tuples which could be sorted in memory. Once that limit was reached, it had to switch to a disk sort, even if there was memory left over. This restriction was removed in version 9.4.


4

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten That's not the case. In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential ...


4

Even though you fixed the immediate rounding issue, the overall algorithm to get per-object/index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data. The following is an adaptation of the code I ...


4

You're dividing by INT so you'll only ever get a whole number answer. You therefore end up with a rounding problem on your own Space calculations. This is why, when you sum them together, you get a different answer. Although the difference is minimal this is one of those key 'gotchas' with handling non-whole numbers in SQL Server. Change your partition ...


3

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition: ... PRIMARY KEY CLUSTERED ([Timestamp], [Id]) If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that ...


3

There a number of approaches to tuning XML queries in SQL Server. Property promotion is a good one, but I also regularly use the following: XML Indexes Best practice syntax XML Schema Collection Full-text Indexing XML Indexes XML Indexes can transform XML query performance, but at a cost. Pre-SQL Server 2012, they come in two types, primary XML ...


3

You should have a look at Property Promotion in XML Best Practices for Microsoft SQL Server 2005. Create a user defined scalar valued function that extracts the value you need and use the function as a computed column in your table. You can persist the column of you like and you can create a regular index on the column. You don't have to persist the column ...


3

Try this select a.Date, a.Foo, b.Bar from TableA a LEFT JOIN TableB b ON a.Date = b.Date UNION select b.Date, a.Foo, b.Bar from TableB b LEFT JOIN TableA a ON a.Date = b.Date Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left ...


3

The reason why the index on search_id is not even considered is because the two tables - and therefore the two search_id columns - have different character sets. One has CHARSET = latin1 and the other CHARSET = utf8. The types are the same varchar(100) but charsets differ and that matters. Columns that are used in joins or comparisons should have identical ...


3

The optimizer makes choices based on costing estimates. The cost model is generic, and may not always choose optimal plans for your particular hardware, and its assumptions may not always be valid for your circumstances. In this case, the optimizer assesses a hash join as the cheaper option over nested loops when the estimated number of rows to be joined is ...


3

This Script contain few columns 1. UserSeek : Number of seeks by user queries 2. UserScans: Number of scans by user queries 3. UserLookups: Number of bookmark lookups by user queries 4. UserUpdates: Number of updates by user queries on the basis these column stats you can figure out either index is useful or not. Actually Index are ...


3

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish. Killed the transaction, and now the indexes are back to normal.


2

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately. This can be done to the clipboard and pasted to the query window generated by the script table as command. I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options ...


2

I think you are already on the right track here - and I encourage you to keep experimenting with different options and see for yourself what the differences to performance are. But, here are my thoughts below: Columnstore Indexes These perform really well when you are genuinely interested in all (or most) of the data in a column. If you are keen to see the ...


2

You can use the admin command: db.adminCommand({setParameter:1, ttlMonitorEnabled:false}); To re-enable, use the same command with true.


2

You are trying to execute a semijoin. This is a very well know issue with the MySQL optimizer before MySQL 5.6. The only way MySQL knows how to execute it is to perform a full table scan on the left table and execute the inner query once per row, thus it is unable to use the index. You have several alternatives: Migrate to 5.6 (or MariaDB 5.5): it will be ...


2

1. Table definition Better: CREATE TABLE test( test_id serial PRIMARY KEY, age INT, name text, data JSONB ); Due to alignment requirements of the types integer and varchar / text it's better to put the two int columns first and together. More: Configuring PostgreSQL for read performance Also, "age" is a dubious column to begin with. Normally ...


1

This is your original query SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName LIKE '%castname%' There are three things you can do SUGGESTION #1 You should reorganize the query so that the castName is searched first SELECT DISTINCT ...


1

I'm not sure what specific problem the script is trying to point out, but no, it is not best practice to unilaterally prevent all columns from allowing nulls. Allow NULLs when you need them, and don't allow them when you don't. There is no right answer to this; it all depends on how you are using the data and how likely it is that you'll have all of the ...


1

I wrote some posts about RAM disk and temp tables Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk? Jan 17, 2012 : How can I optimize my mysql setup to create my index faster? Sep 23, 2011 : skip copying to tmp table on disk mysql The basic idea would be to set set tmp_table_size and max_heap_table_size to the ...


1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko) When you update an indexed column, changes must migrate through this architecture as follows: From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1 From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file) If you are ...


1

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object. See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view." So materialized ...



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