Tag Info

New answers tagged

2

That's a lot of NTEXT columns. You should know NTEXT is deprecated and you should consider refactoring to the newer datatypes, eg NVARCHAR(MAX). Regarding your specific problem, depending on what version and edition ( eg select @@version ) of SQL Server you are using, you could consider partitioning. Here's a simple demo of this might work for you and my ...


0

I think @a_horse_with_no_name is right about that the index is updated once per statement, because if the statement has not completed its execution the data will not be visible since it is in transaction. And the definition of a statement includes having multiple values And accoriding to the docs here index creation/update works more efficient with batches ...


3

For consistency. There are times the access path will use only the data from the indexes or will start from the index and jump to the table. In both cases the informatoin on these two entities should be compatible. A index is just a separate ordered set of data from a table. When you remove an entry from the table, the entry on the index pointing to this ...


0

Maybe you think you are rebuilding but the index doesn't get rebuilt because the index isn't large enough. Have a look at this question Why index REBUILD does not reduce index fragmentatation? Did you check the fragmentation after rebuild? Is it actually defragmented.


7

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed. As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to ...


-1

You should look into several things: 1) Check the query plan, see if SQL Server is using the index. 2) How many records do you have in this table? If it's just a few million or even a few hundred million, then this query should be fast, if it's using the index. 3) Script out the index IX_ActCost_ScenarioID for CREATE and post its code here. See if ...


2

A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead): CREATE UNIQUE INDEX i -- Choose a better name! ON dbo.TrackerPositions (TrackerId, IdTrackerPosition DESC) INCLUDE (Position, [Date], Speed, NbSatellites, Direction, HDOP); The execution plan should look ...


3

Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker. But I really don't like the query from EF. Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"


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 ...


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 ...


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 ...


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.


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 ...


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.


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 ...


0

First thing you should do is Apply SQL Server 2008 R2 SP3 ASAP. This is because SQL Server 2008 R2 RTM version is not supported at all by Microsoft. There are lots of fixes which have been included in this Service pack release and that would surely benefit SQL Server query performance. You asked, So rebuild all indexes is good idea ? NO IT IS NOT. ...


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 ...


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 ...


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 ...


0

In your version of the query with forceseek, it looks like the majority of the query cost is located in the key lookup. You could eliminate this key lookup by creating a non clustered index on GridRunId and MancoId. Have a look at the estimated cost figure in the select part of your plans. The optimizer will chose the one with the lowest number. By adding an ...


0

Problem in the statistics of this query. SQL Server can't make right estimation. Look at [Project3] subquery. In that subquery you use two subqueries with "top 1" but you don't use "order by" clause. In this case no one can guarantee the same result on every run, even sql server. SQL Server don't know what result will be in [Limit1] subquery. Therefore it ...


0

It is possible to check if an index using a number of columns exists. WITH index_cols AS ( SELECT table_name, index_name, COLLECT(column_name) AS column_names FROM all_ind_columns WHERE table_name = '&table_name' GROUP BY table_name, index_name ), ...


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 ...


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 ...



Top 50 recent answers are included