-2

Query:

select * from dbo.ResultsStored order by SearchSerial desc, PercentMatch desc

Indexes:

CREATE clustered INDEX ClustIndex_Sno
ON ResultsStored (SearchSerial)

create nonclustered index nonclustInd_RowId_Percent
ON ResultsStored(RowId) Include (PercentMatch)

Execution Plan:

Execution Plan

As it is resulting into index scan, may be I can manage to get it somehow to index seeks? Can I please get some pointers on this?

The table is going to have data in pretty large amounts , somewhere around 100k rows.Out of all the queries, it is the query with quite bad performance. Is there anything else I can do to improve its performance, please?

James Z
  • 12,209
  • 10
  • 24
  • 44
Simran
  • 539
  • 2
  • 8
  • 28
  • 1
    if you will have lots of data (as you state) and are selecting the entire table (no where clause), expect it to take a long time. – KM. Feb 19 '15 at 20:36
  • In addition to KM above, do you need every column? – Sean Lange Feb 19 '15 at 20:37
  • @SeanLange: There are just 4 columns in the table and yes those are needed. – Simran Feb 19 '15 at 20:38
  • 2
    You aren't going to get seeks here because you are selecting every single row. There is no seeking for the rows to return because it returns them all. – Sean Lange Feb 19 '15 at 20:38
  • 1
    Not an answer to your question but you should name the columns instead of using *. – Sean Lange Feb 19 '15 at 20:39
  • 1
    You're selecting every row and every column. You're not going to avoid a scan. What you need is an index that supports your ORDER BY (or you need to perform your sorting elsewhere). – Aaron Bertrand Feb 19 '15 at 20:40
  • @SeanLange: Thank you, I will keep that in mind but is there anything else I can do here to improve the performance at all? – Simran Feb 19 '15 at 20:40
  • @Simran, try replacing * by columns you really need, and look for covered index – Giorgi Nakeuri Feb 19 '15 at 20:41
  • 1
    Not really. You are selecting hundreds of thousands of rows. It is not going to be fast. Do you really need every row? – Sean Lange Feb 19 '15 at 20:41
  • @AaronBertrand: I have a non clustered index on the columns in Order By clause . Does that not help? What else I can possibly do to improve that? – Simran Feb 19 '15 at 20:42
  • @SeanLange: Yes, all of the rows are to be fetched :) – Simran Feb 19 '15 at 20:43
  • what are you doing with that much data in your application? – KM. Feb 19 '15 at 20:46
  • @KM. a lakh is only 100,000 so this is really just several hundred thousand rows. Hardly what I would call a massive amount of data by current standards. – Sean Lange Feb 19 '15 at 20:48
  • 2
    Your index is sorted on SearchSerial. Your order by is on SearchSerial DESC, PercentMatch. SQL Server has no choice but to sort the data. Consider changing the clustered index to match the order by clause, creating another index to facilitate that, sorting elsewhere, paging, or conceding that without structural changes this query will never be fast. – Aaron Bertrand Feb 19 '15 at 20:49
  • @KM: Basically working on an application that is supposed to merge records from different hospitals. Now, before I query all the records from merged databases of all hospitals, I will be checking in results StoredTable if that particular query has already been performed on the collective database. so it kind of acts like a Cache. May be we will cut it out later but not at the moment. – Simran Feb 19 '15 at 20:50
  • No you don't have an index on all the columns in the sort. Those seem like some odd indexes to me. Include (PercentMatch) is not an index on PercentMatch. – paparazzo Feb 19 '15 at 20:52
  • @Blam: I changed it to create nonclustered index nonclustInd_RowId_Percent ON ResultsStored(RowId, PercentMatch) . Does that make it any easier for SQL server to sort? – Simran Feb 19 '15 at 20:56
  • What is so hard about this? Why do you think creating an index on RowID is going to help a sort that does not include RowID? – paparazzo Feb 19 '15 at 21:00
  • @Blam: As I have mentioned in question above, i already have a clustered index on SearchSerial column which is in OrderBy clause and now a non clustered index on PercentMatch Column as well which is also used in orderBy Clause. – Simran Feb 19 '15 at 21:03
  • 1
    For the second time NO you don't have an index on PercentMatch. Include (PercentMatch) is NOT and index on PercentMatch. – paparazzo Feb 19 '15 at 21:07

3 Answers3

1

I think you need to back up a few steps and ask yourself, "Do I really need to pull back every single row from this table?" You've used a SELECT * , so there isn't a way to get an index seek using this method. Revisit your requirements and determine if you need every single attribute and/or record from the table or if you only want to back into a specific subset of data. Adding a WHERE clause that uses an indexed field can greatly increase performance due to the index and a smaller data set. If this is not the case, consider why you are using an order by. If you only need to look at the top 1000 rows in order, you can greatly decrease the data set size by using a TOP. Provide a little more information as to the purpose of querying your data like this and perhaps we can give you a better opportunity at a solution you like.

SQL Taylor
  • 138
  • 11
1
CREATE NONCLUSTERED INDEX IX_SearchSerial_PercentMatch 
    ON ResultsStored (SearchSerial desc, PercentMatch desc);
paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • Thanks Blam. Two comments back, I mentioned that I have updated it.I removed PercentMatch from Include and added it to the Nonclustered index. Now, There is a clustered index on SearchSerial and non clustered index on Percentmatch. Do I still need to add SearchSerial to non clustered index ? Despite of having a clustered one on it is my question. – Simran Feb 19 '15 at 21:13
  • No I suspect that what you have done is optimal. I learn from you. – paparazzo Feb 19 '15 at 21:34
-1

I'd like to reiterate that you should really have a very specific where clause using the indexed fields if at all possible. It also helps to only select the fields you actually need and use the with (nolock) hint like this:

select * from dbo.ResultsStored with (nolock) order by SearchSerial desc, PercentMatch desc

bcvickers
  • 11
  • 1
  • 4
    Why would you suggest using NOLOCK here? That hint is NOT magic go fast button. It is however a magic "give me results faster and disregard accuracy" button. – Sean Lange Feb 19 '15 at 20:42
  • 1
    Agreed. NOLOCK will not make reading, transferring and rendering hundreds of thousands of rows faster. Please read: http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Aaron Bertrand Feb 19 '15 at 20:52
  • @SeanLange Disregard accuracy is a little harsh. – paparazzo Feb 19 '15 at 21:51
  • @Blam really? That is what that hint does. It reads uncommitted transactions. It can and will return duplicate and/or missing rows. That hint can retrieve data faster because it doesn't have to wait on locks. The cost of that is the accuracy of the results. For summary data or reports it might be ok but when accuracy is important it is an absolute no go. – Sean Lange Feb 20 '15 at 14:08
  • @SeanLange Nolock does a few things. For one it does not take shared lock. A shared lock will block a write lock. If I get stale data because an update could not get a lock then I got stale data. I use no lock a lot for a high volume application and I don't disregard accuracy. – paparazzo Feb 20 '15 at 14:26
  • Well using nolock means your data is not as accurate as it could be. Correct it doesn't take a shared lock. For high volume it may very well be perfectly fine. The real point of my comment was that it is not a good idea to suggest using nolock when performance is the problem. That hint has some very serious side effects that many people don't understand. Blindly suggesting its usage is not a good approach in my opinion. – Sean Lange Feb 20 '15 at 14:34