Hi Friends,
One of my projects need help in solving the following issue.
Our database contains a table with columns of xml type. These xml columns contain large xml documents.
For example: A Single row in the table is as below.
Id
GuId
License Info
Profile Info
Transaction Info
Product Info
1
G01
<License…..>
------
</License>
<Profile….
…….
</Profile>
<Transactions….>
………..
………
</Transactions>
<Product ….
……..
</Product>
Above 4 columns contains large xml documents.
This table is filled by another stored procedure, which collects data from multiple tables and forms xml documents (using 'for xml' in select query) and inserts into the above table. So the size of the xml documents is not fixed.
Table definition is as below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Requests](
[ID] [bigint] NOT NULL,
[GUID] [uniqueidentifier] NOT NULL,
[License Info] xml NULL,
[Profile Info] xml NULL,
[Transaction Info] xml NULL,
[Product Info] xml NULL,
CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
'ID' has primary key as well as clustered index
'GuId' has non clustered index.
Database: SQL Server 2005
Front-End: .Net ( WCF) 2005/ 2008
Language: C#
Requirement:
Our WCF has to fetch the above 4 xml documents from the database table. And we have to return this to client application. We are not displaying that data anywhere; our job Is just pull the data from the database and pass it to the client applications (Web/ Windows).
We will develop the client app later. Based upon the output from the WCF (DataSet/ Xml/ Object) the methods in the client application will be developed.
Problem:
When the xml columns contain a huge xml document, even simple select query is also taking approximately 25 minutes to retrieve the data in SQL Server Management Studio.
Always we will fetch the data by passing the Request Id only.
Ex: Select * from Request (NoLock) where Id = 1
Need help in:
How to retrieve the data from the above table as fast as possible, irrespective of the size of the xml document in those columns?
After retrieving the data from the database, what is the best way/ format that our WCF will use to pass it back to the client application?
Please respond with your suggestions as early as possible.
Thanks,
Madhu