is it ok to have a clustered index on recorddate? I have two rows per device per day for the recorddate.

this is how devicehistory is indexed

indexes on the devicehistory table are as follows:

primary key = id

recorddate ascending = non unique, non clustered

datesubmitted ascending = non unique, non clustered

archived ascending =...

I havent had a chance to setup a test environment yet. I was hoping to find something a little simpler to do, but it doesnt seem that there will be!

just wanted to bump and get any other suggestions before I try what Vic has recommended.

vicdileo (5/4/2012)

---

My suggestion for exploring indexed views is based on the query plans you provided which indicate that the indexes are being used in your query (I didn't see any...

sorry, I misunderstood.

here are two of them. explan is the original query, and explan1 is a larger version of the query that took 4 minutes to run.

vicdileo (5/1/2012)

---

RE:

8) if so, then what does the query plan look like when your WHERE IN has 16,000 deviceid's?

its actually 1600+, Ive attached one for 720 devices

I don't see the...

Lynn Pettis (5/1/2012)

---

First, how quickly does the following run:

SELECT

deviceid,

archived,

AVG(CASE WHEN recorddate > DATEADD(day, -7, getDate()) THEN d2.estgasvolmcf ELSE...

7) does the query plan you provided come from the same query that appears in your post?

yes

8) if so, then what does the query plan look like when your...

vicdileo (5/1/2012)

---

Thanks for providing the query plan and other info.

Regarding the query plan:

I don't see any table scans, so that's good.

It looks like the query plan is probably not being...

Lowell (5/1/2012)

---

two issues i see:

two different WHERE statements have a function on a datetime column...

WHERE DATEADD(day, 15, time) > GETDATE()

--and later:

AND CONVERT(DATE, recorddate) = '2012-04-25'

this requires SQL to do...

1) Copy the plan and post it here.

attached

How is this query being run:

2) As a stored procedure?

no

3) AS embedded SQL in a C# program or Java, etc.?

yes, java

4) Is this...

bump. can anyone point me in the right direction?