Michael Maddison
michael at ddisolutions.com.au
Mon Dec 19 21:59:34 CST 2005
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
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz
Sent: Tuesday, 20 December 2005 4:05 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Upsize?
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.bellsou
>th.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