|
|
Posted by Erland Sommarskog on 05/19/06 00:48
bie2 (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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|