query taking a lot of time.

Question

query taking a lot of time.

Bhuvnesh

SSC Guru

Points: 59351
More actions
January 29, 2010 at 3:19 am

#103892

CREATE TABLE [dbo].[LOG_EVENT](
[LOG_DATE] [datetime] NOT NULL,
[LOG_SEQ] [int] IDENTITY(1,1) NOT NULL,
[GLOBAL_SESSION_ID] [varchar](50) NULL,
[CLIENT_APP_NAME] [varchar](20) NULL,
[CLIENT_APP_HOST] [varchar](15) NULL,
[CLIENT_APP_USER_ID] [varchar](20) NULL,
[CLIENT_APP_USER_IP_ADDRESS] [varchar](15) NULL,
[CLIENT_APP_TIMESTAMP] [datetime] NULL,
[SERVER_HOST] [varchar](20) NULL,
[SERVER_DOMAIN] [varchar](20) NULL,
[COMPONENT_NAME] [varchar](50) NULL,
[INTERACTION_PROCESS_NAME] [varchar](50) NULL,
[INTERACTION_STEP] [varchar](50) NULL,
[INTERACTION_REQUEST_ID] [varchar](50) NULL,
[TRANSACTION_ERROR_CODE] [varchar](10) NULL,
[EVENT_STATUS] [varchar](20) NULL,
[EVENT_START_TIMESTAMP] [datetime] NULL,
[EVENT_DURATION] [int] NULL,
[USER_CREATED] [varchar](30) NOT NULL,
[DATE_CREATED] [datetime] NOT NULL DEFAULT (getdate()),
[LAYER_TYPE] [varchar](20) NULL,
CONSTRAINT [LOG_EVENT_PK] PRIMARY KEY CLUSTERED
(
[LOG_DATE] ASC,
[LOG_SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE NONCLUSTERED INDEX [ix_missing_index_679003] ON [dbo].[LOG_EVENT]
(
[GLOBAL_SESSION_ID] ASC,
[COMPONENT_NAME] ASC,
[INTERACTION_REQUEST_ID] ASC,
[LAYER_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [FG_INDEXES]
SELECT CONVERT(VARCHAR(10),log_date,101),avg(event_duration) respTime from log_event nolock
where log_date > = '2009-12-16 00:00:00' and
component_name like '%Payment%'
group by CONVERT(VARCHAR(10),log_date,101)
I have above query that’s taking 1 min 45 sec to execute
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'm not sure what to suggest here since you are getting a clustered index seek in the execution plan. I was thinking that the LIKE operator with leading wild cards might be causing an issue.

One thing I do see in the execution plan is that the estimated rows for the index seek is saying 1 and the >= operator should return more than 1 row, so perhaps your statistics are out of date.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

I would think the text conversion in the SELECT list and GROUP BY is killing you. Use the DATEADD/DATEDIFF trick to get whole dates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Here... update your stats, defrag your indexes, and try this out...

WITH

ctePreCondition AS

(

SELECT DATEADD(dd,DATEDIFF(dd,0,log_date),0) AS Date,

event_duration

FROM dbo.log_event WITH(NOLOCK)

WHERE log_date >= CAST('2009-12-16' AS DATETIME)

AND component_name LIKE '%Payment%'

)

SELECT CONVERT(VARCHAR(10),Date,101), AVG(event_duration) AS respTime

FROM ctePreCondition

GROUP BY Date,

ORDER BY Date

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Does updating statistics on the table Log_Event help? The estimated rows are 1 and it looks like the type of index that will be susceptible to stale stats

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Was that the actual execution plan or the estimated execution plan attached in the first post? The reason why I ask is because all of the rows have been estimated as "1".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Jeff Moden (1/30/2010)
Was that the actual execution plan or the estimated execution plan attached in the first post?

Estimated plan. In an actual plan there will be both estimates rows and actual rows listed.

The estimation wouldn't be any different if it were the actual plan. We'd just be able to see how far off the estimate is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Bhuvnesh (1/29/2010)
SELECT CONVERT(VARCHAR(10),log_date,101),avg(event_duration) respTime from log_event nolock
where....

I'm assuming that it isn't a problem that this query may return inaccurate results

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

No, query is returning correct results.

i have planned to add event_duration column as include column.

Will it help ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

Bhuvnesh (1/31/2010)
No, query is returning correct results.

It may be now, as written it may not always however. Nolock means to SQL Server 'get me the data fast, I don't care if it's slightly inaccurate occasionally'

i have planned to add event_duration column as include column.
Will it help ?

Seeing as you currently have a clustered index seek, unlikely.

Did you try updating statistics? Did it make any difference? If you have updated statistics, post the actual exec plan of the query after doing so. I want to see how the estimated row count has changed and how it relates to the actual row count.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass