Performance issue in join - SQL Server Forums

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

Performance issue in join

Author

Topic

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-05-10 : 02:56:47

Hi to all,

I am having two tables contains 6 lakhs record and 30,000 record respectively. i make a innner join between these two tables it take min 15 sec. i dint know wats the problem can any 1 help me.

Thanx in Advance.
Amjath

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 02:58:32

Do you have an index for each tables in the JOIN covering the appropriate columns?

Best to post your query here so we can have a look at it.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-10 : 02:59:33

Also post your tabel structure and identify the indexes in the tables.

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-05-10 : 03:14:58

Hi Guys,
I have index for both the Tables.

Here My Query :
--------------
SELECT Reheat.Number AS SeqNo, SR.ID, Reheat.Heat, Reheat.FinalProd AS Product,
1 AS Mill, SR.SampleNeeded, SR.Status, SR.StatusID, SR.SampleID
FROM Table2 SR
INNER JOIN Table1 Reheat ON SR.RollMillBarid = Reheat.LocSeq ORDER BY SR.RollMillBarid DESC

IS THIS Right. Please Correct My Fault.

Regards
-Amjath

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-10 : 03:18:18

Post the table structure

Madhivanan

Failing to plan is Planning to fail

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-05-10 : 03:18:30

My Index Details

Table2
-----------
index_name index_description index_keys
----------------------------------------------------------------------------------------------------------------------------------------------------------
IX_SR_RollMillBarID clustered located on PRIMARY RollMillBarID
SampleRequest_Index_1 nonclustered located on PRIMARY RollMillBarID

Table1
--------
index_name index_description index_keys
----------------------------------------------------------------------------------------------------------------------------------------------------------
PK_NYS1Reheat_1__36 clustered, unique, primary key located on PRIMARY LocSeq

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-05-10 : 03:33:58

Hi

The information given above are enough or i want to give little bit more.

Regards
Amjath

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-05-10 : 04:53:30

Hi,

How to use iindex tuning wizard. when open the wizard and give the mysp.sql as workload and add the tables wat are related to it. but the result wat i got is No Index Recommended for ur Workload.

Please give the correct way of using index tuning wizard.

With Regards
Amjath.

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 05:38:55

The query and indexes look OK.

How many rows is the query returning? As there is no WHERE clause if the query is returning a lot of rows the time it probably just the time to transmit all the data.

Kristen

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-05-10 : 06:46:29

Hi Kristen,

U r right. the result set contain around 32,000 records. thats the problem.

Regards
Amjath.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources