1

I have a view that is:

SELECT a.Id
, a.col1
, a.col2
, b.col3
FROM a
LEFT OUTER JOIN b ON a.Id = b.Id

Table A takes very little time to query and table B takes a long time to query.

I want to query the view like so:

SELECT Col1
FROM view

In dev, this takes seconds because it drops table b. In prod (standard edition), it queries both tables and takes over a minute. Is there any way to tell the optimizer to not hit table b when this particular query is being run? Table B does not contain multiple Ids and this query does run with a correct query plan (and the same exact data in both tables) in dev.

I am using SQL Server 2019 Standard Edition in Prod and SQL Server 2019 Development Edition in dev. The tables are the same in both dev and prod.

I don't know what to try here. I had always expected the execution plan of a query with a left join to drop the outer table if no columns from it are selected.

Leah Schneider
  • 11
  • 2
  • 2
    Not necessarily. If b contains multiple IDs from a, it cannot be dropped for example. Maybe that's what happens here, you need to attach qplans from both servers – siggemannen May 10 '23 at 21:50
  • 1
    You should check the execution plan, index, sizes, server status, etc.you must small table with subquery and status fragmentation index – abolfazl sadeghi May 10 '23 at 21:55
  • 1
    Your `SELECT` clause references `b.col` and, even if the column wasn't included, the number of matching rows in table b will affect the number of rows returned. I don't see how the optimizer could avoid table b with these query semantics. – Dan Guzman May 10 '23 at 21:56
  • `SELECT Col1 FROM A` – Joel Coehoorn May 10 '23 at 22:12
  • You are right, @lptr. I was thinking the case where columns from the underlying table b were included in the query selecting from the view. – Dan Guzman May 10 '23 at 22:33
  • Please show the full definition for both tables, including any primary, unique and foreign keys. Possibly Prod doesn't have a foreign or primary key defined? – Charlieface May 11 '23 at 00:14
  • As much as I try to avoid DISTINCT, coding `SELECT DISTINCT Id, Col1, Col2 FROM view` might (***MIGHT***) be recognized by the optimizer as a query that can drop the joined table b, and maybe even avoid the DISTINCT overhead assuming that a.Id is a PK. My other suggestion is to make sure there is an index on b.Id. That would at least minimize the work needed to determine the number of repetitions for each table a row. – T N May 11 '23 at 01:43
  • Welcome to Stack Overflow. To help you with your [tag:query-optimization] question, we need to see your table definitions, your index definitions, and the actual query plan. Right click, check Show Actual Query Plan, then run the query in SSMS. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. – O. Jones May 11 '23 at 21:57
  • Why not just `SELECT Col1 FROM a` ? this will definitely cut out any concerns about table b - why do you feel compelled to use that view? – Paul Maxwell May 12 '23 at 00:22
  • I had some good optimizer luck with distinct. Thanks. – Leah Schneider May 19 '23 at 17:17

0 Answers0