Select Query Performace Issue

San 161

Dear Experts,

I have a table which is very slow . How can I improve the performance of the query.

Select * from Fact.Mgn_History (Total # of records in the table 2254281). It take around 15 mins when I just run a Select Statement without giving any condition.

There are few indexed in the table

1) IX_MGN - Clustered Index
2) Act_date - Non Clustered Index
3) ID - Non Clustered Index
4)Mgn_sk - Non Clustered Index

It will really help if something share tips to improve performance.

2 answers

Olaf Helper 44,311 Reputation points

2022-02-21T10:09:47.867+00:00

when I just run a Select Statement without giving any condition.

Then SQL Server always perform a full table scan and depending on data size it takes some time. Not to forget the network workload and client takes time to represent the amount of data. So which improvement do you expect here?
Please sign in to rate this answer.

1 person found this answer helpful.
Ronen Ariely 15,191 Reputation points

2022-02-21T10:27:23.313+00:00

Hi @San , there is not a lot we can do using the information you provided and for simple query like Select * from Fact.Mgn_History

This is pure scanning of the table

You can check if there are other actions which lead to waits and locks on this query.

You can check the system resources while running the query

You can check if you have columns which are not needed and you can remove them from the query (maybe using non-clustered index which cover these columns) - this will also improve the network as you will pass less data

think about working in batches and get the data in several requests

try to monitor network during the execution

...

San 161 Reputation points

2022-02-21T11:06:23.037+00:00

@Olaf Helper Please let me know what inform you needed.. how to check network load. Other table seems running fine. The improvement I want to retrieve data bit faster.

San 161 Reputation points

2022-02-21T11:09:05.997+00:00

@Ronen Ariely

1) think about working in batches and get the data in several requests '& try to monitor network during the execution- How to check this one?

2) You can check if there are other actions which lead to waits and locks on this query. & You can check the system resources while running the query - None is running now.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 110.4K Reputation points MVP

2022-02-21T23:07:23.053+00:00

How are you receiving the result?

What constrains the execution time in this case is not really SQL Server, but rather the client that receives the result.

And why are you bothering that about this? That is, what is your actual business need? Why do you want get all rows in the table?
Please sign in to rate this answer.
San 161 Reputation points

2022-02-24T09:43:03.043+00:00

@Erland Sommarskog Can I use a query which can give an idea how much time taken by each tables in the schema. (Means which one is taking longer time to execute)

Erland Sommarskog 110.4K Reputation points MVP

2022-02-24T22:34:22.367+00:00

Sorry, I am not sure that I understand. What do you mean with "time taken by each tables in the schema"? Tables don't take time. Queries may do.

And what is the relevance to your original question?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Select Query Performace Issue

2 answers

Your answer