Edit Question - KnowledgeHub

Title Body

I have an SQL query (generated by LINQ to Entities) which is roughly like the following: SELECT * FROM [mydb].[dbo].[employees] JOIN [mydb].[dbo].[industry] ON jobs.industryId = industry.id JOIN [mydb].[dbo].[state] ON jobs.stateId = state.id JOIN [mydb].[dbo].[positionType] ON jobs.positionTypeId = positionType.id JOIN [mydb].[dbo].[payPer] ON jobs.salaryPerId = payPer.id JOIN [mydb].[dbo].[country] ON jobs.countryId = country.id WHERE countryName = 'US' ORDER BY startDatetime The query returns about 1200 rows, which I don't think is a huge amount. Unfortunately it also takes ~16 seconds. Without the ORDER BY, the query takes <1 second. I've used SQL Server Management Studio to put an index on the startDatetime column, and also a clustered index on "cityId, industryId, startDatetime, positionTypeId, payPerId, stateId" (i.e. all of the columns in "jobs" that we use in JOINs and on the column we use ORDER BY on). I already have individual indexes on each of the columns we use in JOINs. Unfortunately this hasn't made the query any faster. I ran a showplan and got: |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[cityId])) |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[stateId])) | |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[industryId])) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[positionTypeId])) | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[salaryPerId])) | | | | |--Sort(ORDER BY:([mydb].[dbo].[jobs].[issueDatetime] ASC)) | | | | | |--Hash Match(Inner Join, HASH:([mydb].[dbo].[currency].[id])=([mydb].[dbo].[jobs].[salaryCurrencyId])) | | | | | |--Index Scan(OBJECT:([mydb].[dbo].[currency].[IX_currency])) | | | | | |--Nested Loops(

Tags (comma-separated) Cancel