Radek
Thu Sep 09 17:43:28 CDT 2004
Robert,
we have a generic framework for building systems that deploy purely as web
services (see
http://www.asplications.com.au/GenieWhitePaper.pdf)). As this
is obviously based on a request-response pair, we do not rely on scrollable
cursors and thus 'large' result sets are not very useful (why populate
1000's of rows into a frid or listview). Instead, we rely on the explorer
metaphor, where we drill down or categorise items via a treeview/listview
combination. Nonetheless, we do provide for adhoc queries in terms of date
range constraints etc, and so there is scope for a user to request a query
that will return either far too much data to be practical, or will be too
'expensive'. I intend to provide the generic ability to set a threshold on
a query with dynamic constraints (many queries have fixed constraints) that
will return a warning/estimate to the user of what they will get should they
not tighten the constraints and execute the query. At the moment, I see
this only being implemented on about 3 queries, all within our financial
module.
Radek
"Robert Bouillon" <djwhizzard@hotmail.com> wrote in message
news:emwdMmnlEHA.3476@tk2msftngp13.phx.gbl...
> Is it necessary to make this judgement at run-time? I've found Query
> Analyzer's "Show Execution Plan" method to be very effective.
>
> Can you be a little more specific as to the type of query you'd want to
> check, and what the "escape route" might be? Maybe there's a better way. I
> would imagine that Analyzing a query at run time, on every execution could
> be quite expensive.
>
> --ROBERT
>
> "Radek Cerny" <radek.cerny@asplications.nospam.com.au> wrote in message
> news:%23mIY04jlEHA.1904@TK2MSFTNGP09.phx.gbl...
> > Thanks, I'll try that - good idea. I'll just wade through Estimated***
> > columns returned and see if it looks too expensive.
> >
> > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> > news:eivsLkjlEHA.3476@tk2msftngp13.phx.gbl...
> > > Hi Radek,
> > >
> > > Do this before "executing" a sql statament:
> > > using (SqlCommand cmd = new SqlCommand("SET SHOWPLAN_ALL ON",
> > > sqlConnection1))
> > >
> > > {
> > >
> > > cmd.ExecuteNonQuery();
> > >
> > > }
> > >
> > > Then, when you execute any sql statament you won't get the statement
> > result,
> > > but instead you'll get execution plan.
> > >
> > > Turn it OFF when you are done.
> > >
> > > BTW, there is also SHOWPLAN_TEXT option which returns plan in one
column
> > (as
> > > text).
> > >
> > >
> > > --
> > > Miha Markic [MVP C#] - RightHand .NET consulting & development
> > > miha at rthand com
> > > www.rthand.com
> > >
> > > "Radek Cerny" <radek.cerny@asplications.nospam.com.au> wrote in
message
> > > news:eyBT0RglEHA.3340@TK2MSFTNGP14.phx.gbl...
> > > > G'day,
> > > > I've had a decent look around, and cant seem to find any way to
get
> > the
> > > > cost of a query from anything in the System.Data namespace. I hope
I
> am
> > > > missing something obvious. What we want to do is have an optional
> step
> > > > where we prepare a query and/or somehow obtain the cost (or
estimated
> > rows
> > > > returned?) prior to execution and retrieval, and have an escape
route
> if
> > > > too
> > > > high.
> > > >
> > > > TIA
> > > >
> > > > Radek
> > > >
> > > >
> > >
> > >
> >
> >
>
>