SQL query speedup/optimization

Question

I am using SQL Server 2008 R2.I have tried to improve the following query in different methods.

Method 1:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*)  FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'  ) 
AND [date]>=dateadd(d,-7,cast(getdate()as date)))  

Method 2:
 DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*)  FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'  )
 AND [prglog].dbo.[errorlog].[Date] BETWEEN dateadd(d,-7,cast(getdate()as date)) and cast(getdate()as date) )  

Method 3:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'   ) 
AND id>=(SELECT MIN([errorlog].[id]) FROM [prglog].dbo.[errorlog] WHERE ( [prglog].dbo.[errorlog].[Date] >= dateadd(d,-7,cast(getdate()as date)) )) )  

Method 4:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'   ) 
AND id>=(SELECT MIN([errorlog].[id]) FROM [prglog].dbo.[errorlog] WHERE ([prglog].dbo.[errorlog].[Date] BETWEEN dateadd(d,-7,cast(getdate()as date)) and cast(getdate()as date) ) ) )

Method 5:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM (SELECT  * FROM [prglog].dbo.[errorlog] WHERE  [prglog].dbo.[errorlog].[Date] >= dateadd(d,-7,cast(getdate()as date)) )tbl WHERE ( tbl.[Errordescription] LIKE '%General network error%')

The errorlog table contains more than 150000 rows. And when i check the result of the queries in client statistics it is almost the same.Is there any other method to improve the query?

The table structure is :

USE [prglog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[errorlog](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [PCname] [varchar](50) NULL,
    [Username] [varchar](50) NULL,
    [Date] [date] NULL,
    [Time] [varchar](50) NULL,
    [Errordescription] [varchar](max) NULL,
    [LineNo] [varchar](max) NULL,
    [Errorno] [varchar](50) NULL,
    [Procedure] [varchar](255) NULL,
    [Formname] [varchar](50) NULL,
    [Productname] [varchar](50) NULL,
    [Commandstring] [varchar](max) NULL,
    [Exename] [varchar](50) NULL,
    [ExePath] [varchar](255) NULL,
    [ErrorDetails] [xml] NULL,
    [F6 Columns] [varchar](255) NULL,
    [Stage details #] [varchar](255) NULL,
    [F8 Columns] [varchar](8000) NULL,
    [F8 Given By] [varchar](255) NULL,
    [Pro Remarks] [varchar](8000) NULL,
    [Final F6] [bit] NULL,
    [DBname] [varchar](50) NULL,
    [Tablename] [varchar](50) NULL,
    [Serious Error] [tinyint] NULL,
    [Stage #] [tinyint] NULL,
    [Process Id] [int] NULL,
 CONSTRAINT [PK_ErrorLog] 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] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [DF_errorlog_Final F6]  DEFAULT ((0)) FOR [Final F6]
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [DF_prglog_Serious Error]  DEFAULT ((0)) FOR [Serious Error]
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [DF_errorlog_Stage #]  DEFAULT ((1)) FOR [Stage #]
GO
USE [prglog]
GO
CREATE NONCLUSTERED INDEX [Date] ON [dbo].[errorlog] 
(
    [Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE PRIMARY XML INDEX [ErrorDetails] ON [dbo].[errorlog] 
(
    [ErrorDetails]
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
CREATE NONCLUSTERED INDEX [Final F6] ON [dbo].[errorlog] 
(
    [Final F6] ASC
)
WHERE ([Final F6]=(0))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
USE [prglog]
GO
CREATE NONCLUSTERED INDEX [Procedure] ON [dbo].[errorlog] 
(
    [Procedure] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Sometimes when we check Client statistics for performance analysis of the query it gives variations in results from trials to trial.How to check the performance of the query?

I suggest you create a clustered index on the date column and get rid of the primary key since a log table isn't relational in nature. — Dan Guzman
– Dan Guzman, Commented Jul 3, 2020 at 11:20
do you always look for the same string - LIKE '%General network error%'? — NikitaSerbskiy
– NikitaSerbskiy, Commented Jul 3, 2020 at 21:28
You could try to declare a variable for: dateadd(d,-7,cast(getdate()as date)) and use the variable in your query. I guess getdate() is non-deterministic, so it may have to be calculated for each row. — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Jul 4, 2020 at 8:55

Collin · Accepted Answer · 2021-01-12 22:11:32Z

Not enough rep to comment, so posting here. Execution plans would likely help understanding.

I wonder if the DATEADD function in the WHERE clause is messing with SARGability? Also, if most of your queries which search via [date] also search via [Errordescription] or vice versa, I would test creating a covering index on those. Not having more knowledge of data, exec plans, or workload characteristics of this table, it's difficult to say overall benefit. Try something like this:

DECLARE @ThresholdDate DATE
SELECT @ThresholdDate = DATEADD(d,-7,cast(getdate()as date))

SELECT count(*)  
FROM [prglog].dbo.[errorlog] 
WHERE  [date]>=@ThresholdDate  
   AND [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'

Stack Exchange Network

SQL query speedup/optimization

1 Answer 1

Your Answer

Hot Network Questions

SQL query speedup/optimization

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions