Hot answers tagged

12

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here. In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID ...


8

You actually have 595,947 matching rows, which is about 3% of your data. So the cost of the lookup adds up quickly. Suppose you have 100 rows per page in your table, that's 200,000 pages to read in a table scan. That's a lot cheaper than doing 595,947 lookups. With the GROUP BY clause in the question, I think you'll be better off with a composite key on ...


8

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged). In this case if you were to create the temp table with an unnamed primary key ...


6

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. I want to ...


5

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index. For instance: CREATE INDEX IX_temp ON dbo.SomeTable(SomeKey) WHERE SomeKey IS NULL; DROP INDEX dbo.SomeTable.IX_temp; I've created a script to ...


5

The field in your WHERE condition is not the leading field of the index. You have measure defined as NVARCHAR so prefix the literal with an N: where Measure = N'FinanceFICOScore'. Consider creating a Clustered Index on SnapshotKey. If it is unique then it can be a PK (and Clustered). If not unique then it cannot be a PK, but can still be a non-unique ...


5

Let us consider a dataset with N items, of which P items match the predicate and will be returned by the query. 1a.A linear search, unsorted data. There is no way for the algorithm to know whether a value will match the predicate until that value is read. Therefore the only solution is to read all values and ignore those which do not match the predicate. ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


4

Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.


4

As this database will only be used for reporting, we are thinking of shrinking the database. A one time sensible shrink is OK. Disk space is cheap ! Increases the index fragmentation is the side effect of doing a shrink operation. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity. Shrink database ...


3

I think you will find that if you use an SSIS package to move your data between multiple servers @RobFarley's suggestion will work just fine. You are transmitting information across the network either way. In one case a backup file and in the other the data. However if you are absolutely against the idea you can use a combination of the two ideas. ...


3

Based on the information you have provided, here is my suggestion on how to get good performance when querying the latest users that have cast a vote on a given sub item (performance is assumed to be your goal, given that you are asking about what indexes to add.) Create the following table: CREATE TABLE vote_sub_item_cast_uncast ( vote_sub_item_id INT ...


3

For this WHERE clause, I would try an index on (customer_account_id, sync_inactive, last_modified, id): ALTER TABLE main_contactinfo ADD INDEX customer_active_last_modified ( customer_account_id, sync_inactive, last_modified, id ) ; The ORDER BY with e small LIMIT complicates things though, so a different order of the columns in the ...


3

The SRID does not appear to be an integral part of the spatial index nor can it be added. It is integral to the geometry even though there are no real tools for manipulating geometries based on the SRIDs in SQL Server. It does have a few rules about SRIDs. Geographies must have a SRID from a list of specific SRIDs. When comparing, manipulating geometries ...


3

Where a filtered index on a computed column is too limited, you have the option of creating an indexed view. The indexed view is maintained automatically by the database, so you do not need to worry about getting trigger logic correct for all possible DML operations. You also do not have to worry about complicated correctness problems under high ...


3

some say, cluster index stores the whole records in deepest leaf of index. Yes this is correct. The leaf pages of the clustered index contain the actual table rows. This is the actual data. There is no other primary copy of it held elsewhere. The leaf pages also allocate a few bytes to hold the address (file and page) of the next and previous pages in ...


2

With a Cassandra index (i.e. a "secondary index", as opposed to primary keys), each node has to query its own local data for responding to a query (see the Cassandra secondary indexexes FAQ). These index are also built using a background process. This backgrounding means that the index may return false negatives in terms of hits (or false positives in ...


2

Just going by the MySQL Documentation, I would say yes. According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly ...


2

I thought about adding a computed column like this: ALTER TABLE dbo.mytable ADD Diff AS InboundQuantity - OutboundQuantity PERSISTED ; go CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity) WHERE Diff <> 0 ; but that does not work. This is a bit of a kludge, ...


2

In order to make this blisteringly fast, what I would do is the following - with the proviso that I know nothing about your server RAM, CPU or disk config. Bear in mind that a decade's records will be 35M/year - so 350M for a decade - most servers should be able to cope with a small table like this - but I don't know what else is happening on your server. ...


2

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages. This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to ...


2

FWIW, I tried investigating the differences between a newly created index in a db copy and the original index in syscat.indexes, sysibm.sysindexes and sysstat.indexes. The only difference I could spot was a slightly better density (100) for the new index compared with the old one (87). I therefore ended up recreating the index, and now the execution plan is ...


2

Given your description I strongly suggest against using MongoDB. Not because it would necessarily a bad choice (although I believe it is in your case for reasons other than pure technical ones). Here are the points that caught my eye. Data modeling Trying to use MongoDB with relational data model with no adaptations almost always leads to tears and misery ...


2

This depends on so much stuff. What are you building and how complex will your DB queries be? Hobby or professional use? Is the data critical? Do you need transaction safety? Do you already have all the data, in what format? or do you start from scratch? how many users do you expect? how many requests? Are you bound to PHP or can you use Node.JS? Is it a ...


2

Hash indexes are missing in action in PostgreSQL. PostgreSQL knows it needs hash indexes, and that it's code for hash indexes is old and moldy, but they don't remove it because they are waiting for someone to come along and overhaul hash indexing. See this thread: http://www.postgresql.org/message-id/4407.1115698257@sss.pgh.pa.us


2

I deem that there are multiple (very important) aspects that you need to consider before deciding which tool you are going to employ to develop your project. The primary objective should be to manage the pertinent data as it is, a quite valuable organizational asset, and a reliable manner to achieve said objective is by way of technical means that are ...


1

I don't know about a best but at a small number there is no real value. There is some overhead to using the index. Yes an index seek is faster than a table scan but there is some overhead to using the index. Index maintenance clearly has overhead. If a table has a PK then you should use that as a PK and typically clustered. Consider a table of ...


1

There is no reason to not use an index. If the table is small, the cost estimator can easily deduce that a sequential scan has less overhead than an index scan followed by random access into the table data, because the index accesses are associated with a cost as well, and sequential scans also have lower cost than random access. The only downside is that ...


1

As others have said, number of rows is no guide. Find a query that is typical for your system and execute it in SSMS with io on: set statistics io on; select * from SomeTable order by SomeColumn; Look on the Messages tab, at the "logical reads" counter. This is the number of pages that need to be read to satisfy the query. (The "logical" means it does not ...


1

I have noticed the same thing in last week. Sometimes, the mongo shell returns any empty array: Try running the currentOp() many times to get the progress. Use use db command and then perform db.currentOp()



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