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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 search record and join time estimate
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

usafelix
Posting Yak Master

Hong Kong
165 Posts

Posted - 06/25/2014 :  23:24:54  Show Profile  Reply with Quote
I have two table and using of inner join. The query statement is

select distinct project_no,* from project inner join Project.project_no = labour.Project_no

Table Project (record 28000)
Project_no
12abc.0
000010.10
z00123.01
Z00009.11
z00011.02

Table Labour (record 80000)
Project_no
12abc.0
000010.10
z00009.10
z00009.12
z000013.10
z00011.02

The query result is 65000 record output in below :
12abc.0
000010.10

my expect result 80000+28000 record is below:
12abc.0
000010.10
z00123.01
z0009.10
z0009.11
z0009.12
z000013.10

1.The output result is cannot show the prefix "Z" record, why ?

2.Project and labour table the join time should be using ? I join this two table more than 10 minutes and alway make the timeout or temp db full using the above query statement. ( I am expect to display 10 fields column only)

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 06/26/2014 :  01:40:38  Show Profile  Reply with Quote
1.Probably, one of your "Z" record have some extra space. You can remove (if is extra space) . also you can use LTRIM(RTRIM(project_no)) to update or search (be aware of sarg / non-sargable)

2.Is an index define on Project_no? Probably not; add an index on project_no (both tables)



sabinWeb MCP
Go to Top of Page

usafelix
Posting Yak Master

Hong Kong
165 Posts

Posted - 06/27/2014 :  00:06:32  Show Profile  Reply with Quote
How to know the project_no is indexed & how to create index on project_no ?

please review my query is it correct.

select distinct project_no,* from project inner
join ltrim(ltrim(project.project_no)) = ltrim(rtrim(labour.project_no))

Thanks
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 06/27/2014 :  01:13:09  Show Profile  Reply with Quote
Question: why you use distinct and also * ? What you want to obtain from this query?


If I don't know the DDL/structure of your tables , I have to guess

CREATE NONCLUSTERED INDEX IX_NC_Project_ProjectNo ON Project(ProjectNo);
CREATE NONCLUSTERED INDEX IX_NC_Labour_ProjectNo ON Labour(ProjectNo);


where: IX_NC_Project_ProjectNo and IX_NC_Labour_ProjectNo are the name of the index.This could be any unique name.
pls check BOL (BooksOnLine)


select 
    distinct project_no 
from 
        project 
        inner join labour
        ON ltrim(ltrim(project.project_no)) = ltrim(rtrim(labour.project_no))





sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000