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
 General SQL Server Forums
 New to SQL Server Programming
 script running longer

Author  Topic 

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-07-31 : 03:21:32
Hi, I have a situation to query out those matching names. But it takes longer time to return result.

select * from tableA

this contains: ID, firstName, lastName

select * from tableB

this contains more info: ID, firstName, lastName, firstStation, lastStation

i would like to search for same name that travel but it seems like running longer timing.

select * from tableA a
join tableB b on a.firstname=b.firstname and a.lastname=b.lastname

how can i solve this issue?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 12:19:48
Where is your WHERE clause? Why are you returning all data from the tables? How many rows are in those tables? Are the join conditions indexed?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-07-31 : 22:23:50
the ID on both table are index.
Go to Top of Page

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2014-08-01 : 02:57:29
U can do following things to optimize your query

1. Create a Non clustered index on both the tables
Create NonCluster Index Nidx_a on a(firstname, lastname)
Create NonCluster Index Nidx_b on b(firstname, lastname)

2. specify the cloumn name in Select query

3. use the Nolock with the table name



Vijay is here to learn something from you guys.
Go to Top of Page
   

- Advertisement -