Re: returning multiple recordsets from VB to ASP

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: RufusGoofus (RufusGoofus_at_discussions.microsoft.com)
Date: 01/14/05 

Date: Thu, 13 Jan 2005 18:59:02 -0800

I'll consider this. But, if my VB dll returns VB arrays to the ASP, then
I can avoid constructing recordsets in my dll, right? So, I really don't
see why
I should avoid this approach. Perhaps I'm being stubborn, but I really prefer
doing the complex manipulation in VB. Yes, I've heard that Yukon will
allow stored procedures to be written in .NET languages. That's great.
I hope it will come out fairly soon. Again, thanks for the advice.

"William (Bill) Vaughn" wrote:

> Ah, now we're getting somewhere. I expect that with proper tuning your SPs could return the performance you expect. There are several factors that cause (unnecessary) recompiles. For example, not enough RAM to hold the procedures and the data in cache or the stored procedure is using temporary tables (as you said). You might try breaking up the complex SPs into smaller logical units. This way the server can better manage (and not have to recompile) the SPs. Building Recordsets is very expensive and delegating the processing to the client is also expensive. In Yukon you'll be able to execute far more sophisticated SPs (written in VB) but for now, I would not take the course you're traveling.
>
> hth
>
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "RufusGoofus" <RufusGoofus@discussions.microsoft.com> wrote in message news:A44F6750-8C47-49E8-A399-020E1733E000@microsoft.com...
> >I think we have good reasons for wanting to move complex logic out of the
> > stored procedure and into an intermediate VB DLL: (1) complex manipulation
> > of data is
> > more awkward to do in SQL than in VB and (2) the stored procedure is getting
> > constantly recompiled by SQL Server and we think that this problem is due to
> > the stored procedures use of numerous temporary tables and a big loop that
> > iterations through some involved logic. This is having a negative performance
> > impact. However, I do agree that there really is no need to return recordsets
> > to the ASP page. The ASP page anways puts the data into arrays, so I may
> > as well just return arrays. The one sure way of returning multiple arrays to
> > my ASP is to use the approach suggested by Christoph which is to create a
> > persistent object in my DLL and then have the ASP first invoke a method to
> > indirectly invoke the simplified stored procedure and then invoke a different
> > method mutliple times to retrieve each array. I tried passying dynamic
> > arrays by reference from my ASP to the VB DLL, but was getting compile-time
> > "type-mismatch" errors, so I abandoned that approach.
> >
> > Thanks for all the suggestions. It's been a great help.
> >
> >
> > "Stephen Howe" wrote:
> >
> >> > But you may want to rethink your design.
> >> > I would try and make the SP do the work and return the 4 rowsets as part
> >> > of
> >> > 1 recordset with your intermediate DLL doing as minimum adjustment as
> >> > possible.
> >>
> >> Just as a further footnote:
> >>
> >> If you say, "I do need to massage the results returned from the 4 rowsets,
> >> that is why I am constructing a Recordset in the intermediate DLL and
> >> returning that to the ASP".
> >>
> >> I would say: Don't bother. Recordsets have a lot of overhead. Instead I
> >> would transfer the 4 rowsets to VB Arrays of your own type, populate those
> >> and return those to the ASP. Returning arrays of user-defined types is
> >> simpler than creating Recordsets on the fly (at least that is what my
> >> instincts tell me - there may be some reason why this approach will not work
> >> but it is what I would attempt to do if I had lots of post processing to the
> >> rowsets to do)
> >>
> >> Stephen Howe
> >>
> >>
> >>
> >>

Relevant Pages

  • Re: returning multiple recordsets from VB to ASP
    ... I do agree that there really is no need to return recordsets> to the ASP page. ... The ASP page anways puts the data into arrays, so I may> as well just return arrays. ... The one sure way of returning multiple arrays to> my ASP is to use the approach suggested by Christoph which is to create a> persistent object in my DLL and then have the ASP first invoke a method to> indirectly invoke the simplified stored procedure and then invoke a different> method mutliple times to retrieve each array. ...
    (microsoft.public.data.ado)
  • Re: returning multiple recordsets from VB to ASP
    ... stored procedure and into an intermediate VB DLL: ... more awkward to do in SQL than in VB and the stored procedure is getting ... The ASP page anways puts the data into arrays, ...
    (microsoft.public.data.ado)
  • Dynamic Arrays in ASP.NET?
    ... I am converting an ASP page to asp.net. ... I've spent some time reading on the net about the differences in arrays ... This is what I have in my .NET app: ... Dim woPrefix As String ...
    (microsoft.public.dotnet.general)
  • Re: Creating a new window, and passing it variables
    ... Tell the JavaScript function to open that ASP page. ... I have a couple of arrays containing data I ... If your question is worth asking, it's worth posting. ...
    (microsoft.public.inetserver.asp.general)
  • Re: can i load my own dlls into services.exe, if how
    ... ISAPI extensions and ISAPI filters can be useful. ... > DLL via JScript 'new ActiveXObject' or the VBscript equivalent in your ASP ...
    (microsoft.public.windowsce.app.development)