Peroformance with, without with

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 2000 Forums

SQL Server Administration (2000)

Peroformance with, without with

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-03-06 : 06:24:52

have 2 query , want to know how the execution happens internally and performance.

table1 = 9 lak records
table2 = 8 lak records

in with clause initially it will take 9 lak and 8 lak in teamp table and finally it will match the temp table( here a, b ) records.

but in normal selection ( directly from table ) , it will access directly in table right ?
in this case to join data will it populated data in some temp table ( in tempdb ) from that will it join ???? or without inserting in temp table will it match directly from actual table ???

which one is better perforamance ???? wish how this execution happens internally ... so only asking

with
a
as ( select c1,c2
from table1 ) ,

b
as ( select c3,c4
from table2 )

select c1,c2,c3,c4
from a,b
where a.c1 = b.c3
and a.c2 = b.c4

============================

select c1,c2,c3,c4
from table1,table2
where table1.c1 = table2.c3
and table1.c2 = table2.c4

============================

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-06 : 07:56:55

Turn on the setting to show the execution plan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources