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