Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
We have a view table and selecting from view is normally taking too much time. for example: select x,y,z from view1 is taking too much time to load. This one is ok.

if you query:
SQL
select x,y,z from view1 where x in ('abc')

queries in seconds.

if you query:
SQL
select x,y,z from view1 where x in (select 'abc' from table1 where y='1234') 

queries in seconds.

but if you query:
SQL
select x,y,z from view1 where x in (select x from table1 where y='1234') 

is taking too much time to query and this is the problem we want to solve.

by the way you can think that : select x from table1 where y='1234' returns 'abc' with one row.

The scenario is decsribed above , what do you think could be the reason to take so much time to query on the third query. We have tried joining but it didn't work.
Posted

Try using indexes on your table.

Using JOIN instead of IN should speed up the process - http://www.bennadel.com/blog/940-SQL-Optimization-Case-Study-JOIN-Clause-vs-IN-Clause.htm[^].
 
Share this answer
 
Comments
kubi081 10-May-13 2:48am    
Thanks for the response,
We have tried these methods but they didn't work,
it still takes much time to bring data.
This SQL should work for you.

SQL
select x,y,z from view1 
inner join table1 on ((view1.x = table1.x) and (table1.y='1234'))
 
Share this answer
 
Comments
kubi081 10-May-13 2:48am    
Thanks for the response,
We have tried these methods but they didn't work,
it still takes much time to bring data.
_Damian S_ 10-May-13 2:55am    
Well, what's happening in view1? Could you perhaps make view2 that isn't as complex and use that?
kubi081 10-May-13 2:56am    
Yes, we have decided to use another view
Try this...
1.
SQL
select x,y,z from view1 WITH (Index(Index_x)) where x in (select x from table1  WITH (Index(Index_y)) where y='1234')


2.
SQL
select x,y,z from view1
where exists (select * from table1 where y='1234' and table1.x=view1.x)

Happy Coding!
:)
 
Share this answer
 
v2
Comments
kubi081 10-May-13 2:48am    
Thanks for the response,
We have tried these methods but they didn't work,
it still takes much time to bring data.
Aarti Meswania 10-May-13 3:14am    
try second option in updated solution
kubi081 10-May-13 3:28am    
we decided to use another view to get faster results
It is possibly related to optimiser behaviour,
that is why we have decided to use another view to get faster results.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900