Sunday, June 2, 2013

[SQL Server 2008 issues] Xml Query Performance Sql Server

[SQL Server 2008 issues] Xml Query Performance Sql Server

Xml Query Performance Sql Server

Posted: 01 Jun 2013 09:52 AM PDT

Hello, I have the below data structureThe query is taking a while to run. I have a primary xml index, secondary xml indexes, and selective xml index on the table, (which sped it up alot), but it is the Wildcard that is really hurting the performance of this query. I have tried rewriting the query as well, but they were still running slow. Can the wildcard be removed and still return expected results?I am running this on both Sql Server 2008 and 2012Xml Schema -- This is slimmed down version there are more Forms Elements. [code="sql"]USE tempdbGOSET NOCOUNT ONGOIF OBJECT_ID('[dbo].[XmlTable]') IS NOT NULLDROP TABLE [dbo].[XmlTable]GOCREATE TABLE [dbo].[XmlTable]( [XmlId] [int] IDENTITY(1,1) NOT NULL, [XmlDocument] [xml] NOT NULL, CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED ( [XmlId] 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]GOINSERT INTO [dbo].[XmlTable] ( XmlDocument )SELECT '<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" Number="0" SchemaVersion="0.1" Settings="Testing" Title="Ordering"> <dev:Forms FormId="A1"> <dev:A1 ItemNumber="1" ItemGuid="{F402C584-555E-4D07-8C35-E88889B9DA44}"> <dev:Codes>D</dev:Codes> <dev:Required>true</dev:Required> <dev:Informational>false</dev:Informational> <dev:Visitors> <dev:Visitor Name="Dev01" Location="STLRF"> <dev:Divisions> <dev:Division Number="1" Name="TFR3" Usage="Monitor"> <dev:Description>Development Fundamentals</dev:Description> </dev:Division> <dev:Division Number="2" Name="DEF32" Usage="Monitor"> <dev:Description>Testing Fundamentals</dev:Description> </dev:Division> <dev:Division Number="3" Name="DEP13" Usage="None"> <dev:Description>Guided Fundamentals</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> <dev:Visitor Name="Dev02" Location="STLRF"> <dev:Divisions> <dev:Division Number="1" Name="TFR3" Usage="Monitor"> <dev:Description>Development Fundamentals</dev:Description> </dev:Division> <dev:Division Number="2" Name="DEF32" Usage="Monitor"> <dev:Description>Testing Fundamentals</dev:Description> </dev:Division> <dev:Division Number="3" Name="DEP13" Usage="None"> <dev:Description>Guided Fundamentals</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> <dev:Visitor Name="Dev03" Location="FGRTY"> <dev:Divisions> <dev:Division Number="1" Name="TFR3" Usage="Monitor"> <dev:Description>Development Fundamentals</dev:Description> </dev:Division> <dev:Division Number="2" Name="DEF32" Usage="Monitor"> <dev:Description>Testing Fundamentals</dev:Description> </dev:Division> <dev:Division Number="3" Name="DEP13" Usage="None"> <dev:Description>Guided Fundamentals</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> </dev:Visitors> <dev:Senders> <dev:Sender Name="FGY(14A)" /> </dev:Senders> </dev:A1> </dev:Forms> <dev:Forms FormId="A2"> <dev:A2 ItemNumber="1" ItemGuid="{3563F33E-B03A-4859-850E-A87D35BD8562}"> <dev:Codes>C</dev:Codes> <dev:Required>true</dev:Required> <dev:Informational>false</dev:Informational> <dev:Remarks>Support</dev:Remarks> <dev:Notes>Ready</dev:Notes> <dev:Visitors> <dev:Visitor Name="GHFF"> <dev:Divisions> <dev:Division Number="0" Name="Trial" Usage="None"> <dev:FromLocation>LOPO</dev:FromLocation> <dev:ToLocation>RDSS</dev:ToLocation> <dev:Description>Rich Filter</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> </dev:Visitors> <dev:Senders> <dev:Sender Name="W33R" /> </dev:Senders> <dev:IsReady>true</dev:IsReady> <dev:IsCall>false</dev:IsCall> </dev:A2> <dev:A2 ItemNumber="2" ItemGuid="{CCFB2D5D-A23E-412D-8541-536451873713}"> <dev:Codes>A</dev:Codes> <dev:Required>true</dev:Required> <dev:Informational>false</dev:Informational> <dev:Remarks>Loader Ready</dev:Remarks> <dev:Notes>Ready</dev:Notes> <dev:Visitors> <dev:Visitor Name="UDT"> <dev:Divisions> <dev:Division Number="0" Name="Trial" Usage="None"> <dev:FromLocation>TYUJ</dev:FromLocation> <dev:ToLocation>DETF</dev:ToLocation> <dev:Description>Web Enhance</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> </dev:Visitors> <dev:Senders> <dev:Sender Name="RJ4" /> </dev:Senders> <dev:IsReady>true</dev:IsReady> <dev:IsCall>false</dev:IsCall> </dev:A2> </dev:Forms></dev:Doc>' GO[/code]The best working query[code="sql"];WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )SELECT a.value('@Number[1]', 'int') as Number ,b.value('(@FormId)[1]', 'NVARCHAR(50)') Form ,c.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid ,c.value('@ItemNumber[1]', 'INT') AS ItemNumber ,d.value('(@Name)[1]','nvarchar(50)') As Visitor ,d.value('(@Location)[1]','nvarchar(50)') As Location ,e.value('(@Name)[1]', 'NVARCHAR(50)') As Sender From XmlTable As X CROSS APPLY Xmldocument.nodes('Doc') As aa(a) CROSS APPLY a.nodes('Forms') As bb(b) CROSS APPLY b.nodes('*') As cc(c) CROSS APPLY c.nodes('Visitors/Visitor') as dd(d) CROSS APPLY c.nodes('Senders/Sender') as ee(e)[/code]attempted rewrite #1 Including the Visitor and Sender path in the value select slowed it down alot, plus some can contain more than one like A1 has multiple Visitor, so they must be in the nodes method.[code="sql"];WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )SELECT a.value('@Number[1]', 'int') as Number ,a.value('(Forms/@FormId)[1]', 'NVARCHAR(50)') Form ,b.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid ,b.value('@ItemNumber[1]', 'INT') AS ItemNumber ,b.value('(Visitors/Visitor/@Name)[1]', 'NVARCHAR(50)') AS Visitor ,b.value('(@Location)[1]','nvarchar(50)') As Location ,b.value('(Senders/Sender/@Name)[1]', 'NVARCHAR(50)') As Sender From XmlTable As X CROSS APPLY Xmldocument.nodes('Doc') As aa(a) CROSS APPLY a.nodes('Forms/*') As bb(b)[/code]attempted rewrite #2[code="sql"];WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )SELECT a.value('@Number[1]', 'int') as Number ,b.value('(@FormId)[1]', 'NVARCHAR(50)') Form ,c.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid ,c.value('@ItemNumber[1]', 'INT') AS ItemNumber ,c.value('(Visitors/Visitor/@Name)[1]', 'NVARCHAR(50)') AS Visitor ,c.value('(@Location)[1]','nvarchar(50)') As Location ,c.value('(Senders/Sender/@Name)[1]', 'NVARCHAR(50)') As Sender From XmlTable As X CROSS APPLY Xmldocument.nodes('Doc') As aa(a) CROSS APPLY a.nodes('Forms') As bb(b) CROSS APPLY b.nodes('*') As cc(c)[/code]Expected Results[code="sql"]Number Form ItemGuid ItemNumber Visitor Location Sender0 A1 F402C584-555E-4D07-8C35-E88889B9DA44 1 Dev01 STLRF FGY(14A)0 A1 F402C584-555E-4D07-8C35-E88889B9DA45 1 Dev02 STLRF FGY(14A)0 A1 F402C584-555E-4D07-8C35-E88889B9DA46 1 Dev03 FGRTY FGY(14A)0 A2 3563F33E-B03A-4859-850E-A87D35BD8562 1 GHFF NULL W33R0 A2 CCFB2D5D-A23E-412D-8541-536451873713 2 UDT NULL RJ4[/code]

