Stored Proc: Problems with output parameter from ADO and optiomization tips?

Stored Proc: Problems with output parameter from ADO and optiomization tips?

Post by Lance Petle » Fri, 26 Jan 2001 10:59:42



Hey,

I have a stored procedure (which I have included at the bottom of this
message) which returns all records for a given page and page size, which can
optionally be filtered and/or ordered by any criteria.  The stored procedure
is used by a visual basic component, that uses an ado command and gets the
results using an ado recordset.  The server is running SQL7, SQL2000 is not
an option for now.   (There are lots of stored procedures like this, for
every entity, i.e. "prc_Webtrix_NewsItem_sel", "prc_Webtrix_Product_sel",
"prc_Webtrix_Member_sel", etc... only a few have been written similar to
this as a test).

From the visual basic component I NEED to know how many records are
available in total, based on the FilterCondition.  This is used to determine
how many records and pages are available (The component is used by asp
scripts where the pages must be shown similar to how a search engine shows
them).

If I execute this code then all works....

---cut---

....
    Set cmdADOCommand = New ADODB.Command
    Set cmdADOCommand.ActiveConnection = Database.ADODBConnection
    cmdADOCommand.CommandText =
ParentEntity.RuntimeEntity.Attributes("SQL_DefaultProcedureName_Select").Val
ue   'Equates to "prc_Webtrix_Image_sel"
    cmdADOCommand.CommandType = adCmdStoredProc

....

    Set cmdADOParameter =

adParamOutput, 4)
    cmdADOCommand.Parameters.Append cmdADOParameter

    cmdADOCommand.Execute

    Debug.Print "NonPagedRecordCount = " &

...

---cut--

But obviously I want a recordset from executing the ADO command.....

---cut---

....
    Set cmdADOParameter =

adParamOutput, 4)
    cmdADOCommand.Parameters.Append cmdADOParameter

    Set rstResults = cmdADOCommand.Execute

    Debug.Print "NonPagedRecordCount = " &

...

---cut--

When I do this the parameter is not returned, its just blank!!!!!  Is there

recordset???

Next I was wondering if there was any suggestions for optiomizing the
procedure.  I do not need 100% accurate results for the RowCount, which is
why I used WITH(NOLOCK).  But unfortuately cannot query the system
information tables for the RowCount, because I need the RowCount based on a
filter.  Also I am unsure how a temporary table will preform, and if there
are any concurrency issues.  I had to use a temporary table to get the
results back from the query (or it was the only idea I could think of).
Then I was wondering if selecting a page of results by using NOT IN is
efficient.  I cannot use .PageSize, etc, from inside ADO, because of the way
the recordset gets created.

When the system is live, the tables will contain (possibly hundreds of)
thousands of records built up over time.  There majority of the databaes
activity will be selects, all of which are done through stored procedures.
As said the user of the stored procedures will be a visual basic component,
which will be used by ASP scripts, for a number of sites, which combined
could generate alot of hits (in New Zealand standards anyway :P ).  At
present the component does very little caching of results, although for the
next version/stage 2 caching from a shared pool may be investigated.

CODE FOR STORED PROCEDURE:

===========================================
CREATE PROCEDURE prc_Webtrix_Image_sel






AS



  SET NOCOUNT ON

  CREATE TABLE #WebtrixImageRowCount
  ( WebtrixImageRowCount int )









#WebtrixImageRowCount

  DROP TABLE #WebtrixImageRowCount









VARCHAR(30)) + ') '








===========================

Thanks for all your help!


 
 
 

Stored Proc: Problems with output parameter from ADO and optiomization tips?

Post by Dion » Fri, 26 Jan 2001 13:11:22


Hi Lance,

You could try something like the one added at the end of my posting. It will
return the RS in the function and the row count in variable
NonPagedRowCount. I assume your SP code works fine.

The code is based in FMStocks application, by Vertigo Software and you will
need extra code from that application in order this runs as it is. You can
find for download, as well as documentation on how to use it at MSDN
(http://msdn.microsoft.com/library/default.asp?URL=/library/techart/fm...
_starthere.htm).

