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 |
|
DLTaylor
Posting Yak Master
136 Posts |
 Posted - 2011-04-15 : 07:57:04
|
| Hello All,Im using SQL 2005 sp3I have a table containing a field ‘RowUpdateDateTime’ (a datetime data type)Brilliantly; it tells me when each Row of data was last ‘updated’ within the tableSometimes the data ‘stops’ updating, and I would like to know when this happens.Using the query below I can see when no records have been updated in the last 4 hoursSELECT * FROM TableName WHERE RowUpdateDateTime > DATEADD(HOUR, -4, GETDATE())And with some modification I can attach it to a Job and it emails me when No records exist.This works.My question is;Is there a more efficient way to write the select query to scan the table to see if no records have been updated in last 4 hours?I ask as it takes 50 seconds to scan 40 million records… and I don’t want to be interfering with the table running the check every hour…The field data type is; datetime and is not part of the clustered Index & I cant modify existing indexesAny advice would be great!ThanksDan |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-15 : 08:17:14
|
| FOr something like this I would use a nolock hint - something I usually avoid except for monitoring where you don't mind the occasional incorrect result.if not exists(SELECT * FROM TableName (nolock)WHERE RowUpdateDateTime > DATEADD(HOUR, -4, GETDATE())This will stop as soon as it finds a row, but if the column is not indexed it wil probably scan most of the table.Another option is to add a trigger to the table which updates a last inserted date table but that might slow down the inserts too much.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
DLTaylor
Posting Yak Master
136 Posts |
Posted - 2011-04-15 : 08:54:53
|
| excellent - will try this out.Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-15 : 09:33:53
|
quote:
I cant modify existing indexes
Can you add a new index for just that column? And if you don't need all the columns from that table, INCLUDE the columns you do need? You'll probably get a nice index seek that way. |
|
|
|
DLTaylor
Posting Yak Master
136 Posts |
Posted - 2011-04-15 : 10:45:21
|
| That would be a good idea - only the tables are 3rd party and not mine to touch (only query ;-)) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-15 : 10:49:57
|
Stoooooooooooopid 3rd parties! Technically, you're not touching the table, just adding a new index.  Maybe you can ask them to add it to their product. I'm sure they'll love to charge you for it. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-04-15 : 11:19:49
|
When I ask vendors about creating indexes, they usually say no.Then I do it anyway, and don't tell them.Most of the time, I don't bother to ask; I figure I know more about identifying needed indexes than they do, so why ask if I'm going to do it anyway.I've never had one complain, but if they do, I would just say, "Hum, don't how that got there. Did one of your support people do it?"  CODO ERGO SUM |
|
|
|
|
|
|
|
|
More efficient select datetime query