CODEWITHSUNDEEP
c#sql.netsql-serverinner-join
Tom
Tom

Reputation: 4059

SQL query on inner join extremely slow

I have a database in SQL Server. There are 2 tables in it, let's call them MASTER and SLAVE. There are a one-to-many relationship between them, so one MASTER record can connect to many SLAVE records.

Here are the table creator SQL commands (you can see also my indexes there):

CREATE TABLE [MASTER] 
(
    [gprs_id] INTEGER  IDENTITY  NOT NULL,
    [date_time] DATETIME  NOT NULL,
    [valid] TINYINT NOT NULL,
    [lat] REAL  NOT NULL,
    [lon] REAL  NOT NULL,

    CONSTRAINT MASTERPrimaryKey PRIMARY KEY NONCLUSTERED(gprs_id)
);

CREATE CLUSTERED INDEX MASTERDate ON MASTER (date_time);

CREATE TABLE [SLAVE] 
(
    [io_id] INTEGER  IDENTITY NOT NULL,
    [recordid] INTEGER  NOT NULL,
    [ioid] TINYINT  NOT NULL,
    [iovalue] FLOAT  NOT NULL,

    CONSTRAINT SLAVEPrimaryKey PRIMARY KEY CLUSTERED(io_id)
);

I made the following query to query data from SLAVE:

DECLARE @fromdate datetime;
DECLARE @todate datetime;
SET @fromdate = '2014-01-01T00:00:00.000';
SET @todate = '2015-01-01T00:00:00.000';

SELECT 
    MASTER.date_time, SLAVE.* 
FROM 
    MASTER 
INNER JOIN 
    SLAVE ON MASTER.gprs_id = SLAVE.recordid 
WHERE
    date_time >= @fromdate 
    AND date_time <= @todate;

I have about 500 000 records in my SLAVE table, and about 50 000 in MASTER. I find the query extremely slow, it is more than a minute (it is the same, if I test from SQL Server Management Studio of from .net code).

How can I speed it up?

Thanks to all!

Upvotes: 0

Views: 1103

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Create in index on [SLAVE].[io_id] and declare the FK relationship

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is your query:

SELECT m.date_time, s.* 
FROM MASTER m INNER JOIN 
     SLAVE s
     ON m.gprs_id = s.recordid 
WHERE m.date_time >= @fromdate AND m.date_time <= @todate;

The best indexes for this query are: master(date_time, gprs_id) and slave(recordid). Your existing indexes are not optimal.

Upvotes: 3

Related Questions