I'm attempting to query from a table to get some specific date with the following query:
SELECT
[bmUnitId] AS BMUnitID,
[DataType],
[LocalTimeFrom],
[LocalTimeTo],
[LevelTo]
FROM
tibcoRampPosition
WHERE
LocalTimeTo between '".$chosenDate1."' and '".$chosenDate2."'
AND
bmUnitId = '".$chosenBMUnit."'
AND
DataType = 8
ORDER BY
LocalTimeTo asc
I've checked the variables over, making sure they are what I intend (by echoing them before they query is performed). The problem is, the table it is querying data from has 56,685,678 rows, being a 5.3 GB database.
It takes >5 minutes to perform the query. I was wondering if there's anyway I can improve this as... I'd like it to be 10 seconds execution times max pretty much. 5 minutes isn't great for application use.
You need an index, otherwise the database will have to read the entire table every single time you query it. Indexes are data from handful of columns (ones you most often filter by) and are ordered, so seeking is fast. Looking at your query, I think an index like this would suffice:
CREATE CLUSTERED INDEX IX_MyIndex ON dbo.tibcoRampPosition
(
LocalTimeTo,
bmUnitId,
DataType
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I put the LocalTimeTo column first because you're querying it by range. The order of the other two does not matter (at least in this query).
If you had no index before, it's quite possible the query will now return in under 5 seconds. Databases are the only platform where 3 lines of code could improve your performance by many magnitudes.