how to use top and sample in a select statement with multiple joins

Database
Enthusiast

how to use top and sample in a select statement with multiple joins

I know that if I want to take a quick look at only a few rows in a table, I can use either top or sample, e.g., (1) select top 10 * from table or (2) select * from table sample 10. What if I want to take a quick look at a few rows in a table created by a multiple join below:

select

a.cola

b.colb

c.colc

d.cold

... hundres lines of code here...

from a

left join b on a.cola=b.colb

left join c on a.cola=c.colc

left join d on a.cola=d.cold

where a.cola>1

and b.colb>3

and c.colc>5

and d.cold>7

Each table has millions of rolws, and I need to select hundres of columns. To complete this query, it takes hours. But I want to take a quick look at a few rows from this query and before the final table is created which takes hours to run. Can you help me on this?

Thanks

Yunfei

4 REPLIES
Enthusiast

Re: how to use top and sample in a select statement with multiple joins

You can use the same syntax, instead of * use  the columns you need to view.

select

Top 10

a.cola

b.colb

c.colc

d.cold

... hundres lines of code here...

from a

left join b on a.cola=b.colb

left join c on a.cola=c.colc

left join d on a.cola=d.cold

where a.cola>1

and b.colb>3

and c.colc>5

and d.cold>7

Or

select

a.cola

b.colb

c.colc

d.cold

... hundres lines of code here...

from a

left join b on a.cola=b.colb

left join c on a.cola=c.colc

left join d on a.cola=d.cold

where a.cola>1

and b.colb>3

and c.colc>5

and d.cold>7

Sample 10

Junior Contributor

Re: how to use top and sample in a select statement with multiple joins

@kirthi: When you look at Explain you will notice that both SAMPLE and TOP are processed after the full result set has been created, in fact this will be a bit slower than running the SELECT.

The only way to decrease runtime is to apply the SAMPLE in a Derived Table:

from (select * from a sample 100) a
left join b on a.cola=b.colb

But for testing there should be a testing environment with a small subset of the actual data.

Enthusiast

Re: how to use top and sample in a select statement with multiple joins

You are right, I was thinking this is as a Syntax only question, thanks for your correction.

Enthusiast

Re: how to use top and sample in a select statement with multiple joins

Dieter's solution is perfect, I simply need to replace any table, say data a, with (select top 10 * from data) a.

There is only one problem though, I do not have the select * access to the table. Thank you.