Index help - Performance issue

Question

Post reply

Index help - Performance issue

May 13, 2023 at 10:42 am

This was removed by the editor as SPAM

Viewing 6 posts - 16 through 20 (of 20 total)

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

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 1

so code is just a view on top of this table, that does select * from this table without any filter.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

SQL_Surfer wrote:

so code is just a view on top of this table, that does select * from this table without any filter.

Ok, so the code that's causing the performance problem is actually in the view.

What we have from you, so far, is you saying that your car is having issues when you go up hill and can't reach highway speeds even on a flat. You've shown us the spoiler on the back of the car, the nice tires, and the fuzzy dice hanging from the mirror. But, we can't pop the hood on the vehicle to check for you so you need to provide some measurements and tell us what kind of gas, oil, and transmission fluid you're using, as well as what their levels are. 😀

To do that, I strongly recommend that you read and heed the article at the second link in my signature line for one of the better ways to post a performance issue so that you have a much better chance of resolving this issue.

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

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 3

Thanks. Question is do you really have room for improvement, when you your table is large as 176M rows and your view on top of that table returns everything without filter.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

SQL can usually imbed the conditions into the query plan to access the table rather than scanning the entire table first. Of course you'd have to look at the query plan to be sure.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 5

SQL_Surfer wrote:

Thanks. Question is do you really have room for improvement, when you your table is large as 176M rows and your view on top of that table returns everything without filter.

First is view literally just a SELECT * from a single table or is it actually doing any other work?

Second usually doing a SELECT * with no filter from a massive table will not be great performance, why is it not being filtered in some way(either in the view itself or wherever the view is accessed)? As scott noted SQL Server will try to push down where clause filters done on the view to the base tables if it can.