extended stored proc programming

[Bonj]

Hi
I'm looking to write an extended stored procedure, the job of which will
basically to read data from one table, process it using a COM object, and
write (insert) rows out to another table.
I don't really know where to start with finding the functions necessary to
read from an existing table. Can anyone be so kind as to point me in the
right direction?

Thanks very much

[Adam Machanic]

I'm curious as to why you want to retrieve and insert the rows within the
extended proc rather than retrieve the rows, pass them into the extended
proc, then pass the data back for re-insertion? IMO, that would be a more
streamlined approach than having the proc do all of that work itself...
"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:72******** *************** ***********@mic rosoft.com...

Hi
I'm looking to write an extended stored procedure, the job of which will
basically to read data from one table, process it using a COM object, and
write (insert) rows out to another table.
I don't really know where to start with finding the functions necessary to
read from an existing table. Can anyone be so kind as to point me in the
right direction?

Thanks very much

[Toby Herring]

Rows inserted by an XP can be inserted outside the transaction context, so
that if the transaction is rolled back, the XP-inserted rows remain. Good
for auditing/logging/troubleshooting .

--
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life?
http://secondlife.com/ss/?u=03e0e5b3...e80ee40119a65e
"Adam Machanic" <amachanic@hotm ail._removetoem ail_.com> wrote in message
news:u%******** ********@TK2MSF TNGP10.phx.gbl. ..

I'm curious as to why you want to retrieve and insert the rows within the
extended proc rather than retrieve the rows, pass them into the extended
proc, then pass the data back for re-insertion? IMO, that would be a more
streamlined approach than having the proc do all of that work itself...
"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:72******** *************** ***********@mic rosoft.com...

Hi
I'm looking to write an extended stored procedure, the job of which will
basically to read data from one table, process it using a COM object, and
write (insert) rows out to another table.
I don't really know where to start with finding the functions necessary
to
read from an existing table. Can anyone be so kind as to point me in the
right direction?

Thanks very much

[Bonj]

I don't know, I'm curious as to learn what the best way would be.
For me, 'best' == fastest.
I'm not bothered in the slightest about rollbacks or auditing.

However, I am still open to suggestions about what the best method might be.
My intial perception is that reading the data in the xp will be faster than
reading it via SQL and passing it into the xp for processing and then
inserting when back in SQL, as it's not having to constantly cross (marshal?)
between the SQL script and the xp. I intially tried this approach, but
instead of an xp, I wrote a load of SQL code that instantiates the COM object
that does the processing via sp_OAcreate, sp_OAmethod, etc., but it was
horrendously slow. Any ideas why this might be, and why an xp might be faster?

I just get the impression that if it's just the same block of C++ code that
does the entire lot of the work, then it will be faster.

If you still think your approach will be faster, please tell me how to 'pass
a row' to the xp - but please someone tell me how to read data using the xp
so I can test the methods against each other for speed.

"Adam Machanic" wrote:

I'm curious as to why you want to retrieve and insert the rows within the
extended proc rather than retrieve the rows, pass them into the extended
proc, then pass the data back for re-insertion? IMO, that would be a more
streamlined approach than having the proc do all of that work itself...
"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:72******** *************** ***********@mic rosoft.com...

Hi
I'm looking to write an extended stored procedure, the job of which will
basically to read data from one table, process it using a COM object, and
write (insert) rows out to another table.
I don't really know where to start with finding the functions necessary to
read from an existing table. Can anyone be so kind as to point me in the
right direction?

Thanks very much

[Adam Machanic]

"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:27******** *************** ***********@mic rosoft.com...

However, I am still open to suggestions about what the best method might be. My intial perception is that reading the data in the xp will be faster than reading it via SQL and passing it into the xp for processing and then
I guess, like anything else, it depends. Is it faster to cursor through
the data in C++ or T-SQL? How expensive is each initialization of the XP?
Will it be more expensive for the XP to have to make new database
connections to read/write, rather than just using the connection that
spawned the XP?
instead of an xp, I wrote a load of SQL code that instantiates the COM object that does the processing via sp_OAcreate, sp_OAmethod, etc., but it was
horrendously slow. Any ideas why this might be, and why an xp might be faster?

There is, IMO, no reason to believe that an XP would be that much
faster. Did you profile your COM objects to figure out where the
performance drain was?
If you still think your approach will be faster, please tell me how to 'pass a row' to the xp - but please someone tell me how to read data using the xp so I can test the methods against each other for speed.

I apologize if I implied that you can pass a row as-is -- you can't.
You can only pass scalar datatypes. Reading data using an XP is the same as
reading it from any other code -- set up a connection to the DB and query
the data.

[Bonj]

I guess, like anything else, it depends. Is it faster to cursor through
the data in C++ or T-SQL?
I'm guessing C++. That's why I want to check it out with the whole lot
running in C++.
How expensive is each initialization of the XP?
Don't know. Probably average relatively... but the COM object has to be
called ~78m times, at the most recent count of the output rows. It currently
takes about 30 hours to do all that lot with a VB program. But that includes
all the time it takes it to send the data to SQL server. So I'm guessing that
to keep one connection open with the code that's calling the COM object all
the way through would be the best.

