[AccessD] Upsize?

Jürgen Welz jwelz at hotmail.com
Mon Dec 19 11:04:46 CST 2005


Our office is likely going to hit 35 concurrent users over the next year 
from about 25 now.  I have been told that I can move my data to a SQL Server 
BE in anticipation of the increased demands on the system, something that 
was absolutely prohibited previously.

I've generated a great deal of SQL in code for things like search forms, 
where I have over a dozen parameters driven by combos and text boxes, with 
Ands, Ors and Starts With kinds of options.  I also allow users to sort 
lists and set columns to show things like Fiirst Name or Last Name first, or 
Project Name or Job Number first.  A seach may be on a Person or Company, 
but it may require a link to a PO table, a Project Table, a Location table 
or there may be no join at all.

I believe Arthur to say that saved queries can be optimized or compiled 
resulting in optimization, but I must say that I have never found this to be 
true with multiple varying parameters because the execution plan varies 
widely depending upon the parameters passed.  The parameters may require any 
where from a single table query to joins on 8 or 9 different tables.

My experience with these has been that attempting this with parameterized 
queries is not feasible.  I also find that a compiled query may run slower 
than a dynamically constructed query simply because the saved plan may not 
be as good as one newly generated on the fly when the parameters are 
radically different from the time of last execution.

I also frequently change the SQL of saved query defs to filter Sub Reports 
and I have not needed to find a different approach for this.

My boss is prepared to allow 1 month for conversion and I have to tell him 
whether this is feasible.  I still run DAO here so I think I may have quite 
a job ahead.


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





