Are query analyzer run times a reliable source? 2

[NervousRex]

Is the time it takes query anylzyer to execute a SP comparable to the time the same SP would be called from an outside application?


My problem is I have an app that calls 4 SPs to load up a report, this report time outs 95% of the time at end of month as more data is entered. (Time outs occuring after 30secs)


Now if I run the SPs in query analyzer, they take less than a combined 30secs.

I ran profiler...the one SP does have a huge hit, as it has multiple joins and such as it totals and sums different values. The read value is around 9mill?


Any ideas of what to test, look for?

 

[gmmastros]

The default timeout for ADO is 30 seconds. If you are using ado, then you could set the ado object's command timeout property to 0.

ex. Connection.CommandTimeoute = 0

Setting the value to 0 causes the timeout to be disabled. Some people get really nervous about that, so you could set the value to 60, 90, 500, anything big enough to prevent your report from timing out.

 

[NervousRex]

Thanks

I will do this, but I am still confused as to why I can run the full report in query analyzer way under 30secs and then when get the time outs from the app.

 

[Catadmin]

I'm guessing, but:

Because QA is directly connected to the SQL Server and probably doesn't have a timeout set at all. ADO has to do some processing for itself before it can send the info to QA, then send the info to QA, wait for QA to respond, and then do whatever internal processing it needs to do with the responded data.

Are you running the ADO app on the machine with SQL Server on it or are you running the app from another machine? That will definately make a difference.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

I would check the connection string and command string parameters. QA doesn't returns a FireHose I think.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[NervousRex]

Another machine

Well I set the timeout period to 300, guess my only other option we be to rewrite the query, but I really don't know of anyway to speed it up.


Thanks for the help

 

[donutman]

I meant to say, "QA returns a firehose cursor."
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

Donutman,

I've seen "Firehose cursor" before in an error message.

Can you explain what this is or at least point me to a reference?

Thanks,



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

It's a forward only cursor and I don't believe it's updateable (i.e. from QA). Consequently, it's very fast. Within your ADO connection, you can specify a server side or client side cursor. This is not an area of expertise for me, but I think server side cursors are generally faster. You can also control the type of cursor. There are several and each has its advantages and disadvantages. I'm fairly sure that QA uses one that is optimized for speed. I'm suggesting that may be the cause of the difference in speed for the OP (original poster).
If he cares to share his SP, we may be able to fine tune it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Term "firehose cursor" is somewhat misleading. IMO it's only "cursor" element is row-by-row transmission to client. True cursors generate horrible amount of logical reads. See term "default result set" in BOL glossary.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Catadmin]

Ah. Thanks muchly, Donutman & Vongrunt. I greatly appreciate the clarification.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!