1

I have a table with 102 columns and 43200 rows. Id column is an identity column and 2 columns have an unique index.

When I just execute

Select * 
from MyTable

it takes almost 8 minutes+ over the network.

This table has a Status column which contains 1 or 0. If I select with where Status = 1, then I'm getting 31565 rows and the select is taking 6 minutes+. 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, it takes 1.43 minutes and returns 11568 rows.

How can I increase performance for completed and WIP status query separately or cumulatively? Can I somehow use caching?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahmad Suhail
  • 11
  • 1
  • 1
    to help get a better picture of what might be going on, can you include code that shows the data types of the id column and the unique index columns? or include the table definition altogether. – Pete Kelley Jul 23 '22 at 11:28
  • 4
    If your query is taking 8 minutes to return the data when you do a `select *` with no filters, then the problem is that your network bandwidth is too slow or the sheer amount of data is too large. There's nothing you can do in SQL Server to fix that. The solution is to *not* select all columns unless you actually need them. For example, if your table has `{id int, blob1 varbinary(max), blob2 varbinary(max), ..., blob100 vabinary(max)}`, and all you need for some application function is the id's, *don't* `select * from MyTable`, just `select id from MyTable`. – allmhuran Jul 23 '22 at 11:35
  • 2
    For performance questions, you must include current indexes, and please share the query plan via https://brentozar.com/pastetheplan. The question is not answerable otherwise – Charlieface Jul 24 '22 at 03:32

3 Answers3

0

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.

  1. RAM: Is there enough to hold the full table? And is the SQL server configured to use it?
  2. 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
  3. You said "8+ minutes through network". How long does it take on local execution? Maybe the network is slow
  4. 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

Wolf Donner
  • 105
  • 2
  • 12
  • 1
    Yes, in the server I've executed. It takes only 11 sec. But how can I decrease the loading time in network? Without paging is there any more ideas? – Ahmad Suhail Jul 23 '22 at 15:39
  • Okay here you got your answer. The network is the bottleneck. What you can do is decrease the amount of data transfered. Like allmhuran suggested you should not select all columns unless needed. If you really need all columns then you can only invest in better network hardware – Wolf Donner Jul 25 '22 at 16:19
  • Or maybe some more exotic solution would be a compressed SSH tunnel between server and client. When you route your SQL data over that tunnel you maybe(!) could save some network bandwidth. No guarantee though. And on the other hand it costs CPU – Wolf Donner Jul 25 '22 at 16:22
0

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

Pete Kelley
  • 3,713
  • 2
  • 16
  • 17
  • Here's ano old SO question - similar issue - https://stackoverflow.com/questions/3474865/is-there-a-performance-decrease-if-there-are-too-many-columns-in-a-table. I'm betting that you could improve your overall performance by normalizing the data. Another thing to look for is unnecessary overuse of BLOBs. – Pete Kelley Jul 23 '22 at 11:47
  • No there is no BLOB data type field. Varchar, Int and Date only used. – Ahmad Suhail Jul 23 '22 at 18:05
  • @AhmadSuhail It doesn't have to be blobs per se, just large columns. If you have dozens of `varchar` columns, like a few `varchar(10)`, some `varchar(100)`, maybe a `varchar(500)`, and so on, then every row can have a lot of bytes in it. Only select the columns that you actually *need* for what it is you want to do, *don't* select all columns using `select *`. It seems very unlikely that your application would ever need to use every single row and every single column at once. – allmhuran Jul 24 '22 at 01:04
  • @allmhuran I understood. I'm trying to optimize accordingly, InshaAllah will update here. – Ahmad Suhail Jul 24 '22 at 03:51
0

I Suggest create a non clustered index on "Status" Column. It improves your queries with Where Clause that uses this column.

Shahriar Khazaei
  • 321
  • 2
  • 7