>From: "William Hindman" <wdhindman at bellsouth.net>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Upsize?
>Date: Mon, 19 Dec 2005 09:14:49 -0500
>MIME-Version: 1.0
>Received: from databaseadvisors.com ([209.135.140.44]) by 
>bay0-mc5-f15.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 19 
>Dec 2005 06:15:13 -0800
>Received: from databaseadvisors.com (databaseadvisors.com 
>[209.135.140.44])by databaseadvisors.com (8.11.6/8.11.6) with ESMTP id 
>jBJEEwJ03330;Mon, 19 Dec 2005 08:14:58 -0600
>Received: from imf16aec.mail.bellsouth.net 
>(imf16aec.mail.bellsouth.net[205.152.59.64])by databaseadvisors.com 
>(8.11.6/8.11.6) with ESMTP id jBJEEqJ03280for 
><accessd at databaseadvisors.com>; Mon, 19 Dec 2005 08:14:52 -0600
>Received: from ibm58aec.bellsouth.net ([65.11.203.146])by 
>imf16aec.mail.bellsouth.net with ESMTP 
>id<20051219141449.JVOO2154.imf16aec.mail.bellsouth.net at ibm58aec.bellsouth.net>for 
><accessd at databaseadvisors.com>; Mon, 19 Dec 2005 09:14:49 -0500
>Received: from 50NM721 ([65.11.203.146]) by ibm58aec.bellsouth.net with 
>SMTPid <20051219141449.GFEP27766.ibm58aec.bellsouth.net at 50NM721>for 
><accessd at databaseadvisors.com>; Mon, 19 Dec 2005 09:14:49 -0500
>X-Message-Info: tUj+E00hCsMkduNmX7LSN5TFQBDS2DfG02kYPymEkfc=
>References: <200512190037.jBJ0bWJ03454 at databaseadvisors.com>
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-BeenThere: accessd at databaseadvisors.com
>X-Mailman-Version: 2.1.6
>Precedence: list
>List-Id: Access Developers discussion and problem 
>solving<accessd.databaseadvisors.com>
>List-Unsubscribe: 
><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=unsubscribe>
>List-Archive: <http://databaseadvisors.com/pipermail/accessd>
>List-Post: <mailto:accessd at databaseadvisors.com>
>List-Help: <mailto:accessd-request at databaseadvisors.com?subject=help>
>List-Subscribe: 
><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=subscribe>
>Errors-To: accessd-bounces at databaseadvisors.com
>Return-Path: accessd-bounces at databaseadvisors.com
>X-OriginalArrivalTime: 19 Dec 2005 14:15:13.0880 (UTC) 
>FILETIME=[A0FF3580:01C604A6]
>
>...ok ...I'm doubling the Tylenol ...you're making my neck ache with all
>these twists and turns :(
>
>William
>----- Original Message -----
>From: "John Colby" <jwcolby at ColbyConsulting.com>
>To: "'Access Developers discussion and problem solving'"
><accessd at databaseadvisors.com>
>Sent: Sunday, December 18, 2005 7:37 PM
>Subject: Re: [AccessD] Upsize?
>
>
> > What I don't understand Arthur is why you insist on using dedicated 
>static
> > functions?  You write a static function CurrentCountry(), 
>CurrentRegion(),
> > CurrentCity(), CurrentThis(), CurrentThat(), CurrentSomethingElse().
> > Every
> > time you need another CurrentSomething() you have to stop to construct
> > such
> > a new function, you have potentially dozens (hundreds?  Thousands?
> > Millions?) of such functions.
> >
> > I have ONE static function which I happen to call Fltr() but which you
> > could
> > call Current() if that made your day.  Observe that the function has a
> > static COLLECTION which can hold one or up to 32K values.  So my one
> > function has replaced ALL of your CurrentXXX.  The only place where this
> > might not be appropriate is if you need to run it a million times since 
>my
> > function will be about 3 times slower than yours, however where clauses
> > evaluate the function one time and that is that.  Additionally I don't
> > have
> > to stop and write a new function (wasting my client's time).  ;-)
> >
> > '
> > 'Fltr takes two parameters, the filter name and the filter value.
> > '
> > 'The first syntax can be used to set the filter value:
> > '
> > 'fltr "MyFltr1", MyFltrValue
> > '
> > 'The filter lstrName is used as the key into the collection, i.e. when
> > lvarValue
> > 'is stored, it is stored with a key of lstrName.
> > '
> > 'The second syntax can be used to retrieve the value of the filter:
> > '
> > 'fltr("MyFltr1")
> > '
> > 'The fact that the second parameter is Optional allows us to check 
>whether
> > a
> > value
> > 'has been passed in.  If no value is passed in, then the assumption is
> > that
> > the filter
> > 'is expecting to return a value.
> > '
> > 'Because the filter uses a collection internally to save the values, 
>this
> > single
> > 'function can store up to 32K different filter values.
> > '
> > 'Because lvarValue is a variant, the value stored can be pretty much
> > anything.
> > 'In fact it is necessary to use ctl.VALUE if you want to store an
> > unchanging
> > value
> > 'from a control, since passing in a pointer to a control will then 
>return
> > the value
> > 'of the control, which may change over time.
> > '
> > Public Function Fltr(lstrName As String, Optional lvarValue As Variant) 
>As
> > Variant
> > On Error GoTo Err_Fltr
> > Static mcolFilter As Collection
> > Static blnFltrInitialized As Boolean
> >
> >    If Not blnFltrInitialized Then
> >        Set mcolFilter = New Collection
> >        blnFltrInitialized = True
> >    End If
> >    If IsMissing(lvarValue) Then
> >        On Error Resume Next
> >        Fltr = mcolFilter(lstrName)
> >        If Err <> 0 Then
> >            Fltr = Null
> >        End If
> >    Else
> >        On Error Resume Next
> >        mcolFilter.Remove lstrName
> >        mcolFilter.Add lvarValue, lstrName
> >        Fltr = lvarValue
> >    End If
> > Exit_Fltr:
> > Exit Function
> > Err_Fltr:
> >        fwErr , , "Error in Function basFltrFunctions.Fltr"
> >        Resume Exit_Fltr
> >    Resume 0    '.FOR TROUBLESHOOTING
> > End Function
> >
> >
> > John W. Colby
> > www.ColbyConsulting.com
> >
> > Contribute your unused CPU cycles to a good cause:
> > http://folding.stanford.edu/
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
> > Sent: Sunday, December 18, 2005 2:01 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Upsize?
> >
> > As usual, JC is right. I would the following: search your front end for
> > record and row sources that begin with the word "SELECT" and convert all
> > instances to named queries before doing the upsize. Then these will
> > convert
> > as well, without the converter giving them incomprehensible names.
> > You may also have code that dynamically constructs SQL statements using
> > the
> > values of form controls. This is harder to fix, but in general 
>(depending
> > on
> > your convention) the string in question is called strSQL or maybe sSQL 
>or
> > whatever. The way to locate this is to look for the command below the
> > statement's construction: DoCmd.RunSQL <strSQL>. If you are serious 
>about
> > doing the conversion, then you want to isolate these instances, consider
> > whether they can be converted to stored procedures -- and if so, convert
> > them, and if not, tough luck; identify them as potential bottlenecks and
> > proceed.
> > <rant>
> > There are a VERY few occasions in which dynamic SQL construction is
> > required. In my experience, almost all occasions in which it is used are
> > due
> > to the lack of analysis on the part of the programmer. A stored 
>procedure
> > or
> > table-UDF will execute much more quickly than its equivalent
> > dynamically-constructed SQL statement, since there is no way that such a
> > statement can be optimized.
> > I have repeatedly encountered the objection that "I may or may not have
> > parameters x, y and z, the user may have chosen only one or two". This
> > objection is fallacious. The way to handle this possibility in a stored
> > procedure is to compare the parameter to itself, as this snippet
> > illustrates:
> > SELECT * FROM myTable WHERE AccountNumber = @AccountNumber OR
> > @AccountNumber
> > IS NULL You can perform this logic for any set of parameters that might 
>be
> > passed.
> > If you don't believe me, try it.
> > I am converting a colleague's app from MDB to SQL currently, and the MDB
> > contains about 100 occurrences of this sort of code. A form has several
> > controls on it, any combination of which might be used to create a 
>filter.
> > There are dozens and dozens of lines of code that anticipate that this 
>one
> > is null and the other two are not, etc. It is all silly, in both Access
> > and
> > SQL.
> > In SQL the simplest thing to do is to pass all the values of all the
> > relevant controls as parameters to the stored procedure, and let it deal
> > with nulls as outlined above. Dozens or hundreds of lines of code
> > disappear,
> > replaced by one call to the stored procedure.
> > In an MDB you need static functions to achieve the same, but that`s 
>cool.
> > Typically, you create a query that selects this and that from somewhere.
> > You
> > can name it and save it, but the problem is that you cannot conveniently
> > pass parameters to it. If you could, the code would reduce to one line
> > approximately; but you cannot, so the most common practice is to
> > dynamically
> > build an SQL statement and run it instead. Numerous lines of code
> > construct
> > a statement then passed to DoCmd.RunSQL. This is nonsense, IMO.
> > Let us suppose that a given query requires 3 parameters, any or all of
> > which
> > might be passed. Let us call them Country, Region and Customer. Suppose
> > that
> > the front lets the user select any combination of these controls, such
> > that
> > Country only results in a list of every customer in that country, and so
> > on.
> > Create a static function that gets or sets the value for each of these:
> > call
> > them CurrentCountry, CurrentRegion and CurrentCustomer. Each of these is
> > written to return a numeric value, either zero or greater than zero 
>(which
> > presumably references the PK of some other related table). Then write 
>the
> > query invoking these functions rather than addressing the particular 
>form
> > of
> > interest. Modify the query slightly so that at the end of the day it
> > resembles this:
> > SELECT * FROM somewhere
> > WHERE
> > Country = CurrentCountry() OR CurrentCountry() = 0 AND Region =
> > CurrentRegion() OR CurrentRegion() = 0 AND City = CurrentCity() OR
> > CurrentCity() = 0
> > ---------------------
> > I wrote this in Outlook, whose syntax checker does not extend to Access,
> > but
> > I hope this illustrates my points:
> > a) There is NO need to reference specific forms to obtain specific 
>values
> > to
> > plug into queries.
> > b) By wasting your time constructing dynamic SQL queries, you make your
> > code
> > tough to decipher and also bill the client for unnecessary hours.
> > c) By creating the static functions to plug into your query, you enable
> > said
> > query to be used in numerous situations. You can even run it from the
> > debug
> > window with no forms open. Just call the static functions in Set mode 
>then
> > run the query. No need to open the form or report. You will see the
> > results
> > immediately, and if they are wrong then fix the query not the form or
> > report.
> > d) on the chance that you might have to convert an MDB to SQL, queries
> > written this way are easy to port. Static functions can`t be used in SQL
> > of
> > course, but the query itself can be changed to refer to arguments in an
> > equivalent stored proc or table UDF. Estimated conversion time: 1 minute
> > per
> > instance.
> > And finally, the more you do this, the better you will like it.
> > </rant>
> > Sorry for the length of the rant, but once I get started watch out! LOL.
> > A.
> >
> > --
> > 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





More information about the AccessD mailing list