Post reply

opening table in EM causes vb applications run time errors

  • April 23, 2007 at 1:57 pm

    #62495

    We have a table that has over 4.8 million rows, and when the table (returna all rows) is opened in EM, our vb applications gets run time (time out) errors.

    Looks like SQL is allocating all resources to EM.  Any help is appreciated...Rich

  • April 23, 2007 at 10:04 pm

    #702067

    pointing out the obvious, but you should not be trying to display all 4.8 million rows in EM while connected to a production database....

    you are right, EM and SQL server would take up 100% of the CPU trying to load the grid with a displayable table in EM, and that would cause any and all other apps to get application timeout issues, as their default timeout is going to be 30 seconds of waiting and no answer from SQL Server.

    Why would you need to display all 4.8 million rows? you should be using a TOP 50 or something if you are just peaking at the data, and a WHERE statement if you are looking for something specific.

    Get a copy of the database on a development machine so you can orient yourself with the data, if that is what you are doing, but don't do it on production.

    another alternative is to change the application to have .ConnectionTimeout and .CommandTimeout values of zero(wait forever) or a reasnable timeout like 300 (seconds). note that that is just a band aid to work around the bad practice of allowing ad hoc queries on your server, which kills performance.

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 24, 2007 at 6:32 am

    #702143

    Yes, I agree the table should not be opened to display all rows in the production environment.  One of our developers was working on an issue and opened this table to review some data.  This table contains log entries and is keyed by an Identity column which makes it hard to use a where clause. I would assume the data that needed to be reviewed would also be near the end of the rows.  I know when I use the Option for Design, I am prompted that this table contains a large amount of data and do I want to continue; I was hoping maybe this feature could be implemeted in this case; or how can I prevent ad hoc queries being run on this table?  Thanks again for the information on the .timeout values, I will inform our VB developer.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply