Improve performance of a simple SQL query

Question

I have this SQL query:

SELECT COUNT(*) 
FROM Contract_Position 
WHERE Position_Type != 'MARKET'

Click on the link below to view the image of table of data -

Table of data

Imagine the scenario where there are now millions of records in Contract Position table, how would you enhance the performance of the above query?

Thanks for your help, S

Please use the following as a guide on how to post a good question: https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ Hint: data instead of images make a world of a difference... Also, for a question on performance, it would be beneficial to share the execution plan. Use the following tool for this: https://www.brentozar.com/pastetheplan/ — Eli, Aug 31 '17 at 14:16
You stated in the comments on one answer that is just a hypothetical question. What are you expecting for an answer? — Sean Lange, Aug 31 '17 at 14:23

score 3 · Answer 1 · answered Aug 31 '17 at 14:11

3

Have an index on column Position_Type.

answered Aug 31 '17 at 14:11

Georgi Raychev

Thanks. What type of index? – Satyen Gotecha Aug 31 '17 at 14:15
3

You need to share what indexes you already have... just adding an index blindly doesn't always answer the question – Eli Aug 31 '17 at 14:17

laxman kanhere · Answer 2 · 2017-08-31T14:30:10.407

1

Add user defined index on Position_Type column

edited Aug 31 '17 at 14:30

answered Aug 31 '17 at 14:14

laxman kanhere

That would be a Non clustered index? – Satyen Gotecha Aug 31 '17 at 14:16
3

You need to share what indexes you already have... just adding an index blindly doesn't always answer the question – Eli Aug 31 '17 at 14:17
Sorry, there are no indexes available, this is a hypothetical situation/question. The only data I have is on the image. Is there anything else I can provide to aid the your answer? – Satyen Gotecha Aug 31 '17 at 14:20
use below link cluster index only one in a table : https://stackoverflow.com/questions/91688/what-are-the-differences-between-a-clustered-and-a-non-clustered-index – laxman kanhere Aug 31 '17 at 14:21

score -1 · Answer 3 · answered Aug 31 '17 at 14:53

-1

If index are not sufficient, with for example MariaDB you can partition your table basing on some criterias.

answered Aug 31 '17 at 14:53

pinkra

The question is tagged SQL Server so MariaDB is not relevant. – Martin Smith Aug 31 '17 at 15:08
Which index is the best one to use - Clustered or Non-clustered – Satyen Gotecha Aug 31 '17 at 15:49
clustered are better for performance but it depends on what you have to do – pinkra Aug 31 '17 at 15:58
From the above I was advised to to introduce a index to improve the performance issue. Please advise – Satyen Gotecha Aug 31 '17 at 16:20
1

@SatyenGotecha No one can "improve" performance in a hypothetical example. So stop worrying and start doing. If you run into an actual performance problem, that is the time to address it. And note that nothing is free. You need to measure performance across all aspects of your usage. Tuning just one query can easily make others worse. If you are new to this, then you should consult with someone that has the appropriate abilities. – SMor Aug 31 '17 at 18:38

3 Answers3