0

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?

  • 2
    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 Jul 3 at 11:20
  • do you always look for the same string - LIKE '%General network error%'? – Nikita Jul 3 at 21:28
  • No.The string will be different. – IT researcher Jul 4 at 8:17
  • 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 Jul 4 at 8:55
0

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]>=@DateThreshold  
   AND [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.