index scan vs index seek, how it determine the cost

for visitidx seek cost 21.3% returns 27,300 rows, but index scan probookingproidx scan cost only 2.% return 2077,000rows. per iteration means what?

phs5.queryanalysis (208.6 kB)
avatar image By George Zhou 1 asked Mar 23, 2017 at 08:14 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Well, a few things:

  1. Cost % is just an estimated cost %.

  2. Number of rows is one factor in determining costs, but it is not the only factor. Row size, number of executions, sort requirements, and other factors also contribute to cost-based optimization.

  3. Per iteration means that while you have an index seek on visit, it actually has to perform 27,000 singleton seeks to return those rows. So the cost is not the cost of "a seek," it is the cumulative cost of all of the seeks that needed to be performed.

As a side note, you currently have res_room in an outer join, but then you added a predicate in the where clause against that table, which effectively turns that into an inner join. If you find you are filtering out too many rows, you may want to move that filter to the ON clause of the outer join; if you are getting the right results, you might want to make that an INNER JOIN instead, so that the intent is unambiguous.

avatar image By Aaron Bertrand ♦ 1.7k answered Apr 11, 2017 at 09:32 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Mar 23, 2017 at 08:14 PM

Seen: 1051 times

Last Updated: Apr 11, 2017 at 09:32 PM