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 2012 Forums
 Transact-SQL (2012)
 search record and join time estimate

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2014-06-25 : 23:24:54
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

545 Posts

Posted - 2014-06-26 : 01:40:38
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

165 Posts

Posted - 2014-06-27 : 00:06:32
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

545 Posts

Posted - 2014-06-27 : 01:13:09
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
   

- Advertisement -