This query times out - any way to optimize ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

This query times out - any way to optimize ?

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-24 : 11:38:55

Hey, my page is timing out a lot with the below query. Can you guys see any improvements that can be made to it optimize it or would I be better to purge the tables since they have gotten really large

'INSERT INTO #TempTable(id, dateAdded, numPageViews, numSearches, numEstablishmentViews) ' +
'SELECT tblSiteLogs.id, tblSiteLogs.dateAdded, COUNT(tblPageVisitLogs.id) AS numPageViews, (SELECT COUNT(id) AS numRecords FROM tblSearchQueries WHERE (establishmentId IS NULL) AND (tblSearchQueries.siteLogId = tblSiteLogs.id) GROUP BY siteLogId) AS numSearches, (SELECT COUNT(id) AS numRecords FROM tblSearchQueries WHERE (establishmentId IS NOT NULL) AND (tblSearchQueries.siteLogId = tblSiteLogs.id) GROUP BY siteLogId) AS numEstablishmentViews ' +
'FROM tblSiteLogs LEFT OUTER JOIN tblPageVisitLogs ON tblSiteLogs.id = tblPageVisitLogs.siteLogId ' +
@search +
'GROUP BY tblSiteLogs.id, tblSiteLogs.dateAdded ' +
@order

Thanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-24 : 12:22:40

You have 2 almost identical queries in the section:
(SELECT COUNT(id) AS numRecords FROM tblSearchQueries WHERE (establishmentId IS NULL) AND (tblSearchQueries.siteLogId = tblSiteLogs.id) GROUP BY siteLogId) AS numSearches,
(SELECT COUNT(id) AS numRecords FROM tblSearchQueries WHERE (establishmentId IS NOT NULL) AND (tblSearchQueries.siteLogId = tblSiteLogs.id) GROUP BY siteLogId)

You can replace these two seperate searches with a single search:
SELECT COUNT(id), Establishment from (
SELECT case when establishmentId is null then null else 'Not Null' end as Establishment
FROM tblSearchQueries
WHERE (tblSearchQueries.siteLogId = tblSiteLogs.id)
GROUP BY siteLogId) AS Est
Group By establishmentId

Use this as a derived table to get numRecords and numEstablishmentViews with a single lookup of the table tblSearchQueries.

You could also post table definition scripts to get advice on indexes - also say aprox how big each table is.

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-24 : 12:35:13

Size of the tables are around 691MB and 364MB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 13:32:59

can you tell what is the need of dynamic sql here? what will @search contain? also you could replace the subqueries with join which will perform better when number of records is large. something like


SELECT tblSiteLogs.id, tblSiteLogs.dateAdded,t1.numPageViews ,
 tmp.numSearches ,tmp.numEstablishmentViews 
FROM tblSiteLogs 
LEFT OUTER JOIN (
SELECT siteLogId,COUNT(tblPageVisitLogs.id) AS numPageViews 
FROM tblPageVisitLogs 
GROUP BY siteLogId)t1
ON tblSiteLogs.id = t1.siteLogId
LEFT OUTER JOIN (
SELECT siteLogId ,
COUNT( CASE WHEN establishmentId IS NULL  THEN id ELSE NULL END) AS numSearches,
COUNT( CASE WHEN establishmentId IS NOT NULL  THEN id ELSE NULL END) AS numEstablishmentViews 
FROM tblSearchQueries 
GROUP BY siteLogId
)tmp
ON tmp.siteLogId = tblSiteLogs.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources