Amos Shapira wrote:
On 18/07/07, Ron Savage <[EMAIL PROTECTED]> 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
Which initially basically says fetch multiple rows using row-wise or
column-wise binding which anyone who has worked with the ODBC API knows
is often a lot faster (there are examples of times in the following url
(although they are specific to our bridge and sql server)
http://www.easysoft.com/products/data_access/odbc_odbc_bridge/performance_white_paper.html#3_1_1
However the text
"The SQL Server ODBC driver offers an optimization using rowsets to
quickly retrieve an entire result set. To use this optimization, set the
cursor attributes to their defaults (forward-only, read-only, rowset
size = 1) at the time SQLExecDirect or SQLExecute is called"
seems a little confusing. Set things up to their defaults!
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
This is certainly quicker (see reference above) but more difficult to do
in DBD::ODBC than it first appears. A few years ago I spent a day
starting to implement it and got diverted when we could use another DBD
instead. I'm certainly not rushing in to this right now but don't let
that stop anyone else.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
