Post reply

Can Large Data Extractions from SQL cause connection problems

  • October 15, 2007 at 9:50 am

    #70370

    Hello,

    We have a packaged software that seems to abort/stop processing on certain days when there is a large extraction that is being performed at 8:00 am. Is there somekind of limit on how much data can be extracted at one time in sql? I've never heard of such a thing but all fingers are pointing to sql as the culprit.. 😉

    Thanks so much,

    Juanita

  • October 15, 2007 at 10:53 am

    #742069

    It depends on what is causing the "abort". If you mean - are there conditions where a long-running SQL task could stop others from running? Yes, if the "first" task puts a lock on something the second task needs, and keeps the lock on it past the timeout of the second one.

    The "default" timeout on ODBC queries tends to be 45 seconds (unless there's been an update made) - meaning, if a given query fails to return anything within 45 seconds, it will return a failure code. Any process that locks up a resource for longer than that could cause a second process to "fail".

    Without knowing the two things are - it's going to be hard to tell you what's happening. But - is it possible? sure. Can it be helped? perhaps.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 15, 2007 at 11:10 am

    #742072

    Also, when the large extract is occurring, how are things with the overall health of your server. Is it using up all the CPU, Memory and Disk I/O's that are available to SQL? As locks begins to be taken and you start to see contention issues, more memory is being used to hold all of those things. Again without knowing much about your process it's difficult to do more than take a stab in the dark as to the root of the problem.

    You might try to start a trace before the large extract and review the rules of the trace. Yes it will add overhead, but if you do it from another machine that will be lessened somewhat.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

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

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