sql server – Date index SEEK is chosen when using OPTION RECOMPILE but not with OPTION OPTIMIZE FOR

I have a table containing 10 years worth of ‘package scans’. Somebody scans a package and it records the date and username. Let’s pretend for now that retaining 10 years of data actually has a purpose.

I have a page to show a summary for the past week, so clearly I only want to read 1 week’s worth of data.

Here’s the query, to be run in SSMS twice, once with a hardcoded recent date and again with an old date in 2013. It’s originally a parameterized query, but in SSMS I’m replacing @p0 with the date:

SELECT (t0).(VerifyDate), (t0).(PackageId), (t0).(Username)
FROM (dbo).(PackageVerification) AS (t0)
INNER JOIN (dbo).(Package) AS (t1) ON (t1).(PackageId) = (t0).(PackageId)

WHERE ((t1).(PackageStatus) <> 99) AND ((t0).(VerifyDate) > @p0)   
ORDER BY (t0).(VerifyDate) DESC

Before I execute it, I’d like to introduce my date index.

Now my date index is not on my PackageVerification table, but instead is on a ‘helper view’ which performs the same join seen above. The query above is able to magically use this indexed view because I have SCHEMABINDING enabled.

CREATE NONCLUSTERED INDEX (IX_Helper_PackageVerification_USER_SCAN_HISTORY) ON (dbo).(Helper_PackageVerification)
(
    (VerifyDate) DESC,
    (PackageStatus) ASC
)
INCLUDE (
    (VerifyDateDate),
    (Username)
) 

When I run the query in SSMS with an old and new date it uses scan or seek as expected. The threshold seems to be somewhere around 2015. So anything remotely recent should definitely be using a seek. Here’s the results of that:

enter image description here

When I run it as a parameterized query from my application I always get a full scan, which for some reason uses a parallelized plan.

At least it’s using my helper index.

enter image description here

I’m actually not sure why I don’t get parameter sniffing for this. I always pass a very recent date so I would have thought it may have preferred a scan but I’m fine with it choosing the above plan given the circumstances. There’s a million+ rows and it takes about 150ms.

Incidentally this is a SQL Azure database with 2vCores. Parameter sniffing is enabled and parameterization is set to simple.

If I change the query and run my application using OPTION (RECOMPILE) I do get the desired SEEK and a very good performance of just a few ms. The recompile time seems to be negligible and frankly this is perfectly fine performance I can use.

When I look in query store I can verify OPTION RECOMPILE uses the seek for a recent date, and scan for an old date! Awesome.

However, and I’ve never tried this before – I thought how about improving it even further with OPTION (OPTIMIZE FOR @p0 = '4/1/2021').

I was expecting this to also use the seek, but without the need for recompilation every time. I’d just periodically change the date passed to OPTIMIZE FOR – maybe to the beginning of the previous month.

However, this is the query in the query store.

enter image description here

And it goes and does a full scan of all 1+ million rows when setting the date parameter to 4/7/21!

So now I’m lost. I’ve tried to read about everything I can on the subject but haven’t come across this issue. RECOMPILE works, but OPTIMIZE FOR doesn’t seem to do anything when I’m expecting it to effectively simulate running the query in SSMS with hardcoded values.

how can i change whats on a page depending on the date and time

Hi all

Basically, I’m trying to create a website that promotes events in the city I live in. I want to have it so that the event elements only show on a page for as long as they are current events and can be replaced with upcoming events. I’m currently learning HTML, CSS and python but not been able to find a solution.

each HTML page would have about 8 small event elements that would link to either another page on my site or an external website

Thanks in advance for any advice people can give me. I’m not looking for someone to give me the code but to point me in the right direction
SEMrush

 

Conditional formatting of a field on a sharepoint list by month of a date

as you can see in the picture, I have a date type field and I would like this field only to be displayed in orange when the month of the date is equal to the month of the current date but I don’t not see how to do it. Anyone have any idea for this?

=

microsoft excel – Find latest (date) entry

I am trying to bring back a score associated with a company but I only want the latest entry. If there is no score at all, I want the cell to be blank.

Companies Score Date
A 3.5 2/1/2020
A 4.3 1/16/2019

In this case, if I wanted Company A’s score, I would like it to return the 3.5. If I am looking up Company D, I would like the cell to remain blank.

Thank you so much for any help in advance!!

What to put for primary occupation in the DS-160 form when uni date has been pushed back?

I was supposed to attend university starting fall 2020 but due to the pandemic my final semester exams were postponed and I had to defer my admission to spring 2021.

So, I graduated in September and I’m not working anywhere. I have my F1 visa interview in a week. Should I select ‘not employed’ and specify the reason that I recently graduated and am awaiting university to start? Or should I select the student option and fill in the details of the university I’m intending to attend in spring?

How up to date do bank statements need to be for a UK Visa reapplication?

So a month ago I applied for a UK visa, I was declined due to a simple mistake that I have now rectified around an invitation letter not specifying the dates. All my forms and supporting documents are in order however I noticed that my bank statement for my savings account is now not as recent.

When I applied last time my last transaction on said statement was October 21st, only one transaction has happened since then in the savings account however from the date of my new application (November 9th) obviously some time has passed.

The question is will the officer in charge consider the statement too old now as 19 days have passed? Will I need to travel to get another set of statements from my home branch that will provide only one very small transaction?

Choosing a specific future date using Microsoft Dynamics CRM Advanced Search

Is it possible to use the advanced filter to select a specific future date, dynamically?
For example if I want to return all data with a “date” field populated for 30 days from today, but no data from before or after that?
From what I am seeing the only filter option similar is “Next X Days” but it’s inclusive of all days to that point.

google sheets – getting the latest date of something happening (with conditions)

So basically, Ive got two sheets. One (sheet_1) with every single record in the system (over 11000 rows) and the other one (sheet_2) is querying information from that sheet. The relevant information to know is that sheet_1 has 3 columns of interest to me; name of person, thing he/she did and the date he did it. Now in sheet_2, im attempting to create a sort of report about that person, showing all information from sheet_1 that is relevant to that person.

Ive made sort of a statistics section in sheet_2 which shows the incident type, number of times its happened and when was the last time it did happen. What i cant figure out is how to do the “when was the last time it did happen” column.

My first thought was an if function but that didnt work since there was no logic there, so ill give you a more clear explanation of what should happen in the cell:

Cell is supposed to first check the type of incident and the name of the person (both in sheet_2) then cross reference that with sheet_1 and get the “maximum or latest date” for that type of incident for that specific person.

windows command prompt – I need to rename a file and insert a date

I’m trying to set a daily process to locate a file, rename it and insert the previous date in the file name. I want the file to be named “Filename_MMddyyyy.csv” with MMddyyyy being the previous day. I managed to rename the file but I’m a total noob and I don’t know how to calculate the previous day date and insert it in the file name. Here is what I managed to do so far:

cd C:

set file= “pathtomyfile*.CSV”
if exist %file% (
ren “pathtomyfile*.CSV” “Filename.CSV”
)

Thank you very much in advance.

sharepoint online – Using date commands in calculated fields

I am following this article:
Examples of common formulas in lists

A simple sample is this:

=DATEDIF((Dato1),(Dato2),"d")

However when I save the column I get following error:

Sorry, something went wrong The formula contains a syntax error or is not supported.

If I just do this it works:

=(Dato1)-(Dato2)

However I need to calculate hours and days between two dates so I need some of the commands.

Any pointer what I am doing wrong?