Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Join's and Index's Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 12:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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
Posted Wednesday, January 30, 2013 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
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
Posted Thursday, January 31, 2013 7:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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
Posted Thursday, January 31, 2013 7:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
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
Posted Friday, February 1, 2013 8:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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
Posted Wednesday, April 17, 2013 10:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:50 PM
Points: 358, Visits: 876
Maybe the clustered index that re-arranged the pages created disk fragmentation?
Post #1443383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse