It's slow to use ServerXMLHTTP to submit a query to SQLXML virtual directory

I'm using ServerXMLHTTP object in an ASP page (web server) to submit a query to our database server (SQL Server 2000) via SQLXML virtual directory. The ServerXMLHTTP object will return me more than 5000 rows in XML format which is about 15M in size.

The problem is, it takes 1 minute for the ServerXMLHTTP object to get the response from SQLXML web service. That makes our web application not workable because it's really slow.

However, if I use XMLHTTP object instead of ServerXMLHTTP object, it only takes seconds to finish the same query. I know these 2 objects are implemented in different ways. XMLHTTP is designed for client applications and relies on URLMon, which is built upon Microsoft Win32 Internet (WinInet). ServerXMLHTTP is designed for server applications and relies on a new HTTP client stack, WinHTTP. ServerXMLHTTP offers reliability and security and is server-safe. So I'd better use ServerXMLHTTP in my web application if I know how to solve the speed issue.

Can somebody help me out? Thank you very much in advance. This problem happens recently. The program had been working for 3 years.

The url opened by ServerXMLHTTP object is like http://myserver/myvd?sql=select * from staff where gender='M' and staff_id<5000 for xml auto&root=Root

Here are some things to look at.

ServerXMLHTTP can have proxy issues. Check if the time is spent making the connection. [more]

It can take 15+ seconds to negotiate certificates. Are certificates involved? If so, are you reusing the ServerXMLHTTP object? If so does the delay occurs only on the first use of the ServerXMLHTTP object?

If it's possible in your scenario (not enough info for me to tell), you can use ServerXMLHTTP in asynchronous mode to increase concurrency in your app (work while you wait).

HTH!

Tim

sql

It's a computed column processing bug?

Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is

Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now I create two tables and try four select queries:

Code Snippet

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

GO

create table t1 (id int primary key, id_bigint as cast(id as bigint))

GO

create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)

GO

select * from t1 -- (1)

-- Clustered index scan with two times Compute Scalar

GO

select * from t2 -- (2)

-- Clustered index scan with one times Compute Scalar

GO

create index IX_t2 on t2 (id_bigint)

GO

select * from t2 -- (3)

-- Index Scan with one times Compute Scalar

GO

select * from t2 where id_bigint = 0 -- (4)

-- Index Seek with one times Compute Scalar

GO

drop table t1

GO

drop table t2

GO

SET ANSI_PADDING OFF

1. I don't understand why access to computed column raise scalar computation wto times?

2. I don't understand why access to persisted computed column raise any scalar computation?

3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?

It's a BUG or I incorrect understand value of the "PERSISTED" word?

--

Thanks with avanced.

WBR, Roman S. Golubin

grominc[at]gmail.com

Bug request on SQL Server 2005 Feedback for this icident.

To solve this problem while Microsoft SQL Server Team not fix this bug, use update with triggers instead persisted computed columns.

--

WBR, Roman S. Golubin

73! GL! RA1OGE/3

|||

It works.

Use 'WITH SCHEMABINDING'

Courtesy of Kent Tegels!

It's a computed column processing bug?

Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is

Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now I create two tables and try four select queries:

Code Snippet

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

GO

create table t1 (id int primary key, id_bigint as cast(id as bigint))

GO

create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)

GO

select * from t1 -- (1)

-- Clustered index scan with two times Compute Scalar

GO

select * from t2 -- (2)

-- Clustered index scan with one times Compute Scalar

GO

create index IX_t2 on t2 (id_bigint)

GO

select * from t2 -- (3)

-- Index Scan with one times Compute Scalar

GO

select * from t2 where id_bigint = 0 -- (4)

-- Index Seek with one times Compute Scalar

GO

drop table t1

GO

drop table t2

GO

SET ANSI_PADDING OFF

1. I don't understand why access to computed column raise scalar computation wto times?

2. I don't understand why access to persisted computed column raise any scalar computation?

3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?

It's a BUG or I incorrect understand value of the "PERSISTED" word?

--

Thanks with avanced.

WBR, Roman S. Golubin

grominc[at]gmail.com

Bug request on SQL Server 2005 Feedback for this icident.

To solve this problem while Microsoft SQL Server Team not fix this bug, use update with triggers instead persisted computed columns.

--

WBR, Roman S. Golubin

73! GL! RA1OGE/3

|||

It works.

Use 'WITH SCHEMABINDING'

Courtesy of Kent Tegels!