How to improve SELECT query

Question

Is there a way to improve my query

SELECT *
FROM  StringControllerDaten
WHERE StringControllerDaten.Erstellt NOT IN
(
    SELECT  
            Min(Erstellt) AS Erstellt   
    FROM        StringControllerDaten

    GROUP BY
    StringControllerDaten.StringController,
    DATEPART(YEAR, Erstellt),
    DATEPART(MONTH, Erstellt),
    DATEPART(DAY, Erstellt),
    DATEPART(HOUR, Erstellt),
    (DATEPART(MINUTE, Erstellt) / 15)
)

This query returns all data who doesn't match one of the min Datetimes from the 15Min Interval.

It is running again +1Mio Row's and takes hours.

Edit:

the estimated query plan

Definitely avoiding `select *` will help. It's better to put the column names explicitly than `*`. Query plan will explain a lot and, more importantly, it will show the most time-consuming task within your query. — Milind Gokhale, Mar 09 '16 at 08:15
Use a table variable to store the `Min(Erstellt)` first and then use that table inside the NOT IN — artm, Mar 09 '16 at 08:19
Are your tables indexed properly? Could you attach `.sqlplan` file? Adding some sample data would help. — Evaldas Buinauskas, Mar 09 '16 at 09:02

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Create a list of dates with 15 min interval between min and max Erstellt put then in a table/cursor/temptable.

See Generate a resultset of incrementing dates in TSQL

Lets say the table looks like:

    CREATE TABLE [dbo].[AllDates]
    ( COLUMN Item DATETIME)

Cross compare your table with list from 1.

SELECT
   StringControllerDaten.ID -- or what you need
FROM
   StringControllerDaten
WHERE
   StringControllerDaten.Erstellt NOT IN (SELECT AllDates.Item FROM AllDates)

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Could the top answer in this thread help you?

EDIT: still need to figure out how get the NOT IN into an Inner Join

Complex join with nested group-by/having clause?

achinda99 wrote: "In most database systems, the JOIN works a lost faster than doing a WHERE ... IN."

Got no means of testing atm, tho.

edited May 23 '17 at 12:23

Community

1
1

answered Mar 09 '16 at 08:19

Martin Dreher

1,514
2
12
22

How to improve SELECT query

Edit:

2 Answers2