It is a mix between their RunSPReturnRS and RunSPReturnInteger functions.

Hope this helps.

Regards,
Dioni
--------
Function RunSPReturnLongAndRS(ByVal strSP As String, NonPagedRowCount As
Long, ParamArray params() As Variant) As Variant
    On Error GoTo errorHandler

    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset

    ' Create the ADO objects
    Set rs = CtxCreateObject("ADODB.Recordset")
    Set cmd = CtxCreateObject("ADODB.Command")

    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc

    collectParams cmd, params

    ' Assume the last parameter is outgoing and named @Result
    cmd.Parameters.Append cmd.CreateParameter("@Result", adInteger,
adParamOutput, 4)

    ' Execute without a resulting recordset and pull out the "return value"
parameter
    ' cmd.Execute , , ADODB.adExecuteNoRecords

    ' Execute the query for readonly
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

    'returns the row count in the variable
    NonPagedRowCount = cmd.Parameters("@Result").Value

    ' Disconnect the recordset
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Set rs.ActiveConnection = Nothing

    ' Return the resultant recordset
    Set RunSPReturnLongAndRS = rs

    Exit Function

errorHandler:
    Set cmd = Nothing
    RaiseError g_modName, "RunSPReturnInteger(" & strSP & ", ...)"
End Function

"Lance Petley" <la...@kapiti.co.nz> wrote in message

news:uCjvDKnhAHA.2020@tkmsftngp03...
> Hey,

> I have a stored procedure (which I have included at the bottom of this
> message) which returns all records for a given page and page size, which
can
> optionally be filtered and/or ordered by any criteria.  The stored
procedure
> is used by a visual basic component, that uses an ado command and gets the
> results using an ado recordset.  The server is running SQL7, SQL2000 is
not
> an option for now.   (There are lots of stored procedures like this, for
> every entity, i.e. "prc_Webtrix_NewsItem_sel", "prc_Webtrix_Product_sel",
> "prc_Webtrix_Member_sel", etc... only a few have been written similar to
> this as a test).

> From the visual basic component I NEED to know how many records are
> available in total, based on the FilterCondition.  This is used to
determine
> how many records and pages are available (The component is used by asp
> scripts where the pages must be shown similar to how a search engine shows
> them).

> If I execute this code then all works....

> ---cut---

> ....
>     Set cmdADOCommand = New ADODB.Command
>     Set cmdADOCommand.ActiveConnection = Database.ADODBConnection
>     cmdADOCommand.CommandText =

ParentEntity.RuntimeEntity.Attributes("SQL_DefaultProcedureName_Select").Val

- Show quoted text -

> ue   'Equates to "prc_Webtrix_Image_sel"
>     cmdADOCommand.CommandType = adCmdStoredProc

> ....

>     Set cmdADOParameter =
> cmdADOCommand.CreateParameter("@_NonPagedRowCount", adInteger,
> adParamOutput, 4)
>     cmdADOCommand.Parameters.Append cmdADOParameter

>     cmdADOCommand.Execute

>     Debug.Print "NonPagedRecordCount = " &
> cmdADOCommand.Parameters("@_NonPagedRowCount").Value
> ...

> ---cut--

> But obviously I want a recordset from executing the ADO command.....

> ---cut---

> ....
>     Set cmdADOParameter =
> cmdADOCommand.CreateParameter("@_NonPagedRowCount", adInteger,
> adParamOutput, 4)
>     cmdADOCommand.Parameters.Append cmdADOParameter

>     Set rstResults = cmdADOCommand.Execute

>     Debug.Print "NonPagedRecordCount = " &
> cmdADOCommand.Parameters("@_NonPagedRowCount").Value
> ...

> ---cut--

> When I do this the parameter is not returned, its just blank!!!!!  Is
there
> no way for me to get both the return value for @_NonPagedRowCount and a
> recordset???

