July 22, 2008 at 10:52 pm
Hi all,
I currently have a view which contains information of 5 different tables. This view contains millions and millions of records. How do I provide an estimated row count before running the actual query against this view so users of our C# application would know how long (estimated) it will take to run a query?
SELECT * from ViewParameters where DVTFlag = 'Run'
select count (id) doesn't work. I need to return an estimated row count within a few seconds so user can decide if they want to continue with the query or not.
Really appreciate your helps!
Amy
July 22, 2008 at 10:59 pm
Amy,
you can try this..
1- SELECT count('x') from ViewParameters where DVTFlag = 'Run'
2- SELECT count(*) from ViewParameters where DVTFlag = 'Run'
if you wants to count later then use this:
select @@rowcount
Cheers!
Sandy.
--
July 23, 2008 at 12:40 am
You can get estimated row counts quickly from this View: http://qa.sqlservercentral.com/scripts/tables/62545/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 6:20 am
There's no way to pass a where clause and get an estimated row count that I'm aware of.
That said, if you get an estimated execution plan, you'll get an estimated row count too... There might be some way to try to work with that, but I'm not sure how. Uh, call the query and return an XML plan and then query the XML plan to get the row count? I'm not entirely sure that will work.
Maybe someone has already done something like that?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 23, 2008 at 9:15 am
I read about the estimated execution plan but I don't know know to use it. 🙁 I will be reading more into it now. Select count (id) or select count (*) doens't work in my situation since the view is too big.
If anybody has done anything similar to this, please help.
Thanks,
Amy
July 23, 2008 at 9:28 am
Getting an estimated execution plan is no big deal. There's a button right there in the query window in Management Studio.
No, what you need is something more. The estimated plan does two things you need. It supplies an estimated row count and it doesn't actually execute the query. It just puts it through the optimizer which accesses the statistics available and generates a plan. You would probably need to use the XML plan (SET SHOWPLAN_XML ON). Run the proc and... now I'm fuzzy as to how/if it would work... capture the XML output in your code (something I've never even attempted) and then run an XQuery against the XML to get the row count (that's easy).
It's the capturing the plan part that I just don't have a clue how to do.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 23, 2008 at 10:44 am
Did you try this query? It does exactly what you asked for: http://qa.sqlservercentral.com/scripts/tables/62545/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 11:10 am
That query lists the rows & columns & stuff for the tables in a db, but I think what the OP is asking for is a way to determine the rows returned by a given query, which may be joining & filtering data such that having only a count of rows available for a given table doesn't supply.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 23, 2008 at 12:29 pm
Ah, I see. My bad.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 12:33 pm
I solved my problem :-). The code below is written in C# on how to get the execution plan.
string connectionString = "Data Source=server; Initial Catalog=database; User Id=user; Password=pw; Connection Timeout=5000";
string selectstr = "SELECT * from viewParameter where DvtFlag = 'GoNoGo' ";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand sqlCmd = new SqlCommand("SET SHOWPLAN_XML ON", connection);
sqlCmd.ExecuteNonQuery();
sqlCmd.CommandText = selectstr;
string xmlPlanStr = "";
SqlDataReader reader = sqlCmd.ExecuteReader();
while (reader.Read())
{
xmlPlanStr += reader.GetSqlString(0).ToString();
}
connection.Close();
Thanks all that replied 🙂
July 23, 2008 at 12:55 pm
Oh well. I was happy too soon. The execution plan estimated way off the actual row count. Back to square one for me.
July 23, 2008 at 1:03 pm
That's either one of two things, your statistics are out of date or, the estimated plan is far off from the actual. It does happen. Try updating your statistics (or rebuilding your index) and try it agin.
Like I said, I wasn't aware of any process that did this consistently.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 23, 2008 at 2:31 pm
I was happy too soon 🙁 The estimated row count from the execution plan is way off from the actual row count. Back to square one for me.
July 23, 2008 at 9:55 pm
AIKE!! The application tier stealing a top secret execution plan. All power to shields!!!!
July 25, 2008 at 8:43 am
you can always create a SQL agent job to count the actual rows the night before and store them in your own tables. Then you can have multiple rows in your table with Scenario a, b, c, with actual row counts, typical seconds to complete the query, etc.
For example, you users then: "select RowCount, RowTime from MyStats where scenario=a" and they get last nights statistics from your custom MyStats table. Run your custom build stats job whenever you want. Or give the users the ability to exec a proc the updates the stats, when they want.
With that many rows in a query, you really need to create procs to bring back a managable row set.
Cheers
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply