running subselect with in() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

running subselect with in()

Author

Topic

Umutp
Starting Member

4 Posts

Posted - 2008-01-13 : 08:25:15

I'm sorry about my English.If you write an answer,i am very happy

we have two different sql queries that use in().But we dont know performance differences of these queries.In first query;

we use @maxValue variable as a parameter

declare @maxValue int
select @ maxValue = max(col3) from table1
select * from table1 where col3 in(@maxValue)

In second query; we write a subselect query inside in().

select * from table1 where col3 in(select max(uc3) from table1)

we want to run second query.Does this subselect run in all steps or just once and doesn't run at other next steps?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-13 : 08:32:06

Not sure what you mean.
select * from table1 where col3 in(select max(uc3) from table1)
select * from table1 where col3 in(select max(uc3) from table1)
select * from table1 where col3 in(select max(uc3) from table1)

If you run that the subquery will run 3 times.
select * from table1 where col3 in(select max(uc3) from table1)
it will run once for the query.

Note that your query is actually
select * from table1 where col3 = (select max(uc3) from table1)
in your other example
select * from table1 where col3 = @maxValue

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Umutp
Starting Member

4 Posts

Posted - 2008-01-13 : 08:44:11

Firstly, thank you for your answer.

select * from table1 where col3 in(select max(uc3) from table1)

I will run this query only once.But my table has 1 billion records.While SqlServer is processing 1billion records; does run subselect at all step of process?

Umutp
Starting Member

4 Posts

Posted - 2008-01-13 : 08:49:38

I'sorry.You answered my question.I did not see.Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-13 : 08:56:19

Have a look at the query plan.
It'll run it once.

To optimise the query you would probably need
a nonclustered index on uc3
an index on col3 with all other columns included.

Note that you shouldn't return all columns unless you need them - even then I wouldn't use select *.
The subuery should take almost no time to run.
The result set will depend on how many rows are returned - if it's a few the whole query should run in milliseconds.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Umutp
Starting Member

4 Posts

Posted - 2008-01-13 : 09:09:51

I'm a student in university.Your advices is helpful for me.
Thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources