May 3, 2012 at 6:15 am
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 attachment for 720 devices.
sorry, I got tied up yesterday. here it is.
May 3, 2012 at 6:23 am
That's not what people are asking for.
Please run the query with 'actual execution plan' enabled, right click the plan, save as and save it as a .sqlplan file. Attach that plan file to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 7:39 am
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.
May 3, 2012 at 11:34 pm
The query plans certainly concur with your statement that the first outer join is the slowest - it's reading 160 million rows via the index (IX_devicehistory).
Just to be clear - here is the outer join that I'm referring to:
SELECT deviceid, archived, AVG(CASE WHEN recorddate > DATEADD(day, -7, getDate())...
One recommendation comes to mind - using an indexed view as a substitute for that first outer join. In simple terms (you said you're a beginner at SQL), an indexed view is a copy of a specified subset of the data, kept in a separate location in a table-like structure. Indexed views can be great performance enhancers for aggregations like the AVG in your outer join. An indexed view is like a regular view, except that the data in an indexed view is a physical copy of the data in the base tables, whereas a regular view is like a SQL statement that you can refer to by name - a kind of "virtual table."
Honestly, this is a complicated topic for someone who is a beginner with databases so quite a bit of reading and experimentation may be necessary to feel comfortable with the indexed view concept. Also, keep in mind that a lot of solutions (including this recommended one) can sound great but they come at some cost or tradeoff. Sometimes the performance benefits outweigh the resource consumption costs, sometimes not.
It's crucial that you understand the basics of views and of database performance in general to properly evaluate or test indexed views as a performance solution. Here are some sources to get you started on indexed views, as they are called in SQL Server, and called a "materialized views" in most of the other RDBMS brands:
An introduction (a good summary of the cons as well as the pros) -
http://beyondrelational.com/modules/24/syndicated/507/posts/12398/sql-server-materialized-views.aspx
A bit more detailed explanation -
http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer
The technical details -
http://msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx
Let me know if you need further clarification.
Thanks,
- victor di leo
May 4, 2012 at 5:26 am
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 table scans or "clustered index scans", which is a table scan on a table that has a clustered index). The list of indexes you provided look like good indexes and it appears that update statistics is being run with reasonable frequency.
May 4, 2012 at 5:54 am
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 table scans or "clustered index scans", which is a table scan on a table that has a clustered index). The list of indexes you provided look like good indexes and it appears that update statistics is being run with reasonable frequency.
thanks for the info, i was reading thru the 2nd link and saw this:
This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped.
I am assuming that they mean changing the design of a table(adding columns, etc) and not talking about just typical inserts and updates.
May 4, 2012 at 7:08 am
diat150 (5/4/2012)
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 table scans or "clustered index scans", which is a table scan on a table that has a clustered index). The list of indexes you provided look like good indexes and it appears that update statistics is being run with reasonable frequency.thanks for the info, i was reading thru the 2nd link and saw this:
This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped.
I am assuming that they mean changing the design of a table(adding columns, etc) and not talking about just typical inserts and updates.
You are correct. "Schema" refers to table structures. More on schema bound views:
http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/
May 7, 2012 at 6:27 am
just wanted to bump and get any other suggestions before I try what Vic has recommended.
May 9, 2012 at 7:52 pm
How are things coming along?
Do you have a development environment where you can safely practice the concepts and examples given in the links I provided?
If you do give this a try, feel free to follow-up, or run the query plan and post it here.
May 10, 2012 at 8:18 am
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!
May 10, 2012 at 10:00 am
I agree, it's not simple. I avoid solutions like this until there is no other practical choice, so I'm glad you're looking for other solutions before trying this one.
I would go through examples and understand them thoroughly before implementation, if you do choose to go this route.
Also, consider that the problem may lie elsewhere - for example, it may be more system-wide: your server resources may be maxed-out, making the queries with the heaviest load take a conspicuously long time to finish.
Perhaps tempdb is overloaded, or your server is cpu-bound, or there are too many jobs running simultaneously on the server.
May 10, 2012 at 3:41 pm
How is "devicehistory" clustered?
If it's not clustered, or clustered by something irrelevant like an identity column, you could cluster it by recorddate and vastly speed up your queries.
Also, you should specify the schema on the table names, as otherwise SQL must search for other possible tables first, and cannot share the resulting query plan.
If you don't want the query plan shared, as is likely in this case, I still suggest adding the schemas to the table names, and explicitly adding the OPTION RECOMPILE to force a new plan for every run.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 10, 2012 at 8:05 pm
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 = non unique, non clustered
deviceid ascending = non unique, non clustered
im not sure what you mean by specifying the schema, can you go into a little more detail?
thanks.
May 10, 2012 at 8:42 pm
diat150 (5/10/2012)
this is how devicehistory is indexedindexes on the devicehistory table are as follows:
primary key = id
recorddate ascending = non unique, non clustered
datesubmitted ascending = non unique, non clustered
archived ascending = non unique, non clustered
deviceid ascending = non unique, non clustered
im not sure what you mean by specifying the schema, can you go into a little more detail?
thanks.
The best way to explain is to show. In the code below, I am not specifying the schema in which the table resides:
SELECT
deviceid,
archived,
AVG(CASE WHEN recorddate > DATEADD(day, -7, getDate()) THEN d2.estgasvolmcf ELSE NULL END) AS week_mcf_avg,
AVG(d2.estgasvolmcf) AS month_mcf_avg
FROM
devicehistory d2 -- << Look here
WHERE
recorddate > DATEADD(day, -30, getDate())
GROUP BY
deviceid,
archived
In the code that follws now, I am specifying the schema, as well as using the table alias to indecate which table (only one in this case) that each column belongs:
SELECT
d2.deviceid,
d2.archived,
AVG(CASE WHEN recorddate > DATEADD(day, -7, getDate()) THEN d2.estgasvolmcf ELSE NULL END) AS week_mcf_avg,
AVG(d2.estgasvolmcf) AS month_mcf_avg
FROM
dbo.devicehistory d2 -- << Look here
WHERE
d2.recorddate > DATEADD(day, -30, getDate())
GROUP BY
d2.deviceid,
d2.archived
May 10, 2012 at 9:18 pm
I would try the suggestions by lowell first
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply