none
Timeout Exception or slow execution time when using strongly typed tableadapter but not when using SqlDataAdapter RRS feed

  • Question

  • (Sorry if my english is not perfect. And btw this is my first post)
    Recently I've come across a strange behaviour. I created a new tableadapter/datatable in my dataset (let's say myTableAdapter, myDataTable and myDataSet). The tableadapter just has a Fill method. When I created it in the DataSet, I executed the query and everything was fine. It took about 1.5 to 2 seconds to complete.

    Now here's what I do in my code

    Dim ta AS new myDataSetTableAdapters.myTableAdapter
    Dim dt AS new myDataSet.myDataTable
    ta.Fill(dt)

    I've already done this many times without any issues, but in this case, instead of executing the query in about 2 seconds, it takes 25 seconds or even more (above 30 I get a timeout exception)
    I found this strange and just for testing I manually created the query, connection and data adapter in my code.

    (Created the exact same query used to create de tableadapter in the dataset)
    Dim query AS String = "SELECT * FROM ...."
    Dim conn AS new SqlConnections(...)
    Dim command AS new SqlCommand(query, conn)
    Dim ta AS new SqlDataAdapter(command) -----> instead of "Dim ta AS new myDataSetTableAdapters.myTableAdapter"
    Dim dt AS new myDataSet.myDataTable

    ta.Fill(dt) -------> now it took about 2 seconds!

    This is only the second times I've come across this behaviour, and as a temporary fix I do the above. But I would like to know the reason. I'm using Visual Studio 2008 Professional Edition and SQL Server 2005 Express Edition.
    Thanks in advance for your help.

    Thursday, July 16, 2009 8:19 PM

Answers

  • The only remaning suggestion that I have is to go and add some debugging into the the TableAdapter to see what it taking so long. The TableAdapter.Fill method is just a wrapper over the existing SqlDataAdapter. So what you could do is in the following

    1)Create a partial class that refers to this TableAdapter
    2)Copy the existing Fill on the Table adapter into this code and call it Fill2
    3) Supplement the Fill2 code with blocks of getting the current time like so

    timesnap1 =DateTime.Now;
    sqlDataAdapter.Fill(dataTable)
    timesnap2 = DateTime.Now;
    Console.WriteLine(timesnap2-timesnap1)

    This will essentially allow you to performance profile the code and identify what in the TableAdapter.Fill is taking time to do this. The reason I'm saying to copy and create another method is because the TypedDataSet Generated code will change when you go and make changes or open and close the DataSet Designer. It regenerates the file. So by creating a method not in the file but attached to the class through the partial class this will allow you a way to profile the code.

    When you use the TableAdapter initially it has to initialize some pieces, but subsequent calls do not require this. So look into whether on the first call it takes longer than the second.

    Lastly I'm sorry for repeating this, but you have to make 100% sure the same query is actually being executed. If its not the same all this profiling is useless.

    Other than giving advise on how to profile the problem I can't really sugggest anything more. There must be some differences in the Fill's that are occuring but none that are apparent to me.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 17, 2009 2:57 PM

