Re: Efficient scanning of SQL Server 2005 tables?
- From: amos.shapira@xxxxxxxxx (Amos Shapira)
- Date: Wed, 18 Jul 2007 13:55:19 +1000
On 18/07/07, Ron Savage <ron@xxxxxxxxxxxxx> wrote:
Amos Shapira wrote:
Hi Amos
> Our local SQL Server/Windows/C++ guru told me that the standard way we
scan
> tables causes the server to practically copy the entire result set into
a
> temporary table on the server before feeding it to the client.
I assume MS are so cynical they do this to deliberately slow down the
process.
Hanlon's Razor: Don't attribute to malice what can be adequately explained
by incompetence.
Bruce's Razor: Don't attribute to incompetence what can be adequately
explained by Microsoft funding.
- Craig Bruce (
http://www.brainyquote.com/quotes/quotes/c/craigbruce189141.html)
He found a way to avoid this in C++ by some clever direct ODBC hacks on
> Windows which make the server practically stream the results to the
client
> without creating a temporary table.
>
> Is there a way to achieve this with Perl on Linux too?
Well, if you can publish his code here it should be convertible in Perl
(perhaps with a C++ component). But if the code remain secret, I can't
see how it can be replicated without being independently created.
Here is what I got from him:
1. A pointer to the MSDN article which (tries to) explain this. Maybe it's
more meaningful to people with more experience with SQL Server than me (he
specifically quotes the last paragraph beginning with "The SQL Server ODBC
driver offers an optimization"):
http://msdn2.microsoft.com/en-us/library/aa197691(sql.80).aspx
2. Pseudo code which supposedly demonstrate what this article is talking
about:
Set CursorType = forward_only, read_only
Set RowSize = 1
ExecDirect( query )
SQLFetch
Set RowSize = 1000
BindColums (To buffer big enough to hold 1000 rows)
While ( moreResults )
Begin
SQLFetchScroll( SQL_FETCH_NEXT )
end
Thanks,
--Amos
- Follow-Ups:
- Re: Efficient scanning of SQL Server 2005 tables?
- From: Martin Evans
- Re: Efficient scanning of SQL Server 2005 tables?
- References:
- Efficient scanning of SQL Server 2005 tables?
- From: Amos Shapira
- Re: Efficient scanning of SQL Server 2005 tables?
- From: Ron Savage
- Efficient scanning of SQL Server 2005 tables?
- Prev by Date: Re: Efficient scanning of SQL Server 2005 tables?
- Next by Date: Re: DBD::ODBC multiple active statements (was Trouble Installing DBD::ODBC with postgresql)
- Previous by thread: Re: Efficient scanning of SQL Server 2005 tables?
- Next by thread: Re: Efficient scanning of SQL Server 2005 tables?
- Index(es):
Relevant Pages
|
|
