24
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(