tempdb is full

Posted: 31 May 2013 09:50 PM PDT

Msg 1105, Level 17, State 2, Line 2Could not allocate space for object 'dbo.SORT temporary run storage: 141509403869184' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. when I am executing a query with large tables , I am getting the above error message though I am not creating any temporary tables. I sharank the db and files but the problems persists.Any suggestions .Thanks in advance.

which one is best query?

Posted: 31 May 2013 08:42 PM PDT

first queryselect COUNT(id) from tbl_birthmaster1 where status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where isnull(pdffilepath,'')!='' and status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where cfcApprove=0 and status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where cfcApprove=1 and mcaApprove=0 and status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where FormType=1 and mcaApprove=1 and DRApprove=0 and status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where FormType=2 and mcaApprove=1 and SDOApprove=0 and status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where FormType=3 and mcaApprove=1 and CRApprove=0 and status_info=1 and zone_id=1 second one is--SELECT sum(CASE WHEN status_info = 1 and zone_id=1 then 1 else 0 end) AS count1, sum(CASE WHEN isnull(pdffilepath,'')!='' AND status_info=1 and zone_id=1 then 1 else 0 end) AS count2, sum(CASE WHEN cfcApprove=0 and status_info=1 and zone_id=1 then 1 else 0 end) AS count3, sum(CASE WHEN cfcApprove=1 and mcaApprove=0 and status_info=1 and zone_id=1 then 1 else 0 end) AS count4, sum(CASE WHEN FormType=1 and mcaApprove=1 and DRApprove=0 and status_info=1 and zone_id=1 then 1 else 0 end) AS count5, sum(CASE WHEN FormType=2 and mcaApprove=1 and SDOApprove=0 and status_info=1 and zone_id=1 then 1 else 0 end) AS count6, sum(CASE WHEN FormType=3 and mcaApprove=1 and CRApprove=0 and status_info=1 and zone_id=1 then 1 else 0 end) AS count7FROM tbl_birthmaster1

