select takes along time - 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 2008 Forums

Transact-SQL (2008)

select takes along time

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-13 : 07:05:59

hi,
select * from tbl1 returns data within a second
select * from tbl2 returns data within a second

do you know why this query takes forever.
select tbl1.ccy from tbl1 inner join tbl2 on tbl1.ccy = tbl2.ccy

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 07:08:26

what indexes you've on table? what are amount of data you've in each table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-13 : 07:09:15

please note that I forgot to say that tbl1 and tbl2 are both views.
But I do not see why each view returns data quickly but not when they are joined.

Sachin.Nand

2937 Posts

Posted - 2011-10-13 : 07:15:11

Try adding the EXPAND option to the select query when you join the views.



select tbl1.ccy from tbl1 inner join tbl2 on tbl1.ccy = tbl2.ccy with (expand views)

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-13 : 07:20:22

quote:
Originally posted by Sachin.Nand

Try adding the EXPAND option to the select query when you join the views.

select tbl1.ccy from tbl1 inner join tbl2 on tbl1.ccy = tbl2.ccy with (expand views)

PBUH
It gives error on expand views:
SELECT g.Country
FROM dbo.view1 g INNER JOIN
dbo.view2 y ON
g.Ccy = y.Ccy
with (expand views)

Sachin.Nand

2937 Posts

Posted - 2011-10-13 : 07:21:29

Ok just try this

..g.Ccy = y.Ccy with (expand)

Cant remember the exact syntax.

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-13 : 07:28:15

Solved the issue by creating a stored procedure which places each view into a temp table and then join them.
Runs less than a sec.
Solved.
Thank you all

Sachin.Nand

2937 Posts

Posted - 2011-10-13 : 07:29:47

quote:
Originally posted by arkiboys

Solved the issue by creating a stored procedure which places each view into a temp table and then join them.
Runs less than a sec.
Solved.
Thank you all

Great.But I would like to know how does my solution works ?

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-13 : 07:42:14

still gives error.
Thanks anyway as I have already found a solution.

Sachin.Nand

2937 Posts

Posted - 2011-10-13 : 07:55:31

Ok the correct syntax is