You have 1,000,000 records in your customers table....

Question

You have 1,000,000 records in your customers table....

Steve Jones - SSC Editor

SSC Guru

Points: 734512
More actions
April 3, 2009 at 12:23 pm

#63489

Comments posted to this topic are about the item You have 1,000,000 records in your customers table....

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

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

I would imagine that everyone would get this question right, but... this is why I hate Microsoft and other tests... totally unreal scenario's. Who on this good green Earth would have a million row customer table and have that be the only bloody query running against it never mind running that same query once per second?

It's like my other favorite question in the whole world...

Which of the following Server Roles must a login be a member of to use Bulk Insert?

1. Bulk Insert Administrators

2. Bulk Insert Administrator

3. BulkInsertAdministrators

4. BulkInsertAdministrator

5. None of the above

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

GSquared SSC Guru Points: 260824 More actions · Answer 2

I have to ask how that solution would be better than an indexed view with a covering index for the query, which is what the first option seems to imply to me. Though I also have to admit that the words "pre-sorted" in the first option do make me doubt it.

But, for example, you could have a view like this:

create view dbo.MyIndexedView

with schemabinding

as

select CUSTOMERID, START_DT, CUSTOMER_NM, LAST_ORDER_DT, COMPANY_NM

from dbo.customers;

go

create unique clustered index UCX_MyIndexedView_ID on dbo.MyIndexedView(customerid);

--

create index IDX_MyIndexedView_Covering on dbo.MyIndexedView(start_dt)

include (customer_nm, last_order_dt, company_nm);

That would seem to satisfy, "Create a pre-sorted indexed view for the query", and would give very comparable, probably identical, performance to the other option.

Other factors in the table's use might indicate one or the other solution would be overall superior, but just for purposes of improving the performance of that one query, the two answers would appear to be pretty much equivalent.

Of course, it would be even easier to just build that index on the table directly, instead of on a view, but that wasn't an option.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734512 More actions · Answer 3

Part of wording the questions is that I have to limit the scope. One way to do that is exaggerate a bit to get you to focus on that one area.

Steve Jones - SSC Editor SSC Guru Points: 734512 More actions · Answer 4

I went back and looked and the question really needs to have someone about the update load. An indexed view adds overhead for inserts/updates, and space (less significant). Is it more than a regular view? That would be a great test.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Steve Jones - Editor (4/14/2009)
Part of wording the questions is that I have to limit the scope. One way to do that is exaggerate a bit to get you to focus on that one area.

Heh... agreed... I can still hate the buggers, though. 😛

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

TomThomson SSC Guru Points: 104773 More actions · Answer 6

I wonder how much of the rather high failure rate on this one was due to bad wording of the correct option? What I'm thinking is that "one" in that option may have been interpreted as "primary key with clustered index", thus being impossible because a table can't have two primary keys. I had to read that option three or four times to convince myself that "one" here meant "clustered index" before I finally decided for that option rather than the "presorted view" (which is only poorer because the storage implication is regrettable, since there are no queries updating anything in this table so there's no update overhead).

Tom