Join's and Index's

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Join's and Index's

Join's and Index's

Rate Topic

Display Mode

Topic Options

Author

Message

dwilliscp

Posted Wednesday, January 30, 2013 12:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

I am fuzzy on this.. so looking to see if my thoughts are correct... When looking at the execution plan, it does not ask for an index, but...

If you have two moderate (500K to 1M) tables, and both are a heap, wouldn't it help the inner join to have index's on what is being joined? If it matters, one is a temp table (#temp) so it would already exist in memory.. one would hope.

Post #1413740

GilaMonster

Posted Wednesday, January 30, 2013 12:35 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725, Visits: 29,981

Maybe. Maybe not. Depends on the rows involved, the join type, other filters, complexity of the query, data types and a few other things. Add indexes, see if they help.

Temp tables are not necessarily memory resident, but anything that they query processor operates on will be in memory, temp or permanent, the query processor doesn't know what a disk is.

Ignoring joins, the permanent table needs a clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1413742

dwilliscp

Posted Thursday, January 31, 2013 7:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

Thanks... BTW it is an inner join. I have already sent a request off to the software company to see if we can create a PK. Currently there is not one. I will see about a clustered index, if we can not get a PK put into place.

Post #1414111

GilaMonster

Posted Thursday, January 31, 2013 7:10 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725, Visits: 29,981

You really should have both on a permanent table. Maybe as one and the same thing, maybe different.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414120

dwilliscp

Posted Friday, February 01, 2013 8:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

Thanks for your help.. BTW I put a covering index into place, but the job that inserts data into that table grew from 30min to 90min (give or take a few min). So I have removed the covering index, and the run time is back down. While the difference in run time of this query is only about 2min. Given that this query only runs once per day.. I am going to leave the covering index off.

Post #1414696

mmartin1

Posted Wednesday, April 17, 2013 10:49 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192, Visits: 640

Maybe the clustered index that re-arranged the pages created disk fragmentation?

Post #1443383

« Prev Topic | Next Topic »

Permissions