Query Taking Exponentially Longer to Complete

Question

Post reply

Query Taking Exponentially Longer to Complete

Jonathan AC Roberts

SSCoach

Points: 18257
More actions
August 19, 2019 at 7:21 pm
Answer
#3672456
The query will remain the same, the optimiser will work out it needs to use it.
So just run the statement:
```
CREATE INDEX IX_DocumentLog_1 ON elliedb.DocumentLog(Title, OrderDateUtc) 
INCLUDE (Company, RequestedBy, Stage, Status, EncompassId);
```
In SSMS and SQL Server will do the rest.

Viewing 15 posts - 16 through 29 (of 29 total)

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

awalton SSC Enthusiast Points: 191 More actions · Answer 1

So the plan looks the same, however I get the Missing Index when I change it to top 10k.

Attachments:

You must be logged in to view attached files.

awalton SSC Enthusiast Points: 191 More actions · Answer 2

Awesome! Thank you Jonathan I will try that! Do I modify my query to use the newly created IX_DocumentLog_1 or would I keep it the same?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

awalton wrote:

And here is the DDL (I think)

With one exception, all of the VARCHAR columns in that table are VARCHAR(MAX), which is a LOB datatype. That's the first thing that needs to be fixed.

--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)

awalton SSC Enthusiast Points: 191 More actions · Answer 4

Jeff, which ones are VARCHAR(MAX)? Are those the fields with length -1?

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 5

Jeff Moden wrote:

awalton wrote:
And here is the DDL (I think)
With one exception, all of the VARCHAR columns in that table are VARCHAR(MAX), which is a LOB datatype. That's the first thing that needs to be fixed.

Yes, I didn't look at the DDL, the OP won't be able to create an index on Title unless it's changed to a smaller varchar.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

awalton wrote:

Jeff, which ones are VARCHAR(MAX)? Are those the fields with length -1?

Yes. You can verify that by right clicking on the table in the Explorer window of SSMS and generating the script for the table. That's also what people actually want when they ask you to "post the DDL". It should also include all constraints, indexes, and triggers on the table.

--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)

awalton SSC Enthusiast Points: 191 More actions · Answer 7

Upon a quick google I think I should be looking for a Tasks entry on the right click menu, however I don't see that as an option when I right click on the table.

Attachments:

You must be logged in to view attached files.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

Jonathan AC Roberts wrote:

Jeff Moden wrote:
awalton wrote:
And here is the DDL (I think)
With one exception, all of the VARCHAR columns in that table are VARCHAR(MAX), which is a LOB datatype. That's the first thing that needs to be fixed.
Yes, I didn't look at the DDL, the OP won't be able to create an index on Title unless it's changed to a smaller varchar.

The real key here is that all of those MAX columns need to be "right sized". Since 2005, all of that LOB stuff gets stored in-row by default and there's no sense at all in having something a "status" column being assigned as a MAX datatype even if you do drive them out of row. In fact, only the order date column is "right-sized" in this table. While I do believe and agree that "premature optimization is the root of all evil" as Knuth's parable goes, this is just wicked bad design and needs to be fixed.

--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)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

awalton wrote:

Upon a quick google I think I should be looking for a Tasks entry on the right click menu, however I don't see that as an option when I right click on the table.

Nope. Your graphic has the right thing on it and whatever your "quick google" stated is incorrect. You need to click on that "Script Table as" selection and then follow your nose.

--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)

awalton SSC Enthusiast Points: 191 More actions · Answer 10

The Database I'm working on is a replication of the database within the third party application that my company uses to conduct business. I'm sure they set up the tables on their end to be the VARCHAR(max), it seems that just about every field in this table is assigned a VARCHAR(max) data type (there are about 50 other fields that I didn't show yall). Would I be able to reassign the length on my end and still accept the data that we get from the replication? Like I change the Title column in my database to Varchar(40) and the incoming data is still Varchar(max).

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 11

You should find out what the max length is of any of the data currently on the database

SELECT MAX(LEN(Id))          MAXId,
       MAX(LEN(Company))     MAXCompany,
       MAX(LEN(RequestedBy)) MAXRequestedBy,
       MAX(LEN(Stage))       MAXStage,
       MAX(LEN(Title))       MAXTitle,
       MAX(LEN(Status))      MAXStatus
  FROM [Staging].[elliedb].[DocumentLog]

Then make your columns a bit longer than the results.

It might also be worth trying to contact the third party vendor to see what they think the maximum lengths should be.

awalton SSC Enthusiast Points: 191 More actions · Answer 12

Thank you! So there shouldn't be an issue changing the data type of the columns on my end with importing the data of the Varchar(max) type that we receive from that replication?

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 13

awalton wrote:

Thank you! So there shouldn't be an issue changing the data type of the columns on my end with importing the data of the Varchar(max) type that we receive from that replication?

I don't know how the data is replicated so can't say. The column you really need to change the data type of is Title. If you can't do that just use the index SQL Server has suggested as it will be able to create that one.

awalton SSC Enthusiast Points: 191 More actions · Answer 14

Just an update, adding the index that SSMS suggested cut the runtime of the query from ~26 minutes to 3 seconds. Thank you for opening my eyes to the Execution Plan and Indexing Tables. I'll continue to look into assigning the correct length for these columns to further optimize querying this database, but thank yall so much.