I just can't accept that initializing a stored procedure, or initalizing and
opening a connection, or pretty much initializing *anything* 78 million times
can be a good thing. That's why I want to do it all within the C++ code of an
extended proc.

Will it be more expensive for the XP to have to make new database
connections to read/write, rather than just using the connection that
spawned the XP?
That's what I'm thinking, yes.

instead of an xp, I wrote a load of SQL code that instantiates the COM object

that does the processing via sp_OAcreate, sp_OAmethod, etc., but it was
horrendously slow. Any ideas why this might be, and why an xp might be

faster?

There is, IMO, no reason to believe that an XP would be that much
faster. Did you profile your COM objects to figure out where the
performance drain was?

The COM object isn't under our control. But it is at the very very very
centre of all the loops. It is the absolute bottleneck. And before you ask,
no we can't write it in SQL because the company that wrote it have protected
it with a dongle, so I would think they'll be unlikely to do anything that
might be tantamount to giving the algorithm away.
I apologize if I implied that you can pass a row as-is -- you can't.
You can only pass scalar datatypes. Reading data using an XP is the same as
reading it from any other code -- set up a connection to the DB and query
the data.
I'm afraid I insist. Can you please tell me how to read a table from within
an XP, even if just to satisfy my own curiousity regarding testing how fast
it actually will be?

[Toby Herring]

Depends on what version of C++.

If it's MS C++ 6, then you can simply use the ADO libraries/objects. If
it's C++.Net, then you can either use ADO with unmanaged C++, or you can
write mixed managed/unmanaged code, and use ADO.Net libraries for data
access.

If you're using another version of C++, then you'll either have to use the
ADO COM libraries or use whatever data access is built into the version of
C++ you're using. (You can use the SQL API, but I wouldn't recommend that
approach. It can be faster, but it's a royal PITA to write & troubleshoot,
and it's nearly impossible to find decent documentation on its
implementation. )

If you were to write a front-end application that needed to access the
database, and wrote it in C++, how would you access the database? It's the
same method, no matter whether it's an executable or a dll or an xp.

--
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life?
http://secondlife.com/ss/?u=03e0e5b3...e80ee40119a65e
"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:76******** *************** ***********@mic rosoft.com...

I'm afraid I insist. Can you please tell me how to read a table from
within
an XP, even if just to satisfy my own curiousity regarding testing how
fast
it actually will be?

[Adam Machanic]

"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:76******** *************** ***********@mic rosoft.com...

Don't know. Probably average relatively... but the COM object has to be
called ~78m times, at the most recent count of the output rows. It currently
centre of all the loops. It is the absolute bottleneck. And before you ask, no we can't write it in SQL because the company that wrote it have protected it with a dongle, so I would think they'll be unlikely to do anything that
might be tantamount to giving the algorithm away.
Okay, I'm going to suggest that if you're going to be looping 78 million
times, you don't use an XP at all for this! It will be much more efficient
if you pull the data out to a flat file, manipulate it however you need to,
then BULK INSERT or BCP the data back in. The time saved by the BULK INSERT
alone as compared with a row-by-row reinsertion will be gigantic...
I'm afraid I insist. Can you please tell me how to read a table from within an XP, even if just to satisfy my own curiousity regarding testing how fast it actually will be?

As I said, open a connection to the database and query it. Just as you
would from any client app. I don't know what connection library you're
using in C++ -- ADO, MFC's ODBC connection library, ATL's OLE DB Consumer
Templates library, etc -- use that however you normally would. There is no
"loopback" feature in the XP architecture, if that's what you're looking
for.

[Bonj]

hey now that's a good idea.
I might have to learn how to do that.

Can I just add a managed class and as long as I do the #import <mscorlib>
thing and put, like "__gc class...{}" rather than just "class...{} " then
it'll be able to use the managed ADO.NET?

Thanks for that!
"Toby Herring" wrote:

Depends on what version of C++.

If it's MS C++ 6, then you can simply use the ADO libraries/objects. If
it's C++.Net, then you can either use ADO with unmanaged C++, or you can
write mixed managed/unmanaged code, and use ADO.Net libraries for data
access.

If you're using another version of C++, then you'll either have to use the
ADO COM libraries or use whatever data access is built into the version of
C++ you're using. (You can use the SQL API, but I wouldn't recommend that
approach. It can be faster, but it's a royal PITA to write & troubleshoot,
and it's nearly impossible to find decent documentation on its
implementation. )

If you were to write a front-end application that needed to access the
database, and wrote it in C++, how would you access the database? It's the
same method, no matter whether it's an executable or a dll or an xp.

--
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life?
http://secondlife.com/ss/?u=03e0e5b3...e80ee40119a65e
"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:76******** *************** ***********@mic rosoft.com...

I'm afraid I insist. Can you please tell me how to read a table from
within
an XP, even if just to satisfy my own curiousity regarding testing how
fast
it actually will be?

[Bonj]

> As I said, open a connection to the database and query it. Just as you

would from any client app. I don't know what connection library you're
using in C++ -- ADO, MFC's ODBC connection library, ATL's OLE DB Consumer
Templates library, etc -- use that however you normally would. There is no
"loopback" feature in the XP architecture, if that's what you're looking
for.
Yes unfortunately that is what I was hoping for. What about "DB-Library for
C" - is that fast?

Also any suggestions about how to implement a managed class inside this
would be helpful!