0

Sometime in a program the user launching a very big query to extract data from DB(in this case SQLserver).

String SQL_SELECT = " SELECT * FROM BIG_TABLE";    

SqlConnection conn = null;
SqlDataReader aReader = null;

conn = getConnessione();

SqlCommand aCommand = new SqlCommand(SQL_SELECT, conn);

conn.Open();
aReader = aCommand.ExecuteReader();

It's possible to know the estimated time before the command

aReader = aCommand.ExecuteReader();

Are there a command or a best practices ?

daniele3004
  • 13,072
  • 12
  • 67
  • 75
  • 4
    I am gonna go out on a limb here and say no – David Pilkington Jan 10 '17 at 08:18
  • Maybe [this post](http://stackoverflow.com/questions/2108041/c-sharp-sqldatareader-execution-statistics-and-information) can help you. – diiN__________ Jan 10 '17 at 08:20
  • 1
    @diiN__________ This requires the query to be run – David Pilkington Jan 10 '17 at 08:21
  • Yes, you can estimate. The question is, based on what would you like to estimate? There is no API for giving you an "it will take X seconds" kind of estimate. If you tell me the query is `SELECT * FROM Table`, and how many rows there are, how large the rows are, your network speed, your server's memory, your storage speed and your application's processing time, I can estimate... but that's probably not what you're looking for. The typical approach is to supply appropriate timeouts (the default is 30 seconds) and then optimize as it becomes necessary. – Jeroen Mostert Jan 10 '17 at 08:22
  • Oh, and, of course (almost forgot!) *running the query and measuring*. You can do that before executing the query on *production* -- just test it before. If it takes 3 hours on a test machine, then production might be faster (depending on your hardware) but it's pretty safe to say it's not a fast query. – Jeroen Mostert Jan 10 '17 at 08:26
  • Actually -- what do you need this time for? A progress indicator? Feedback to the user before they click the button? A watchdog timer? Is the query always the same? Is the server? In the latter case a safe estimate is simply the number of rows in the table, and there are fast ways of obtaining that. – Jeroen Mostert Jan 10 '17 at 08:29
  • @JeroenMostert Feedback to the user before they click the button, I need a strategy to reach this goal – daniele3004 Jan 10 '17 at 08:31
  • Run this query in background thread on application starting and save execution time. Then if user will start this query you will have estimated time which will be somehow close to the real one. – Fabio Jan 10 '17 at 08:33
  • And is your query always the same (a simple linear dump of all rows)? A conservative estimate could be to read the top X rows, measure how long this takes, get a rowcount and then extrapolate. This will still take a bit of time to execute, but not as much as the whole query (you could do this offline and periodically refresh the estimate). However, if you get at that point, you probably want to include a progress indicator as well, as this will give users a much better impression of how long things are going to take. – Jeroen Mostert Jan 10 '17 at 08:33
  • Most databases service more than a single user. Even if you received a 100% reliable estimate (which for various reasons many have pointed out is unlikely), by the time you *act* on that estimate information, the reality may have changed due to external factors. E.g. after you estimate and before your query starts, someone else runs `BEGIN TRANSACTION; SELECT * FROM BIG_TABLE WITH (XLOCK); WAITFOR DELAY '20:00:00'; ROLLBACK` - well, guess what - your query is going to take (at least) 20 hours longer to run than your estimate. There's no API for guessing the future. – Damien_The_Unbeliever Jan 10 '17 at 09:13
  • @Damien_The_Unbeliever Thank you. The only way it's build an euristc function to approximate the time ? You can write a response – daniele3004 Jan 10 '17 at 09:22

1 Answers1

0

Theoretically speaking you don't.

There's a lot of factor in play.

Size of table, amount of rows, amount of columns, indexes, query factors such as where's (Where will it use indexes etc.), network speed (Ex. if it's remote the estimated time will be more than if the database is hosted local) etc.

Bauss
  • 2,767
  • 24
  • 28