TSQL Join efficiency

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_Connec开发者_JAVA百科tions 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

It may have nothing to do with your query and everything to do with the data transfer.

• How fast does the query run in query analyzer?
• How does this compare to the web page?

If you are bringing back the entire data set you may want to introduce paging, say 100 records per page.

The first thing I normally suggest is to profile to look for potential indexes to help out. But the when the problem is sporadic like this and the normal case is for the query to run in <1sec, it's more likely due to lock contention rather than a missing index. That means the cause is something else in the system causing this query to take longer. Perhaps an insert or update. Perhaps another select query — one that you would normally expect to take a little longer so the extra time on it's end isn't noted.

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.