SQL Query on a HUGE table

Question

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.

Unless you can post the ddl (including indexes) for the table there isn't a lot anybody can do here. I know you didn't post all your code but from what you did post it seems like this might also be vulnerable to sql injection. — Sean Lange, Aug 20 '15 at 13:54
First use **EXPLAIN query** to catch the bottleneck with your indexes — B001ᛦ, Aug 20 '15 at 13:56
Google and learn what a "Covering Index" is, and put one on your table for this query, and it will be super fast. — Tab Alleman, Aug 20 '15 at 14:02
I'm kind of new to using SQL, how can I provide you with the information of the table? — Callum Luke Vernon, Aug 20 '15 at 14:15
Take a look for for a good approach to putting this information together. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Aug 20 '15 at 14:33

Anonymous · Accepted Answer · 2015-08-20T14:24:35.540

0

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).

See more info in this answer.

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.

edited Aug 20 '15 at 14:24

answered Aug 20 '15 at 14:11

Anonymous

11,740
3
40
50

Thanks for the answer! How would I go about implementing this in my PHP code? – Callum Luke Vernon Aug 20 '15 at 14:43
You don't implemented in PHP. You run that code on the database once and then all subsequent queries will be able to use the index. I assume you have access to SQL Server Management Studio, so run it there on the right database. – Anonymous Aug 20 '15 at 14:46
Ah right, thanks, I do have Management Studio yes; I'll attempt to do this – Callum Luke Vernon Aug 21 '15 at 07:22

SQL Query on a HUGE table

1 Answers1

See more info in this answer.