search record and join time estimate

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources