Re: Data File
From: Steve Kass (skass_at_drew.edu)
Date: 08/30/04
- Next message: Ace: "Re: Find a column with x value within a row"
- Previous message: Justin M. Keyes: "XML mail -> SQL server?"
- In reply to: Anith Sen: "Re: Data File"
- Next in thread: Uri Dimant: "Re: Data File"
- Reply: Uri Dimant: "Re: Data File"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 30 Aug 2004 19:00:30 -0400
Anith,
There are a couple of differences worth noting. As far as better
plans, the Jet provider will give an accurate rowcount estimate for
SELECT * FROM f(), which may mean a better query plan will be chosen for
queries that join the remote table with other tables. Your function
will always be estimated to return 10,000 rows, but mine will give a
fairly accurate estimate. (You'll see below, that better plan or no
better plan, your solution gets the data quicker in the test I ran!)
Here's a repro - C:\test.txt is this 10-row file:
BLFKI
BNBTR
BNTON
BROUT
BERGS
BLBUS
BLONP
BOLID
BONBP
BOTTM
and here's the repro:
CREATE FUNCTION f_Sen()
RETURNS TABLE AS
RETURN
SELECT "output" as CustomerID
FROM OPENQUERY ( DESKTOP , 'SET FMTONLY OFF;
EXEC master..xp_cmdshell ''TYPE C:\Test.txt''')
go
create function f_Kass(
) returns table as return
select F1 as CustomerID from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'text;Database=C:\;HDR=NO',Test#txt)
go
set statistics io on
set statistics time on
go
select O.OrderID
from f_Sen() F join Northwind..Orders O
on O.CustomerID = F.CustomerID
go
select O.OrderID
from f_Kass() F join Northwind..Orders O
on O.CustomerID = F.CustomerID
go
set statistics io off
set statistics time off
go
drop function f_Sen, f_Kass
If you look at the query plans, you'll see they're different, and the
estimated time using your function is 99.42%. As it turns out, the
query with your function runs many times faster. So it looks to me like
there is a lot of overhead involved with the Jet driver (in part to get
the good rowcount, probably), but in some cases it may be worth it to
ensure a better query plan.
Another difference shows up when I try this:
select F1.CustomerID
from f_Sen() F1 join f_Sen() F2
on F1.CustomerID = F2.CustomerID
Here's what I get:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'SQLOLEDB'. The
provider could not support a required property. The provider indicates
that conflicts occurred with other properties or requirements.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
[OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work
was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e21: SET FMTONLY OFF;
EXEC master..xp_cmdshell 'TYPE
C:\Test.txt'[PROPID=DBPROP_SERVERCURSOR VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING]].
The self-join works with f_Kass(), though.
The picture is certainly not as straightforward as I thought, but I
think there are differences worth knowing about.
SK
Anith Sen wrote:
>Thanks Steve. I think it would be a better alternative, since one can use
>SELECT * FROM txt directly. BTW, I fail to see how it facilitate better
>optimization, since in both cases it is a remote pass through. Thoughts ?
>
>
>
- Next message: Ace: "Re: Find a column with x value within a row"
- Previous message: Justin M. Keyes: "XML mail -> SQL server?"
- In reply to: Anith Sen: "Re: Data File"
- Next in thread: Uri Dimant: "Re: Data File"
- Reply: Uri Dimant: "Re: Data File"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