> Next I was wondering if there was any suggestions for optiomizing the
> procedure.  I do not need 100% accurate results for the RowCount, which is
> why I used WITH(NOLOCK).  But unfortuately cannot query the system
> information tables for the RowCount, because I need the RowCount based on
a
> filter.  Also I am unsure how a temporary table will preform, and if there
> are any concurrency issues.  I had to use a temporary table to get the
> results back from the query (or it was the only idea I could think of).
> Then I was wondering if selecting a page of results by using NOT IN is
> efficient.  I cannot use .PageSize, etc, from inside ADO, because of the
way
> the recordset gets created.

> When the system is live, the tables will contain (possibly hundreds of)
> thousands of records built up over time.  There majority of the databaes
> activity will be selects, all of which are done through stored procedures.
> As said the user of the stored procedures will be a visual basic
component,
> which will be used by ASP scripts, for a number of sites, which combined
> could generate alot of hits (in New Zealand standards anyway :P ).  At
> present the component does very little caching of results, although for
the
> next version/stage 2 caching from a shared pool may be investigated.

> CODE FOR STORED PROCEDURE:

> ===========================================
> CREATE PROCEDURE prc_Webtrix_Image_sel
>  @SiteID int,
>  @PageSize int = 0,
>  @Page int = 1,
>  @FilterConditions varchar(1500) = ' 1 = 1 ',
>  @FitlerSortCriteriaList varchar(1500) = '  ID ',
>  @_NonPagedRowCount int = 0 OUTPUT
> AS

>   DECLARE @intPageTopExclusive int
>   DECLARE @vchSQLExec varchar(5000)

>   SET NOCOUNT ON

>   CREATE TABLE #WebtrixImageRowCount
>   ( WebtrixImageRowCount int )

>   SELECT @vchSQLExec = ''
>   SELECT @vchSQLExec = @vchSQLExec + 'INSERT #WebtrixImageRowCount '
>   SELECT @vchSQLExec = @vchSQLExec + 'SELECT COUNT(ID) '
>   SELECT @vchSQLExec = @vchSQLExec + 'FROM WebtrixImage WITH(NOLOCK) '
>   SELECT @vchSQLExec = @vchSQLExec + 'WHERE (SiteID = ' + CAST(@SiteID AS
> VARCHAR(30)) + ') AND ( ' + @FilterConditions + ' ) '

>   EXEC(@vchSQLExec)

>   SELECT @_NonPagedRowCount = WebtrixImageRowCount FROM
> #WebtrixImageRowCount

>   DROP TABLE #WebtrixImageRowCount

>   SELECT @intPageTopExclusive = ((@Page - 1) * @PageSize)

>   SELECT @vchSQLExec = ' '
>   SELECT @vchSQLExec = @vchSQLExec + ' SELECT '

>   IF @PageSize > 0 SELECT @vchSQLExec = @vchSQLExec + 'TOP ' +
> CAST(@PageSize AS VARCHAR(30)) + ' '

>   SELECT @vchSQLExec = @vchSQLExec + 'WebtrixImage.* '
>   SELECT @vchSQLExec = @vchSQLExec + 'FROM WebtrixImage WITH(NOLOCK) '
>   SELECT @vchSQLExec = @vchSQLExec + 'WHERE ((SiteID = ' + CAST(@SiteID AS
> VARCHAR(30)) + ') '

>   IF @Page > 1 SELECT @vchSQLExec = @vchSQLExec + 'AND (ID NOT IN (SELECT
> TOP ' + CAST(@intPageTopExclusive AS VARCHAR(30)) + ' ID FROM WebtrixImage
> WITH(NOLOCK) WHERE (SiteID = 1) AND ( ' + @FilterConditions + ' ) ORDER BY
'
> + @FitlerSortCriteriaList + ' ))'

>   SELECT @vchSQLExec = @vchSQLExec + ') AND ( ' + @FilterConditions + ' )
'
>   SELECT @vchSQLExec = @vchSQLExec + 'ORDER BY ' + @FitlerSortCriteriaList

>   EXEC(@vchSQLExec)

> ===========================

> Thanks for all your help!

> Lance Petley   la...@kapiti.co.nz