Query execution takes different times from different clients

Hi everyone,
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
Andrew
Try turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>
|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>
|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>