search

HangfireIO / Hangfire

An easy way to perform background job processing in .NET and .NET Core applications. No Windows Service or separate process required
https://www.hangfire.io
Other
9.44k stars 1.71k forks source link

SQL Query on Hangfire.State table is very slow #2342

Closed Shibshankar01 closed 10 months ago

Shibshankar01 commented 10 months ago

I am making a hangfire job that will mail me the summary about all the failed jobs in the last 12 hours. To do this I want data from the Hangfire.State (Data Column). And, whenever I execute the query to fetch data from the State table using the Id column, the query takes a lot of time and I get TimeOutException. Since I don't have Entities for the Hangfire tables in .Net I cannot use repository pattern. How can I handle this?

odinserj commented 10 months ago

Hi @Shibshankar01, can you send me the query you are executing and the output of the following query to understand what schema version are you using for Hangfire?

SELECT * FROM [HangFire].[Schema]
Shibshankar01 commented 10 months ago

Hi @odinserj, The query is "Select * From Hangfire.State nolock where Id in ({Ids})". Ids is comma seperated StateIds received from Hangfire.Job Table for all the failed Jobs. And the [Hangfire].[Schema] Version is 7. Thanks in advance.

odinserj commented 10 months ago

State table has a composite primary key, try querying by both JobId or Id, in this case there will be an index seek operation, not scanning through all the records.

Or, much better, see the Job table as it has current state name associated, and query against it instead.

odinserj commented 10 months ago

*by both JobId and Id

Shibshankar01 commented 10 months ago

Hi @odinserj , Changed the query to get the data based on both Id and JobId in the Hangfire.State table and the query is much faster. Thanks for the quick response.