[dba-SQLServer] dba-SQLServer Digest, Vol 50, Issue 10

David Lewis DavidL at sierranevada.com
Fri Apr 20 12:14:47 CDT 2007


John:  If your tables are very large, and you find that just storing sql
statements impacts performance (that is, if you run the query as
execsql, which is the least desireable from the point of view of
performance), you could do much more with this approach.  For example,
the sql you store could be something like "CREATE VIEW vw..... blah blah
blah" where it appends all the portions of the statement from the
various tables, and at the end it also does things like "create
statistics.... " etc. so that the views actually run efficiently.
Anyway playing around with the concept will doubtless give you some
ideas on how to make it work for your needs.  Just a thought.  D

------------------------------

Message: 2
Date: Thu, 19 Apr 2007 18:22:53 -0400
From: "JWColby" <jwcolby at colbyconsulting.com>
Subject: Re: [dba-SQLServer] Managing orders database
To: <dba-sqlserver at databaseadvisors.com>
Message-ID: <001901c782d1$46790320$657aa8c0 at m6805>
Content-Type: text/plain;	charset="us-ascii"

Long term that is actually a much better solution.  

There is ATM a set of a few tables in one database.  As I take orders
(counts) I build the sets of views that implement them, but the views
reference data (currently) out in another database where the actual data
is
stored.

Actually it is more than a where clause though.  It typically takes
anywhere
from one to 4 joins, sometimes outer joins, then pulling where fields
and
counts.  Thus I tend to use base views and views joined to views, with
those
counting and filtering.  I do like the concept of storing SQL in a
table,
particularly since I could then have a real order database with an order
table.


John W. Colby
Colby Consulting
www.ColbyConsulting.com





More information about the dba-SQLServer mailing list