Select Query Performace Issue

Question

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

Your answer

Answer 1

Olaf Helper 47,616

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?

Ronen Ariely 15,221 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.

Answer 2

Erland Sommarskog 133.5K MVP Volunteer Moderator

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?

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 133.5K Reputation points MVP Volunteer Moderator

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?

Share via

Select Query Performace Issue

2 answers

Your answer