1

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
Improve this question
3
  • 1
    What indices do you have? What's the query plan when you "show execution plan" from sql query analyzer? Commented Nov 3, 2009 at 2:37
  • 1
    A 20 second differential on a query as simple as this points to a load/server issue. Can you mirror the data and run this on a different server? I would try that first before attempting to optimize. Commented Nov 3, 2009 at 2:44
  • 1
    It's always quick on the SQLExpress server I have setup on the dev box. SQL was never the focus of my studies, so I figured it might also be a less-than-optimal query. I'll give the query analyzer a look, too. Commented Nov 3, 2009 at 9:05

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The query and the page delay loading are about the same. It's purely the SQL server that's holding it up. Got a couple of .NET timeout error messages that had SQL calls on the stack, too. I'm using a ComponentArt grid. I'm pretty sure it manages its own pagination, but I also think it does pull down the whole lot of data and manages from there. What's the best way to approach that?
I'm assuming you scripted the tables on the dev box and pushed them to the production box. Did you make sure the indexes got scripted as well? Sounds like dev is indexed but production is not.
2

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.

Improve this answer

1 Comment

There aren't really a lot of users right now, so I don't think it could be a lock problem, but I could be wrong.
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.