Efficient scanning of SQL Server 2005 tables?

------=_Part_87776_31504808.1184716212953
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello,

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.

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?

Thanks,

--Amos

------=_Part_87776_31504808.1184716212953--

Re: Efficient scanning of SQL Server 2005 tables?

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.

> 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.

--
Ron Savage
ron@savage.net.au
http://savage.net.au/

Re: Efficient scanning of SQL Server 2005 tables?

------=_Part_89809_9856227.1184730919734
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 18/07/07, Ron Savage 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).as px

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

------=_Part_89809_9856227.1184730919734--

Re: Efficient scanning of SQL Server 2005 tables?

Amos Shapira wrote:
> On 18/07/07, Ron Savage 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).as px

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_bridg e/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