Re: query the Elmah_Errors.AllXml column and extract data from it using a View

[James_2JS]

Hi there Mark,

A couple of suggestions... based on some guesswork here!

1) My guess is that the query is doing a full table scan on dbo.Elmah_Error - check the explain plan to verify this.

You should add an index to dbo.Elmah_Error on Type ASC, Sequence DESC

This will mean that SQL Server can go directly to the relevant entries and serve them in the desired order direct from the index.

Once done, check the explain plan again and hopefully it should be using the index! :O)

2) I'm not as sure on this one, and don't have a SQL instance here to test it out, but...

You've got 17 CAST(AllXml AS XML) statements.

I don't know if SQL optimises that down into a single cast, or in fact executes it 17 times!

So it might be worth doing a CROSS APPLY to perform the cast... see: http://stackoverflow.com/questions/15136345/converting-a-string-to-xml-datatype-before-querying-in-t-sql

But that answer also warns about the performance issues in casting to XML and suggest looking here: http://dba.stackexchange.com/questions/34700/adding-indices-to-views/34716#34716

Which in turn has a further explanation here: http://sqlblog.com/blogs/paul_white/archive/2012/09/05/compute-scalars-expressions-and-execution-plan-performance.aspx

I hope this points you on the right track!!

Cheers,

James

On Thursday, April 4, 2013 7:40:38 PM UTC+1, Mark Kamoski wrote:

Dear Elmah Users -- 

Please help.

I need to query the Elmah_Errors.AllXml column and extract data from it using a View in SQL Sever.

Below is one solution; but, it is slow.

Do you know of a way to make a faster query?

USE [TestDatabase1]

GO

/****** Object:  View [dbo].[SiteStatsView]    Script Date: 04/04/2013 14:28:44 ******/

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SiteStatsView]'))

DROP VIEW [dbo].[SiteStatsView]

GO

USE [TestDatabase1]

GO

/****** Object:  View [dbo].[SiteStatsView]    Script Date: 04/04/2013 14:28:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[SiteStatsView]

AS

SELECT     TOP (100) PERCENT 

Sequence, 

TimeUtc, 

ErrorId, 

[Type], 

[User], 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''APPL_PHYSICAL_PATH'']/value/@string)[1]', 'nvarchar(max)') AS ApplPhysicalPath, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''AUTH_TYPE'']/value/@string)[1]', 'nvarchar(max)') AS AuthType, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''HTTP_HOST'']/value/@string)[1]', 'nvarchar(max)') AS HttpHost, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''HTTP_REFERER'']/value/@string)[1]', 'nvarchar(max)') AS HttpReferer, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''LOCAL_ADDR'']/value/@string)[1]', 'nvarchar(max)') AS LocalAddr, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''PATH_INFO'']/value/@string)[1]', 'nvarchar(max)') AS PathInfo, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''PATH_TRANSLATED'']/value/@string)[1]', 'nvarchar(max)') AS PathTranslated, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''QUERY_STRING'']/value/@string)[1]', 'nvarchar(max)') AS QueryString, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''REMOTE_ADDR'']/value/@string)[1]', 'nvarchar(max)') AS RemoteAddr, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''REMOTE_HOST'']/value/@string)[1]', 'nvarchar(max)') AS RemoteHost, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''REMOTE_PORT'']/value/@string)[1]', 'nvarchar(max)') AS RemotePort, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''REQUEST_METHOD'']/value/@string)[1]', 'nvarchar(max)') AS RequestMethod, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''SCRIPT_NAME'']/value/@string)[1]', 'nvarchar(max)') AS ScriptName, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''SERVER_NAME'']/value/@string)[1]', 'nvarchar(max)') AS ServerName, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''SERVER_PORT'']/value/@string)[1]', 'nvarchar(max)') AS ServerPort, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''SERVER_PROTOCOL'']/value/@string)[1]', 'nvarchar(max)') AS ServerProtocol, 

CAST(AllXml AS XML ).value('(/error/serverVariables/item[@name=''URL'']/value/@string)[1]', 'nvarchar(max)') AS Url

FROM         dbo.Elmah_Error

WHERE     (Type = 'Team.Test.BusinessLayer.BusinessEntities.ElmahSiteStatisticsException')

ORDER BY Sequence DESC

GO

Please advise.

Thanks.

-- Mark Kamoski

[Atif Aziz]

Hi Mark,

This seems like more of a question for a SQL Server discussion group than something specific to ELMAH (except the error table structure and indices but then again that's publicly available for anyone to peek). I think you'll benefit from a larger audience and pool of experience by generalising your question and posting it to a SQL Server mailing list.

- Atif

P.S. You might also find the ELMAH Log Analyzer project interesting.

--
You received this message because you are subscribed to the Google Groups "ELMAH" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elmah+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.