score:0
I added some more indexes to the database and it sped things up considerably. Execution time is now about 20 seconds (!!). I'll admit that a lot of the added indexes were guesswork (or just adding them randomly).
Read More
- SQL Server - Nested query takes 40 minutes to run
- SQL Server Stored Proc takes (much) longer to run than the same query executed from text in Management Studio
- The Same SQL Query takes longer to run in one DB than another DB under the same server
- Query in a batch takes from few millisec to 2 minutes on SQL Server
- sql server a simple query takes forever to run due to transaction isolation level
- Strange error occurring when using wcf to run query against sql server
score:0
Do you really need to aggregate twice? Would this query give you the same results?
SELECT
T2.ForumId,
Forums.Title,
T2.ForumThreads,
T2.ForumPosts,
T2.ForumStart,
T2.ForumStop
FROM
Forums
INNER JOIN (
SELECT
Min(ThreadStart) As ForumStart,
Max(ThreadStop) As ForumStop,
Count(*) As ForumThreads,
Sum(ThreadPosts) As ForumPosts,
Threads.ForumId
FROM
Threads
INNER JOIN (
SELECT
Posts.DateTime As ThreadStart,
Posts.DateTime As ThreadStop,
Count(*) As ThreadPosts,
Posts.ThreadId
FROM
Posts
) As P2 ON Threads.ThreadId = P2.ThreadId
GROUP BY
Threads.ForumId
) AS T2 ON T2.ForumId = Forums.ForumId
score:0
If you denormalize by adding ForumId to the Posts table, you would be able to query all of the stats straight out of the Posts table. With the right index, this would probably perform pretty well. Of course, that will require a small change to your code to include the ForumId when inserting into the Posts table...
Similar Question and Answer
- SQL Server query dry run
- SQL query times out when run from C#, fast in SQL Server Management Studio
- Return SQL Server database query with nested Json
- SQL Server view takes a long time to alter but query itself finishes quickly?
- SQL Server query takes longer with parameter than with constant string
- Query to Convert Varchar HH:MM to Integer Minutes in SQL Server 2008
- Select DATEADD minutes with query SQL Server 2008
- MS SQL - Query takes too long to run
score:1
Have you tried putting those 2 derived table in a #temp table? SQL Server will get statistics (single column) from them and you can lay indexes on them as well.
Also, at first sight indexed views could help here as you have lots of aggregates.
score:1
How about something like this? Anyway, you get the idea...
SELECT f.ForumID,
f.Title,
MIN(p.[DateTime]) as ForumStart,
MAX(p.[DateTime]) as ForumStop,
COUNT(DISTINCT f.ForumID) as ForumPosts,
COUNT(DISTINCT t.ThreadID) as ForumThreads
FROM Forums f
INNER JOIN Threads t
ON f.ForumID = t.ForumID
INNER JOIN Posts p
ON p.ThreadID = p.ThreadID
GROUP BY f.ForumID, f.Title
score:1
Indexes may work when you do SELECT FROM
, but the results of the sub-queries are not indexed. The join to them is probably killing the performance.
As buckley suggested, I'd try storing the intermediate results in a #temp table and adding an index before doing the final query.
But the outer SELECT
doesn't include thread-specific information. It looks like the query is just selecting the min/max date by forum. If so, you can just get the min/max/count posts grouped by forum.
More questions
- sql query takes much long time compared to next run
- How can I enforce a query timeout for MS SQL Server queries run via the MS JDBC Driver?
- Is there any way to view the last run sql query in sql server managment studio
- Query in sql server 2012 with datetime between takes too long with millisecond difference
- Is there any way/tool to identify estimate query run time in SQL sERVER
- Sql server takes too long time for update query to execute for few rows
- SQL Server query performance: Nested cursors
- Show how long a query has to run in SQL Server
- SQL Server 2005 - Nested recursive query :(
- Temporary table in SQL Server - error occurs if the query takes long time to complete
- Run SQL query with minimum match criteria in sql server 2008
- SQL Server 2008 - Query takes forever to finish even though work is actually done
- how to run a parallel query on sql server 2008?
- Being unable to run query in Microsoft SQL Server
- Select DATEADD minutes with query SQL Server 2005
- How can i run a sql query on a schedule without using sql server agent jobs( & no BI too) and pump the results in an excel file
More questions with similar tag
- How to convert multiple bit fields (8 per set) from database to byte in model
- Entity Framework: query against view in EF (C# code) returns duplicate results
- Select Statement where 2 values have Counts
- How to select group of codes and represent a specific weight in SQL?
- Would it ever be wise to have a SQL server per web server?
- Best way looking for changes on SQL Server?
- Delete Filegroup and File from database
- Handling objects being different for different SQL Server Versions
- How do I transform the data in this SQL Table into a specific format?
- How to deal with Date Format of SQL Server?
- SQL ALL IN clause
- disable/enable auto increment in MSAccess via query
- SQL Server: how to constrain a table to contain a single row?
- Convert Datetime to Varchar exactly as is
- Date time convert and retain blank
- How to remove time portion from Nullable<DateTime> in LINQ
- How to configure Passportjs with SQL Server?
- aggregate values in SQL columns
- Sql Server 2008 MERGE - best way to get counts
- Can I retrieve a value from a previous line using CURSORs of SQL?
- compare records in the same table and calculate difference at different dates
- How to save data getting from nested json in sql server
- SQL Server migrate new data into old schema
- TSQL Conditional Where Based on Another Table
- How to make 2 sql queries 1
- Find common data between groups
- rolling up data and getting SUM group by
- How do I stop and start a SQL Azure database?
- C# Dataset DataTable position from DB
- LINQ to SQL - Insert Custom Raw SQL?