Click here to Skip to main content
14,387,050 members
Rate this:
Please Sign up or sign in to vote.
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.
Updated 4-Nov-19 3:11am
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]

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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100