ExamGuru IT/.Net Guru : Determining cost of a query on microsoft.public.dotnet.framework.adonet

.NETGURU

Determining cost of a query

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

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

Reply to this message...

--dweezil (VIP)

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" wrote:

[Original message clipped]

Reply to this message...

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?

"--dweezil" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
> I know Oracle has a Cost Based Optimizer that does exactly this. Not
sure
[Original message clipped]

Reply to this message...

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

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Radek Cerny" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Radek Cerny

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:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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

"Radek Cerny" <Click here to reveal e-mail address> wrote in message
news:%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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

"Robert Bouillon" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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

--
Val Mazur
Microsoft MVP

"Radek Cerny" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

System.Data.SqlClient.SqlCommand

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification