By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Help to optimize query

P: n/a
Hi,
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.

What can I do?

Thanks

May 18 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
bie2 (Fr*************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 18 '06 #2

P: n/a
>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?

0 1 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 =
convert(numeric, 84) AND D.Date BETWEEN '5/18/2006' AND
'5/18/2006' 2 1 0 NULL NULL NULL NULL 18.225489 NULL NULL NULL 63.874825 NULL NULL SELECT 0 NULL
0 1 |--Nested Loops(Inner Join, OUTER
REFERENCES:([D].[IDF_Item])) 2 3 1 Nested Loops Inner Join OUTER
REFERENCES:([D].[IDF_Item]) NULL 18.226202 0.0 7.6185526E-5 110 63.874825 [i].[Description],
[i].[IDF_Family], [i].[ZDID], [i].[ID] NULL PLAN_ROW 0 1.0
0 1 |--Sort(DISTINCT ORDER BY:([D].[IDF_Item]
ASC)) 2 4 3 Sort Distinct Sort DISTINCT ORDER BY:([D].[IDF_Item]
ASC) NULL 18.226202 1.1261261E-2 4.4788996E-4 16 63.866585 [D].[IDF_Item] NULL PLAN_ROW 0 1.0
0 1 | |--Clustered Index
Scan(OBJECT:([AccessReporting].[dbo].[Datas].[PK_Data] AS [D]),
WHERE:([D].[Date]='May 18 2006 12:00AM')) 2 5 4 Clustered Index
Scan Clustered Index
Scan OBJECT:([AccessReporting].[dbo].[Datas].[PK_Data] AS [D]),
WHERE:([D].[Date]='May 18 2006 12:00AM') [D].[Date],
[D].[IDF_Item] 41.490025 56.721283 4.9664497 78 61.687733 [D].[Date],
[D].[IDF_Item] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index
Seek(OBJECT:([AccessReporting].[dbo].[Items].[PK_Items] AS [i]),
SEEK:([i].[ID]=[D].[IDF_Item]), WHERE:([i].[IDF_Family]=84) ORDERED
FORWARD) 2 6 3 Clustered Index Seek Clustered Index
Seek OBJECT:([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.0 6.3284999E-3 7.9603E-5 101 8.1532737E-3 [i].[Description],
[i].[IDF_Family], [i].[ZDID], [i].[ID] NULL PLAN_ROW 0 18.226202

May 19 '06 #3

P: n/a
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

May 19 '06 #4

P: n/a
Solved Created a non clustered index for Date and IDF_Items.

May 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.