All replies


  • Is this reproduceable? Every time you call fill on the TableAdapter does it always take longer right now as compared to your calls to the SqlDataAdapter?

    If this is a one time thing could it be that there was increased network traffic or a machine issue?

    I would look into profiling the code some more. If you have 2008 with the VS profiler built in I would use this to analyze how its running.

    Thanks
    Chris Robinson
    Program Manager DataSet

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 17, 2009 12:07 AM
  • The only times I came across this issue was with 2 particular queries. It never happened with others. 

    With the first query, the problem appeared a little bit random. About 75% of the time I was executing the query with the table adapter, I had a timeout exception, but 25% of the time it ran fine. By using the SqlDataAdapter, it runs fine everytime in less than 5 seconds.

    With the second query, the problems happens 100% of the time. It's always between 25 and 30 seconds (25 seconds most of the time) with the table adapter, and 2 seconds with the SqlDataAdapter

    I have bigger queries that run fines anytime in the day so network traffic shouldn't be an issue. Anyway I previously tested this at 9pm when I was alone on the network and it happended again. I also tried this in another computer with the same results.

    Sorry but could you explain what you meant about profiling the code. I'm quite new to visual studio and never used the profiler. In the meantime I'll try to find info about this.

    Thanks for your quick reply
    Friday, July 17, 2009 1:37 AM
  • Here is an article to on how to find perf bottlenecks

    http://msdn.microsoft.com/en-us/magazine/cc337887.aspx

    Are you sure the same queries are being executed?

    Thanks
    Chris Robinson
    Program manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 17, 2009 5:44 AM
  • Thanks for the article. And yes the exact same queries are executed in my code and in the table adapter. And, like I said, if I open myDataset.xsd in the designer, go into the query builder, and execute the queries, both runs fine (first in 5 sec, second in 2 sec), so I doubt the query is the problem. The problem is just when using the myTableAdapter.Fill in the code.
    Friday, July 17, 2009 12:25 PM
  • The only remaning suggestion that I have is to go and add some debugging into the the TableAdapter to see what it taking so long. The TableAdapter.Fill method is just a wrapper over the existing SqlDataAdapter. So what you could do is in the following

    1)Create a partial class that refers to this TableAdapter
    2)Copy the existing Fill on the Table adapter into this code and call it Fill2
    3) Supplement the Fill2 code with blocks of getting the current time like so

    timesnap1 =DateTime.Now;
    sqlDataAdapter.Fill(dataTable)
    timesnap2 = DateTime.Now;
    Console.WriteLine(timesnap2-timesnap1)

    This will essentially allow you to performance profile the code and identify what in the TableAdapter.Fill is taking time to do this. The reason I'm saying to copy and create another method is because the TypedDataSet Generated code will change when you go and make changes or open and close the DataSet Designer. It regenerates the file. So by creating a method not in the file but attached to the class through the partial class this will allow you a way to profile the code.

    When you use the TableAdapter initially it has to initialize some pieces, but subsequent calls do not require this. So look into whether on the first call it takes longer than the second.

    Lastly I'm sorry for repeating this, but you have to make 100% sure the same query is actually being executed. If its not the same all this profiling is useless.

    Other than giving advise on how to profile the problem I can't really sugggest anything more. There must be some differences in the Fill's that are occuring but none that are apparent to me.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 17, 2009 2:57 PM
  • I have this exact same situation that happens from time to time.  I have never found a solution.  I end up just playing around with the underlying stored procedure (or the query if I'm using a query) and eventually something changes enough to let it go fast.  It is a frustrating situation because the query goes fine in the Management Studio, but just on the Table Adapter it's painfully slow.  It is slow when I use "Preview Data" from within the Table Adapter also.

    So Jon09 - did you ever find an answer?

    EB
    Thursday, February 11, 2010 6:00 PM
  • Lightspeed,

    This doesn't answer your question, but is there any chance that you could get rid of the TableAdapters in favor of DataAdapters?

    If you've read some of my posts here, you'd see that I keep harping on TableAdapters. Sorry to be constantly doing that, but it's something I feel strongly about. TableAdapters are frustrating and confusing and I have never liked them. They are, in effect, wrappers around the DataAdapter which actually does the work. Check out my 3-part series on Data Access for some ideas of making it all a bit simpler by not using TableAdapters. You may, or may not, want to re-work your existing app, but at least keep some of this in mind for any future work you do:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Even just the first blog would be enough to get you started with DataAdapters and away from using those confounded TableAdapters (in that first post, there's a link to my rant against TableAdapters).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, February 13, 2010 6:01 PM
  • I have this situation also, and I solve it by a "Magic Space":

    At the the end of my TableAdapter's select command, I change the "@MinBalance)" into "@MinBalance )".

    A white space is added between the parameter @MinBalance and the last closing bracket. The @MinBalance parameter is in Decimal type and is inside the HAVING statement of the select command.

    Thursday, July 29, 2010 8:14 AM
  • SET ARITHABORT ON in a stored procedure ***dramatically*** speeds up performance of filling TableAdapter for RDLC reports from a stored procedure.
    Monday, February 3, 2020 5:52 PM