Query Analyzer Slow - Management Studio Fast

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clint Good - 31 Mar 2008 11:15 GMT

Hi

If I run a simple "select top 10000 * from table" query against a SQL
2005 server from a client pc using management studio the query returns
in about a second. Running the same query on the same client pc using
the older query analyzer takes 25 seconds.

Does anyone know why/how this could happen ?

Do the two applications connect to SQL using different mechanisms ?
(Both QA and MS connect using windows authentication)

Regards
Clint.

Reply to this Message

John Bell - 31 Mar 2008 15:06 GMT

Hi Clint

Query analyser will be using an ODBC connection, have you got ODBC tracing on?

Are you returning both sets of results to grids/text?

Have you compared the query plans?

Are these on the same machine?

John

> Hi
>
[quoted text clipped - 10 lines]
> Regards
> Clint.

Reply to this Message

Clint Good - 31 Mar 2008 16:21 GMT

Hi John

> Query analyser will be using an ODBC connection, have you got ODBC tracing on?

ODBC. Thanks. I've left the office for the day, but I'm almost positive
it isn't on. Will check tomorrow.

> Are you returning both sets of results to grids/text?

Both QA and MgmtStudio results to text.

> Have you compared the query plans?

Didn't think to as it was just "select top 20000 * from table". I'd be
amazed if this was it as it is consistenty coming up with one good plan
for a MgmtStudio client and one bad one for QA client.

> Are these on the same machine?

Yes. Same machine.

Thanks for your reply
Regards
Clint.

Reply to this Message

Tibor Karaszi - 31 Mar 2008 17:32 GMT

> Both QA and MgmtStudio results to text.

Try executing both to grid and compare. Perhaps try with a smaller row number and work upwards and
compare grid/grid vs. text/text to see if there is a smaller difference in grid handling between QA
and SSMS compared to text handling.

I believe that MS did some smarts regarding text handling in SSMS where in QA there was lots of
overhead for "whitespace painting".

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi John
>
[quoted text clipped - 19 lines]
> Regards
> Clint.

Reply to this Message

Clint Good - 01 Apr 2008 00:28 GMT

Thanks for you reply Tibor.

>> Try executing both to grid and compare. Perhaps try with a smaller row number and work upwards and

I had done that. I mentioned 20000 in the original post because it
wasn't a huge amount of data and it demonstrated the order of magnatude
speed difference between the tools.

This was a bizarre problem. This had been occurring for a number of days
and then this morning I came into work and the problem was gone. To be
honest I don't know if we'll ever know what caused the (temporary ?)
problem.

Hopefully this one does not return.

Regards
Clint.

Reply to this Message