troubleshooting Question

SQL query running slow in smaller dataset then a larger one.

Avatar of electricink
electricinkFlag for Canada asked on
Microsoft SQL Server 2008Windows Server 2008
9 Comments1 Solution329 ViewsLast Modified:
Ok have an odd problem with either with my code or my table. We are  tracking each view of a page by a separate database entry which logs the id of the vehicle the view, the datetime field, and their ip / other things and gives it a record id.

Table format
| Recordid | CarId | DateViewed | Ip | refferer | useragent |

Dataset to be returned for this sql statement.
| Count | Date |

We have been running the same sql code for some time that grabs all the views from a certain time period and gives back the number of views within that time as a count for each (day/month/hour). This has worked fine and is still working fine for any period that is before 2009.

Now were having a problem, the data returned for Jan 2009 is taking about 1 minute to return, while i can go back to december of last year and it works within 2 seconds. There is no error, besides the slowness it takes to process much less data. 2008 contains 38000 records, 2009 so far contains 368 records.

Can anyone tell me if there is something in the sql that i can modify and get it faster or tell me a reason that this same sql works very fast on 2008 records, but slow as dirt on 2009?




'ASP
	mySql = "SELECT count(cc.ip) AS c, MAX(LEFT(cc.dateviewed,6)) AS m FROM " & varDomCode & "_car_counter cc, " & varDomCode & "_carinfo ci WHERE MONTH(cc.dateviewed) = '" & curmonth & "' AND YEAR(cc.dateviewed) = '" & curyear & "' AND cc.carid = ci.recordid AND ci.dealercode = '" & varDC & "' GROUP BY cast(floor(cast(cc.dateviewed AS FLOAT)) AS DATETIME)"
 
SQL SERVER
SELECT count(cc.ip) AS c, MAX(LEFT(cc.dateviewed,6)) AS m FROM wo_car_counter cc, wo_carinfo ci WHERE MONTH(cc.dateviewed) = '12' AND YEAR(cc.dateviewed) = '2008' AND cc.carid = ci.recordid AND ci.dealercode = 'dm' GROUP BY cast(floor(cast(cc.dateviewed AS FLOAT)) AS DATETIME)
stats.jpg
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros