[AccessD] [dba-SQLServer] Optimizing Stored Procedures and Views

David Emerson newsgrps at dalyn.co.nz
Mon Apr 10 21:43:26 CDT 2006


Dear All,

The problem was solved by storing the data from the view that caused 
the problem into a temporary table, then basing the main procedure on 
the new table.

Thanks all for your help.

David

At 8/04/2006, you wrote:
>Yikes,
>    This query was tough on you.  I hope you get better soon!
>
>On 4/7/06, David Emerson <newsgrps at dalyn.co.nz> wrote:
> > Dear all,
> >
> > Thanks for all your suggestions.  I was given 18 hours notice to turn
> > up to our hospital for a pacemaker replacement (I wore out the old
> > one - too much late night/early morning programming).
> >
> > Operation successful but I feel as if someone hit my shoulder with a
> > sledgehammer (which they well might have done considering the banging
> > that was going on during the operation).
> >
> > Hope to be back on deck next week to follow up the suggestions.
> >
> > David
> >
> > At 6/04/2006, you wrote:
> > >If this is a very busy table you can generally use table hints to
> > >prevent locking the table, and is generally very fast, simplay add a
> > >WITH(NOLOCK) at the end of your table names (not variable tables).
> > >
> > >
> > >On 4/5/06, David Lewis <DavidL at sierranevada.com> wrote:
> > > > Well, there is a lot there to begin to look through...
> > > >
> > > > One thing that sticks out is the
> > > >
> > > > "SELECT TOP 100 PERCENT"   in the underlying view.  Remove that and the
> > > > "ORDER BY" clause and you will do much to streamline the execution of
> > > > the view.  Also consider making as many of the queries covered as
> > > > possible by either adding indexes or removing columns from the views
> > > > that are not needed in the final query.
> > > >
> > > > I am not very experienced with streamline execution plans by optimizing
> > > > joins, but I do try to read what comes my way and I have read that all
> > > > joins are NOT created equal.  Because your joins are 
> relatively complex,
> > > > it may be that there is much to do in that area.  When I find a good
> > > > reference I will pass it on...
> > > >
> > > > D. Lewis
> > > > _______________________________________________
> > > > dba-SQLServer mailing list
> > > > dba-SQLServer at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > > http://www.databaseadvisors.com
> > > >
> > > >
> > >
> > >
> > >--
> > >-Francisco
> > >http://pcthis.blogspot.com |PC news with out the jargon!
> > >http://sqlthis.blogspot.com | Tsql and More...
> > >_______________________________________________
> > >dba-SQLServer mailing list
> > >dba-SQLServer at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > >http://www.databaseadvisors.com
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
>--
>-Francisco
>http://pcthis.blogspot.com |PC news with out the jargon!
>http://sqlthis.blogspot.com | Tsql and More...
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com




More information about the AccessD mailing list