对数据构型的小测试

最新推荐文章于 2022-11-18 14:45:22 发布

原创最新推荐文章于 2022-11-18 14:45:22 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #security #go #output #null #测试

ASP 专栏收录该内容

5 篇文章

订阅专栏

这篇博客探讨了数据构型在SQL查询中的使用，通过两种状态（使用和不使用数据构型）进行对比测试。内容包括执行SHAPE和APPEND语句，以及比较数据构型与传统查询的效率和实用性。作者指出，虽然数据构型在特定场景下可能有用，但在大多数情况下，他们倾向于使用联合查询和其他方法来达到相同效果。

状态一使用数据构型


<%
Set Connection = Server.CreateObject("ADODB.Connection")
ConnectionString = "Provider=MSDataShape;DATA PROVIDER=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=123;Initial Catalog=Northwind;Data Source=(local)"
Connection.Open(ConnectionString)

SQL = _
"SHAPE {SELECT CustomerID FROM Customers} "&_
"APPEND ({SELECT OrderID,CustomerID FROM Orders} AS chapOrders "&_
"RELATE customerID TO customerID)"

Set Rs = Connection.Execute(SQL)

Do While not Rs.Eof
Response.Write(Rs(0) &" - "& Rs.Fields.Count)
Response.Write("<br>")
Set RsChild = Rs("chapOrders").Value
Do While Not RsChild.EOF
Response.Write( RsChild(0) &" "& RsChild(1) &" - "& RsChild.Fields.Count)
Response.Write("<br>")
RsChild.MoveNext
Loop
RsChild.Close
Rs.MoveNext
Loop

Set RsChild = Nothing
Rs.Close
Set Rs = Nothing
Connection.Close
Set Connection = Nothing
%>

发送如下 SQL
SET NO_BROWSETABLE ON
go
declare @P1 int
set @P1=1
exec sp_prepare @P1 output, NULL, N'SELECT OrderID,CustomerID FROM Orders', 1
select @P1
go
SET NO_BROWSETABLE OFF
go
SELECT CustomerID FROM Customers;SELECT OrderID,CustomerID FROM Orders
go
SET NO_BROWSETABLE ON
go
exec sp_unprepare 1
go

状态二不使用数据构型


<%
Set Connection = Server.CreateObject("ADODB.Connection")
ConnectionString = "PROVIDER=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=123;Initial Catalog=Northwind;Data Source=(local)"
Connection.Open(ConnectionString)

SQL = "SELECT Customers.CustomerID, Orders.OrderID, Orders.CustomerID FROM [Customers],[Orders] WHERE Customers.CustomerID=Orders.CustomerID"

Set Rs = Connection.Execute(SQL)

Do While not Rs.Eof
Response.Write(Rs(0) &" - "& Rs.Fields.Count)
Response.Write("<br>")
Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing
Connection.Close
Set Connection = Nothing
%>

发送如下 SQL
exec sp_reset_connection
go
SELECT Customers.CustomerID, Orders.OrderID, Orders.CustomerID FROM [Customers],[Orders] WHERE Customers.CustomerID=Orders.CustomerID
go

效率就不用说了总体来说感觉使用性不是很大分类和论坛结构上可能用到上
当效率是很大的问题我宁愿使用联合查询配合其他方法实现同样效果