[AccessD] Upsize?

Hi Jim,

99.5% of the time I agree that sprocs are the way to go.  However when things get really messy EXEC is
a godsend ;-)

I see I've created some confusion <sorry>  ;-)
The 80 variables combo.  I should have written...  Variable combinations of 80 fields from a variety of tables.
Variable combinations as in the user decides what they want to see in the resultset.

I could just return all fields and let the FE sort it out but I choose not to.  The real issues arise with the joins
and the Where conditions.  Approx 50 potential criteria choices for the user.  They can choose all or none
or any combination they like, including single or multiple selects ie I want Customer X or I want Customer X
and y and Z.  They can do betweens or just > I even let then do stuff like between 5 and 10 and between 15 and 20.


Hi Michael:

Quicker: When just doing a simple query like an update or delete there is little difference. When in single user mode there is little to be gained but when the SQL DB is being hit with multiple requests using a complex set of queries the gain become obvious. The SP is compiled and cached before use and every subsequent access is lightning fast.
>>>Executed strings are cached as well.

Flexible: A SP can be a few simple lines or a total mini-application in itself, with a hundred lines of code calling a number of built-in and created functions and can even call external executables. It can create temporary tables (cursors) and views and use them as components to the calculations. Complex functions like UNIONS, GROUP and ROLL-UP can be added where required to the mix with little loss in performance.
>>>Can do all that dynamic as well.  Not that I would unless I had a good reason of course  ;-)))

Easier: If a set of SPs are designed correctly with appropriate support functions it should be easier to extend functionality with little impact on your Access FE.
>>>?  Not sure what you mean?  

80 Variables from one combo box could be handled this:

<code sample>
...
strComboString = ""

If MyComboBox.ListIndex > -1 Then
 For i = 0 To MyComboBox.ListCount - 1
  If MyComboBox.Selected(i) = True Then
    If Len(Trim(strComboString)) > 0 Then
      strComboString = strComboString & ", " & Str(MyComboBox.Column(MyComboBox.BoundColumn - 1, i))
    Else
      strComboString = strComboString &
Str(MyComboBox.Column(MyComboBox.BoundColumn - 1, i))
    End If
  End If
 Next i
End If

ProcessComboBoxStrings strComboString
...

Function ProcessComboBoxStrings(strComboString As String)

...

Set objCmd = New ADODB.Command

With objCmd
 .ActiveConnection = gstrConnection 'My server connection string  .CommandText = "MyCombohandlerSP" 'The appropriate Stored Procedure name  .CommandType = adCmdStoredProc 'Type of process ...SP  .Parameters.Append .CreateParameter("@chvComboString", adVarChar, dParamInput, len(strComboString), strComboString) 'Parameter string

 .Execute
End With 

....

End Function
</code sample>

Though the code sample is incomplete and mostly from memory you get the idea. It first routine loops through a combo box list accumulating all the selected items into a string. Then it passes the string to a SP and it can be handled from there.

HTH
Jim        

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael Maddison
Sent: December 20, 2005 3:21 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Upsize?

Hi Jim,

I think Jürgen and my response is no.
Or at least how?

BTW I disagree with 'They tent(d) to be quicker, have greater flexibility, easier to work with'
they may be safer but...
quicker - not necessarily, especially if the execution plan needs to change from execution to execution.
Flexible - whats more flexible then building a string and running that?
Easier - 80 variable cols, 50 potential where statements...  gonna be some sproc...  what is the char limit for a sproc?

cheers

Michael M


Hi Jürgen:

Can you not use Stored Procedures and just pass parameters? They tent to be quicker, have greater flexibility, easier to work with and safer.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz
Sent: December 19, 2005 9:42 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Upsize?

Michael:

With variable joins, do you point somthing like a list source of search 'hits' to different queries, one query for each join, or how do you handle variable combinations of joins?  Lets say there is 1 table that may be joined to 0 to 5 other tables in various combinations, being 32 possible querydefs.  I've always constructed the SQL in code and was very satisfied with the performance.  Add another table and you're up to 64 querydefs.  
That's ugly.



Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Michael Maddison" <michael at ddisolutions.com.au>
>
>  Hi J|rgen,
>
>If you go with variable parameters check out the 'With Recompile' option.
>It forces a new execution plan each time the procedure is run and 
>overcomes SQL's 'parameter sniffing' problem.
>
>cheers
>
>Michael Maddison
>
>DDI Solutions Pty Ltd
>michael at ddisolutions.com.au
>Bus: 0260400620
>Mob: 0412620497
>www.ddisolutions.com.au



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com