Any index or other reccomendations on this?

[BlakeK]

I have a query that currently takes around 5 minutes to return results.
I have run it through the Index Tuning wizard (on Thorough) and it had no recommendations.
I will layout the query, the field types, and the current indexes. If you have any thoughts, please let me know.

Here is the query:

SELECT tbl_server_logs.server_id, Count(tbl_server_logs.content_id) AS Total
FROM tbl_server_logs, tbl_advertisement
WHERE tbl_advertisement.content_id = tbl_server_logs.content_id
AND tbl_server_logs.zone_id = 1
AND tbl_server_logs.play_time > '2/1/2005' 
And tbl_server_logs.play_time < '3/1/2005'
AND tbl_server_logs.log_type_id = 9
GROUP BY server_install_id

On tbl_advertisement:
- The primary key is a field called ad_id (int), not used in this query
- the field content_id (int) is a UNIQUE index

On tbl_server_logs:
- The primiary key consists of 4 fields: server_install_id (int), zone_id(int), play_time(datetime), and log_type_id (int)
- There are 3 other indexes on this table which have been implemented for other queries:
1.) play_time, server_install_id, log_type_id, content_id
2.) server_install_id, play_time, log_type_id, content_id
3.) content_id, server_install_id, play_time, log_type_id
All fields in the above 3 indexes are "Ascending" and none are UNIQUE

Concerning table data, tbl_advertisement currently contains 4,518 rows and tbl_server_logs currently contains 227,158,649 rows

Any suggestions on what might improve this query execution time would be greatly appreciated.
If there is any details that I left out that might be helpful, let me know.
Thanks,
Blake

 

[bob120579]

First, try a join on contentID and a BETWEEN in the WHERE clause for the play_time

 

[donutman]

I don't see how that even works. You have a column in the Select list that isn't in the Group By list. If that's a typo, then try using an explicit join and see if that helps. I don't think it will, but it's worth a shot.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[BlakeK]

donutman,
That is a typo. DOH! (But just here on this topic, the query I am running is right. I should have cut and pasted instead of typing it!)
The field in the SELECT should be server_install_id, the saem as the GROUP BY field.

I changed the query as bob120579 suggested to use a JOIN and the BETWEEN operator. Is this the correct form:

SELECT server_install_id, Count(tbl_server_logs.content_id) AS Total
FROM tbl_server_logs INNER JOIN tbl_advertisement
ON tbl_advertisement.content_id = tbl_server_logs.content_id
AND tbl_server_logs.zone_id = 1
AND tbl_server_logs.play_time BETWEEN '2/1/2005' AND '3/1/2005'
AND tbl_server_logs.log_type_id = 9
GROUP BY server_install_id

The original query took 5 minutes 8 seconds to run.
The modified query using INNER JOIN and BETWEEN has been running for 10 minutes and still hasn't finsihed. And all users are gone for the day, so server activity is only me right now.

 

[BlakeK]

FYI, modified query finally return after 14 minutes and 29 seconds.

 

[donutman]

[Blue]SELECT[/Blue] server_install_id[Gray],[/Gray] 
       [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]tbl_server_logs.content_id[Gray])[/Gray] [Blue]AS[/Blue] Total
[Blue]FROM[/Blue] tbl_server_logs [Blue]INNER[/Blue] [Gray]JOIN[/Gray] tbl_advertisement
   [Blue]ON[/Blue] tbl_advertisement.content_id [Gray]=[/Gray] 
      tbl_server_logs.content_id
[Blue]WHERE[/Blue] tbl_server_logs.zone_id [Gray]=[/Gray] 1 [Gray]AND[/Gray]
      tbl_server_logs.play_time [Gray]>[/Gray][Gray]=[/Gray] [red]'2/1/2005'[/red] [Gray]AND[/Gray]
      tbl_server_logs.play_time [Gray]<[/Gray] [red]'3/1/2005'[/red] [Gray]AND[/Gray]
      tbl_server_logs.log_type_id [Gray]=[/Gray] 9
[Blue]GROUP[/Blue] [Blue]BY[/Blue] server_install_id

Generally, it's better not to use Between.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Porpose of inner join here is only to filter rows that have valid content_id. So if data integrity is OK (foreign key?), join is not necessary (WHERE.... AND content_id IS NOT NULL is enough).

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[BlakeK]

I tried the query using the INNER JOIN and not using the BETWEEN...

SELECT server_install_id,
       COUNT(tbl_server_logs.content_id) AS Total
FROM tbl_server_logs INNER JOIN tbl_advertisement
   ON tbl_advertisement.content_id =
      tbl_server_logs.content_id
WHERE tbl_server_logs.zone_id = 1 AND
      tbl_server_logs.play_time >= '2/1/2005' AND
      tbl_server_logs.play_time < '3/1/2005' AND
      tbl_server_logs.log_type_id = 9
GROUP BY server_install_id

And that took 12 minutes and 34 seconds.
So far, my original query using WHERE clause join is the quickest at 5 minutes+.
Any other recommendations of things to try, let me know.
The users don't have much love for a report that takes 5 minutes to run! LOL.

 

[donutman]

BlakeK, vongrunt is suggesting that you remove INNER JOIN tbl_advertisement and the On clause. First try that and see how much improvement that provides, then you can think about the implications of removing the join.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[BlakeK]

Karl,
That is how I originally had it structured (without an INNER JOIN)...

SELECT tbl_server_logs.server_id, Count(tbl_server_logs.content_id) AS Total
FROM tbl_server_logs, tbl_advertisement
WHERE tbl_advertisement.content_id = tbl_server_logs.content_id
AND tbl_server_logs.zone_id = 1
AND tbl_server_logs.play_time > '2/1/2005'
And tbl_server_logs.play_time < '3/1/2005'
AND tbl_server_logs.log_type_id = 9
GROUP BY server_install_id

Is that what he meant, or is there another way to write it it without an INNER JOIN that I am missing?

 

[donutman]

SELECT server_id, Count(content_id) AS Total
FROM tbl_server_logs
WHERE 
    zone_id = 1
AND play_time > '2/1/2005'
And play_time < '3/1/2005'
AND log_type_id = 9
GROUP BY server_install_id

You had an implicit join.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[BlakeK]

Karl,
That JOIN is necessary because the field content_id in tbl_server_logs contain values that link to 3 different possible tables.
There is a table tbl_advertisement, a table tbl_entertainment, and a table tbl_images which all contain the field content_id.
The content_id in tbl_server_logs could be any of those 3, that is why the link to tbl_advertisement is required.

Thanks