TSQL Join efficiency

Question

I'm developing an ASP.NET/C#/SQL application. I've created a query for a specific grid-view that involves a lot of joins to get the data needed. On the hosted server, the query has randomly started taking up to 20 seconds to process. I'm sure it's partly an overloaded host-server (because sometimes the query takes <1s), but I don't think the query (which is actually a view reference via a stored procedure) is at all optimal regardless.

I'm unsure how to improve the efficiency of the below query: (There are about 1500 matching records to those joins, currently)

SELECT dbo.ca_Connections.ID, 
       dbo.ca_Connections.Date, 
       dbo.ca_Connections.ElectricityID, 
       dbo.ca_Connections.NaturalGasID, 
       dbo.ca_Connections.LPGID, 
       dbo.ca_Connections.EndUserID, 
       dbo.ca_Addrs.LotNumber, 
       dbo.ca_Addrs.UnitNumber, 
       dbo.ca_Addrs.StreetNumber, 
       dbo.ca_Addrs.Street1, 
       dbo.ca_Addrs.Street2, 
       dbo.ca_Addrs.Suburb, 
       dbo.ca_Addrs.Postcode, 
       dbo.ca_Addrs.LevelNumber, 
       dbo.ca_CompanyConnectors.ConnectorID, 
       dbo.ca_CompanyConnectors.CompanyID, 
       dbo.ca_Connections.HandOverDate, 
       dbo.ca_Companies.Name, 
       dbo.ca_States.State,
       CONVERT(nchar, dbo.ca_Connections.Date, 103) AS DateView, 
       CONVERT(nchar, dbo.ca_Connections.HandOverDate, 103) AS HandOverDateView
  FROM dbo.ca_CompanyConnections 
INNER JOIN dbo.ca_CompanyConnectors ON dbo.ca_CompanyConnections.CompanyID = dbo.ca_CompanyConnectors.CompanyID 
INNER JOIN dbo.ca_Connections ON dbo.ca_CompanyConnections.ConnectionID = dbo.ca_Connections.ID 
INNER JOIN dbo.ca_Addrs ON dbo.ca_Connections.AddressID = dbo.ca_Addrs.ID 
INNER JOIN dbo.ca_Companies ON dbo.ca_CompanyConnectors.CompanyID = dbo.ca_Companies.ID 
INNER JOIN dbo.ca_States ON dbo.ca_Addrs.StateID = dbo.ca_States.ID

Answer 1

I would start with indexing, but I have a database that is a third-party application. Creating my own indexes is not an option. I read an article (sorry, can't find the reference) recommending breaking up the query into table variables or temp tables (depending on number of records) when you have multiple tables in your query (not sure what the magic number is).

Start with dbo.ca_CompanyConnections, dbo.ca_CompanyConnectors, dbo.ca_Connections. Include the fields you need. And then subsitute these three joined tables with just the temp table.

Not sure what the issue is (would like to here recommendations) but seems like when you get over 5 tables performance seems to drop.