How make select run faster for a table with 120 columns

Question

0.00/5 (No votes)

See more:

I have a select statement, in which three tables are being inner joined and one of the table has 120 columns in it and almost all of the columns are being returned in the select statement from this big table. The execution plan shows clustered index seek only for all the tables. Looking for any out of the box ideas to make the select work more faster.

What I have tried:

I applied page compression for the big table and it yielded 10 seconds improvement, but still i want to tune it further.

Posted 4-Nov-19 0:06am

dinesh42

Updated 4-Nov-19 2:11am

Add a Solution

Comments

F-ES Sitecore 4-Nov-19 6:10am

"It depends". Is the query slow because these 120 columns have huge amounts of text? Or is the query slow because the number of rows being queried and the column count is irrelevant? Is the query slow because you are returning a lot of rows? Each of these have different causes and solutions, there is no magic bullet that makes queries faster, some of these issues have no real solution except to use a faster server. You have to first identify the cause of a bottle neck before you can tackle it. Google for speeding up sql queries to get an idea for the basics.

Maciej Los 4-Nov-19 7:39am

Sounds like an answer ;)

Dave Kreskowiak 4-Nov-19 8:05am

A lot of the performance is going to come from the design of the tables and what you're doing in the query. We can't tell you anything about this because you haven't described any of it nor shown an actual query that is performing badly.

For example, if you put a CAST in a WHERE clause, the CAST has to execute on every single row in a table. I don't think this is the case because you mentioned the plan has nothing but indexed seeks. This was just an example.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2019-11-04T02:11:00

You could try to Create Filtered Indexes - SQL Server | Microsoft Docs[^]
this will only be of benefit however if columns only have a small number of relevant values for queries.
Also make sure you indexes are maintained, here is a nice tool for that: free-tools-for-sql-server~sql-index-manager[^]

If this is still not enough, you might think of using a more performant database, see this overview: relational-databases[^]