SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

usafelix
Posting Yak Master

Hong Kong
103 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
Constraint Violating Yak Guru

Romania
439 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
103 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
Constraint Violating Yak Guru

Romania
439 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000