I have a table with 102 column. It has 43200 rows. Id column is identity column and 2 column has unique index. When I just execute "Select * from MyTable" it takes almost 8min through network. This table as a "Status" column which contains 1 or 0. If I select with where Status = 1 then getting 31565 number of rows and taking 6min . For your information status 1 completed and will not change ever anymore. But 0 status is working in progress and the rows are changing different columns value by different user stage. When I select with Status = 0 the it takes 1.43min and returns 11568 rows.
How can I increase performance for completed and WIP status query separately or cumulatively? Do I can use cashes?
CodePudding user response：
The SQL server takes care of caching. At least as long as there is enough free RAM. When it take so long to get the data at first you need to find the bottleneck.
- RAM: Is there enough to hold the full table? And is the SQL server configured to use it?
- Is there an upper limit to RAM usage? If not SQL server assumes unlimited RAM and this will often end caching in page file, which causes massive slow downs
- You said "8 minutes through network". How long does it take on local execution? Maybe the network is slow
- Hard drive: When the table is too big to be held in RAM it gets read from hard drive. HDDs are somewhat slow. Maybe defragmenting the indices could help here (at least somewhat)
If none helps, the SQL profiler might help to show you where the bottleneck actually is to find
CodePudding user response：
This is an interesting question, but it's a little open-ended, more info is needed.
I totally agree with allmhuran's comment that maybe you shouldn't be using "select * ..." for a large table. (It could in fact be posted as an answer, it deserves upvotes).
I suspect there may be design issues - Are you using BLOB's? Is the data at least partially normalized? ref https://en.wikipedia.org/wiki/Database_normalization