Help to optimize query

Hi,
I have these two tables in a Database

ITEMS
IDnumeric (Primary key)
ZDIDnvarchar 3 (not null)
IDF_Familynumeric(not null)
Descriptionnvarchar40 (not null)

DATAS
IDnumeric(Primary Key)
IDF_Itemnumeric(Foreign key)
IDF_Referencenumeric(Foreign Key)
[Date]smalldatetime(not null)
Containernchar10(not null)
Averagedecimal(not null)
[%Compliance]decimal(not null)
[%OutOfRange<MinTg]decimal(not null)
[%OutOfRange>MaxTg]decimal(not null)
Targetdecimal(not null)
[Min]decimal(not null)
[Max]decimal(not null)

The table DATAS has 4000000+ records

I'm running this query:

SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.Description
FROM Items as I, Datas as D
WHERE D.IDF_Item = I.ID AND I.IDF_Family = 84
AND D.Date BETWEEN '5/18/2004' AND '5/18/2004'

it's taking 4-5 minutes to run.
The result is correct, there is no thing on that date.
I've done a reindex, but still the same thing.

What can I do?

Thanksbie2 (Francois.Tardif@.gmail.com) writes:
> I have these two tables in a Database
> ITEMS
> ID numeric (Primary key)
> ZDID nvarchar 3 (not null)
> IDF_Family numeric (not null)
> Description nvarchar 40 (not null)
> DATAS
> ID numeric (Primary Key)
> IDF_Item numeric (Foreign key)
> IDF_Reference numeric (Foreign Key)
> [Date] smalldatetime (not null)
> Container nchar 10 (not null)
> Average decimal (not null)
> [%Compliance] decimal (not null)
> [%OutOfRange<MinTg] decimal (not null)
> [%OutOfRange>MaxTg] decimal (not null)
> Target decimal (not null)
> [Min] decimal (not null)
> [Max] decimal (not null)
>
> The table DATAS has 4000000+ records
> I'm running this query:
> SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.Description
> FROM Items as I, Datas as D
> WHERE D.IDF_Item = I.ID AND I.IDF_Family = 84
> AND D.Date BETWEEN '5/18/2004' AND '5/18/2004'
> it's taking 4-5 minutes to run.
> The result is correct, there is no thing on that date.
> I've done a reindex, but still the same thing.

A shot in the dark: change 84 to convert(numeric, 84). If that does not
cut it, please answer the questions below:

How many rows are there in Items?

Exactly what indexes are there on the table? Please indicate which
indexes that are clustered.

Can you run the query preceeded by SET STATISTICS PROFILE ON, and
post the output?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>A shot in the dark: change 84 to convert(numeric, 84). If that does not
>cut it, please answer the questions below:
Nothing changed.

>How many rows are there in Items?
30

>Exactly what indexes are there on the table? Please indicate which
>indexes that are clustered.
On DATAS ID is a clustered Index

>Can you run the query preceeded by SET STATISTICS PROFILE ON, and
>post the output?
01SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.Description FROM
Items as I, Datas as D WHERE D.IDF_Item = I.ID AND I.IDF_Family =
convert(numeric, 84) AND D.Date BETWEEN '5/18/2006' AND
'5/18/2006'210NULLNULLNULLNULL18.225489NULLNULLNULL63.874825NULLNULLSELECT0NULL
01 |--Nested Loops(Inner Join, OUTER
REFERENCES:([D].[IDF_Item]))231Nested LoopsInner JoinOUTER
REFERENCES:([D].[IDF_Item])NULL18.2262020.07.6185526E-511063.874825[I].[Description],
[I].[IDF_Family], [I].[ZDID], [I].[ID]NULLPLAN_ROW01.0
01 |--Sort(DISTINCT ORDER BY:([D].[IDF_Item]
ASC))243SortDistinct SortDISTINCT ORDER BY:([D].[IDF_Item]
ASC)NULL18.2262021.1261261E-24.4788996E-41663.866585[D].[IDF_Item]NULLPLAN_ROW01.0
01 | |--Clustered Index
Scan(OBJECT:([AccessReporting].[dbo].[Datas].[PK_Data] AS [D]),
WHERE:([D].[Date]='May 18 2006 12:00AM'))254Clustered Index
ScanClustered Index
ScanOBJECT:([AccessReporting].[dbo].[Datas].[PK_Data] AS [D]),
WHERE:([D].[Date]='May 18 2006 12:00AM')[D].[Date],
[D].[IDF_Item]41.49002556.7212834.96644977861.687733[D].[Date],
[D].[IDF_Item]NULLPLAN_ROW01.0
00 |--Clustered Index
Seek(OBJECT:([AccessReporting].[dbo].[Items].[PK_Items] AS [I]),
SEEK:([I].[ID]=[D].[IDF_Item]), WHERE:([I].[IDF_Family]=84) ORDERED
FORWARD)263Clustered Index SeekClustered Index
SeekOBJECT:([AccessReporting].[dbo].[Items].[PK_Items] AS [I]),
SEEK:([I].[ID]=[D].[IDF_Item]), WHERE:([I].[IDF_Family]=84) ORDERED
FORWARD[I].[Description], [I].[IDF_Family], [I].[ZDID],
[I].[ID]1.06.3284999E-37.9603E-51018.1532737E-3[I].[Description],
[I].[IDF_Family], [I].[ZDID], [I].[ID]NULLPLAN_ROW018.226202|||Tried this simple query

SELECT D.ID
FROM Datas as D
WHERE D.Date BETWEEN '5/18/2006' AND '5/18/2006'

And still took me 3 minutes, So maybe the problem is with the index on
DATAS|||Solved Created a non clustered index for Date and IDF_Items.