Slow Query when add 1 additional field

Question

Slow Query when add 1 additional field

detanto

SSC Enthusiast

Points: 174
More actions
April 10, 2020 at 10:28 am

#3741948

dear all,
i have simple query that related several tables which 1 table have millions of rows. i have created index on each table that i will relate to master table. and it went pretty well, from 1 minute query that produce 400 thousands row, after add index it will only takes 30 seconds. Problem came when i add 1 field on the select statement, it will takes 3 or 5 minutes to show exact same rows count.
question :
1. should i put the field in the index? or do you have any other suggestions?
2. where can i find best practices in creating index. to be honest, i'm still confused when using query plan from sql server management studio
3. why new SSMS cannot show diagram anymore? is it true that microsoft will eliminate the feature?
thx

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

The first thing you should do it look at differences in the execution plans.

Did you just add the column to the rows that are being selected and not add it to the WHERE clause? If so you probably need to just INCLUDE the column in the index.

detanto SSC Enthusiast Points: 174 More actions · Answer 2

detanto

SSC Enthusiast

Points: 174

April 10, 2020 at 11:05 am

#3741952

i add in the row, not in the where clause

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

You should check the which index, on the table that has this column, is being used (from the execution plan), then add the column you have added to the INCLUDE columns of the index.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

detanto wrote:

dear all,
i have simple query that related several tables which 1 table have millions of rows. i have created index on each table that i will relate to master table. and it went pretty well, from 1 minute query that produce 400 thousands row, after add index it will only takes 30 seconds. Problem came when i add 1 field on the select statement, it will takes 3 or 5 minutes to show exact same rows count.
question :
1. should i put the field in the index? or do you have any other suggestions?
2. where can i find best practices in creating index. to be honest, i'm still confused when using query plan from sql server management studio
3. why new SSMS cannot show diagram anymore? is it true that microsoft will eliminate the feature?
thx

If you are returning 400,000 rows to the screen, you can expect that to take a bit even in the presence of perfect indexes. Why on Earth anyone would return 400,000 rows to the screen would be my first question on this matter.

For item #2, the best all-in-one reference on the subject can be found at the following link. It's one of those things that you really should not only read cover to cover but actually use it as if it were a course book for deep study and as a handy reference even after you think you're really good at reading execution plans. Grant Fritchey is the author and he did an incredible job on this book.

https://www.sqlservercentral.com/books/sql-server-execution-plans-second-edition-by-grant-fritchey

For Item #3, the later versions of SSMS 17 don't have SQL Diagrams because the "geniuses" at MS thought no one used them or there were better tools or no longer wanted to support it or whatever. They brought it back in the later versions of SSMS 18 because the people that do use it (I'm one of them) raised holy hell with MS about it. So, if you don't have it on your version, download and install the latest version or do an update of your current version.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

To see more about what Jonathan's suggesting maybe have a look at this page:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

It could be looked at like there're 2 ways to "include" columns in an index: 1) as an element of the index key itself, and 2) as a nonkey INCLUDE column which is intended to reduce the seek time on columns selected when the index is accessed. The way Microsoft says it:

An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

The Microsoft example syntax looks like this:

CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
GO

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

detanto SSC Enthusiast Points: 174 More actions · Answer 6

actually i will show it in datatables on php, which i have try it to paging the query but takes time every change page, so i decided to query it all, put it in json and show it to user. it takes almost 1 hour

in order to check the bottleneck, i put the query in azure data studio windows, and debug every join i made. that's why i found the problem laid on when i add 1 field on select statement