Determining cost of a query

[Radek Cerny]

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

 

[Val Mazur]

Hi,

As I know System.Data does not have anything like this. I do not think there
is a simple way to do this and it should be database specific. It should be
related to the query execution plan, but I am not sure if you could easily
access it from your application

 

[Guest]

I know Oracle has a Cost Based Optimizer that does exactly this. Not sure
about MS SQL Server, but it must have something similar.

I believe the previous poster is correct about there not being anything in
the .NET frameworks for calculating the cost of a query. As this would be
specific to the database vendor you are using.

 

[Radek Cerny]

Thanks to both replies. We use SQLServer 2000 solely, so I guess I should
be a bit disappointed that this feature is not supported natively. Maybe
someone has some unmanaged code with a nice wrapper for this?

 

[Miha Markic [MVP C#]]

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).

 

[Radek Cerny]

Thanks, I'll try that - good idea. I'll just wade through Estimated***
columns returned and see if it looks too expensive.

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

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

 

[Robert Bouillon]

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

Thanks, I'll try that - good idea. I'll just wade through Estimated***
columns returned and see if it looks too expensive.

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

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

 

[Radek Cerny]

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

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

Thanks, I'll try that - good idea. I'll just wade through Estimated***
columns returned and see if it looks too expensive.

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

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 estimated
rows

route

if