Divide the result of count function

Posted: 29 May 2013 04:09 AM PDT

Hi all,I have a question I hope the community can help me with. I have a query with count function and the result is 97, I need divide the result like this:Quant name30 cccccc30 cccccc30 cccccc7 cccccc

Best way to find certain records.

Posted: 17 May 2013 08:48 AM PDT

I have to find certain data from a table based on date range & and activity.So, if a records is 60 days old for a certain activity and do not fall within 90 & 120 days for other activity and do not have anything less than 60 days as create date, we should delete it.The script is to crete table & data is as below.So from the given example we have to delete only client id 5.[code="sql"]Create table TestPurgeClient(ClientId int,ActivityId int,CreateDate Datetime)--select * from TestPurgeClient--Allinsert into TestPurgeClient values(1,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(1,33, '2013-02-16 00:00:00.000')insert into TestPurgeClient values(1,34,'2013-01-17 00:00:00.000')-- 60 day + Currentinsert into TestPurgeClient values(2,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(2,72,GETDATE())--60 + 90 dayinsert into TestPurgeClient values(3,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(3,33, '2013-02-16 00:00:00.000')--All + currentinsert into TestPurgeClient values(4,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(4,33, '2013-02-16 00:00:00.000')insert into TestPurgeClient values(4,34,'2013-01-17 00:00:00.000')insert into TestPurgeClient values(4,34,GETDATE())--60 Day Onlyinsert into TestPurgeClient values(5,72, '2013-03-18 00:00:00.000')[/code]

How DBCC Checkdb works internally?

Posted: 31 May 2013 07:36 PM PDT

Can someone please help me understand how DBCC checkdb runs internally? Does it run in tempdb or does it run within the datafile itself? And how much space is needed to run the Checkdb (i mean, does it need the total size of the database? or only data or index) ?? Also is database accessible while running Checkdb? Thanks,Pradeep
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog