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.
| Author |
Topic |
|
mattboy_slim
Yak Posting Veteran
72 Posts |
 Posted - 2013-06-26 : 16:18:11
|
| I have a table that runs anywhere from 500,000 records to 1,000,000 records. The table is constantly updated with event start times and event stop times. Event start times are INSERTed without heed. The event stop times UPDATE the event in the following manner: (*note that these queries are performed via web-based code)Step 1:SELECT TOP 1 f_ID from tb_events WHERE f_eventtype = 'x' AND f_computername = 'y' AND f_endtime IS NULL ORDER BY f_starttime DESCStep 2:UPDATE tb_events SET f_endtime = ? WHERE f_ID='%recordfromquery%'These 2-step queries run pretty consistently throughout the day, causing high CPU utilization (I assume due to the inefficiency of the two-step query, but also due to the sheer number of records involved).I believe I have two options (both may be valid):1) Combine the query into a single one to update the end time of the event to the corresponding start time2) Use a temporary table where all events are dumped into a) In the temporary table, complete the same process as above b) Create a SQL job to move only completed events (where f_starttime and f_endtime are NOT NULL) into the final table c) Clean out the records from the temporary table that have been moved into the final table. This will keep the temporary table small and shouldn't involve as much CPUOf course, I could be completely off-base with both of the above self-recommendations. If the above recommendations sound OK, please let me know how I can combine the two queries into a single one (to start with).Thanks in advance,Matt |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-26 : 17:30:19
|
| Some tiems you have to pick your poison with tuning queries: CPU vs IO trade offs.Is that predicate correct: WHERE f_ID='%recordfromquery%' Or is it doing a LIKE? |
 |
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-06-26 : 17:50:39
|
| Sorry, the %recordfromquery% isn't the actual syntax - it was meant to indicate a variable obtained from the first step (sorry, should have clarified) |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-06-27 : 12:41:59
|
| Firstly , check the execution plan . Are you seeing any Hash Joins, Sorts, Filters - which are common cause - although not the only ones. Step 1 - can you run that more efficiently - ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
High-CPU problem - bad query?