0

To fetch any record from table that are either inserted or updated, I am using created on date to get inserted records and updated on date to get rows in which any column is updated.

Simple select query is: 

SELECT *
FROM [Table] WITH (NOLOCK)
WHERE ([Table].CreatedOnDate >= @Date OR [Table].UpdatedOnDate >= @Date)

(@Date is just a parameter passed to the stored procedure)

Now this table has grown to over 30 million rows and this query is timing out.

I have read about rowversion at MSDN and here

Can I use SQL Server's [Rowversion] column instead of these two date columns to prevent time outs? Is Rowversion faster for searching then date search?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
hassan ammar
  • 11
  • 3
  • 1
    You're the one with the tables and data. You're the one who can, reasonably easily, set up a comparison and determine the answers to your questions. – Damien_The_Unbeliever Jan 25 '18 at 07:13
  • 2
    To support Damien_The_Unbeliever: Read [Race your horses](https://ericlippert.com/2012/12/17/performance-rant/) – Shnugo Jan 25 '18 at 07:28
  • 1
    Silly question: Is there an index on the two date columns you are targeting? Did you check the actual execution plan? – Shnugo Jan 25 '18 at 07:32
  • 2
    `ROWVERSION` does **NOT** store date and time - it's a **binary counter** - so this is **NOT** something you can easily use to do date range searching .... – marc_s Jan 25 '18 at 07:47
  • 1
    Also, if possible, do *not* use `SELECT *` - but select **only** those columns you really need. If you do this, you might be able to create a **covering index** that contains all the columns you need, to speed up selection quite a bit ! – marc_s Jan 25 '18 at 07:52
  • 3
    I would create a (persisted) computed column that stores the "relevant" date for a change (so either createdondate or updatedondate) and create an index on that column. Thus the condition can be simplified to `where relevant_change_date >= @Date` which very often can be better optimized than an `OR` condition –  Jan 25 '18 at 07:56

0 Answers0