Click here to Skip to main content
15,904,416 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello All,
I have following table structure with more than 10 lakh records and i want to find out MAX Rainfall by every day with group by AWS_Id and AddeedOn as Date. I have tried but gives me error like
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


CREATE TABLE [dbo].[AWS_Responce2](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AWS_Id] [nvarchar](50) NULL,
	[Temperature] [decimal](10, 4) NULL,
	[Humidity] [decimal](10, 4) NULL,
	[Rainfall] [decimal](10, 4) NULL,
	[WindDirection] [decimal](10, 4) NULL,
	[WindSpeed] [decimal](10, 4) NULL,
	[Radiation] [decimal](10, 4) NULL,
	[Luminiousity] [decimal](10, 4) NULL,
	[SensorFault] [nvarchar](50) NULL,
	[IsActive] [bit] NULL,
	[IsDeleted] [bit] NULL,
	[AddedOn] [datetime] NULL,
	[AddedBy] [int] NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [int] NULL,
 CONSTRAINT [PK_AWS_Responce2] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [PK_AWSAddedOn]    Script Date: 11/29/2021 9:20:59 PM ******/
CREATE NONCLUSTERED INDEX [PK_AWSAddedOn] ON [dbo].[AWS_Responce2]
(
	[AWS_Id] ASC,
	[AddedOn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]




Please give me the solution to find out max value.

What I have tried:

WITH CTE AS
(
SELECT Rainfall,AddedOn,AWS_Id
FROM AWS_Responce2
WHERE cast(AddedOn as date) between DATEADD(DD, -1, '2021/01/01') AND '2021/11/10' AND IsDeleted = 0 AND AWS_ID = '29420467921183302'
)
SELECT 
Max(Rainfall) as Rainfall,
CAST(AddedOn as date) as AddedOn,AWS_Id
FROM CTE
GROUP BY cast(AddedOn as date),AWS_Id
ORDER BY cast(AddedOn as date) DESC;
Posted
Updated 29-Nov-21 6:12am

1 solution

Quote:
SQL
cast(AddedOn as date) between DATEADD(DD, -1, '2021/01/01') AND '2021/11/10'
That's a likely problem - calling a function on a column in your WHERE clause will make the query non-SARGable.

Instead, use:
SQL
AddedOn >= '20201231' And AddedOn < '20211111'

You might also want to create a filtered index[^], and include columns to make sure it covers your query:
SQL
CREATE NONCLUSTERED INDEX IX_AWSAddedOn_NotDeleted ON dbo.AWS_Responce2
(
	AWS_Id,
	AddedOn
)
INCLUDE
(
    Rainfall
)
WHERE
    IsDeleted = 0
;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900