Query Analyzer Problem

Question

Query Analyzer Problem

Daksha

Valued Member

Points: 51
More actions
November 15, 2004 at 8:49 pm

#86934

Hi there
I am running a simple query on a table having 5375542 rows. it takes a second to execute the query in Enterprise manager (by running SQL on Table).
But if the same query is been run using query analyzer it takes 13-14min.
Does anybody know why????
below is the sample of query
select * from abcTable where postedby <> 'System'
and FId in (1,2,3,4,5) and code in (select code from codeTable where grouping in ('abc') or code = 'efg')
and there is already an index of "postedBy,Fid and code"
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

I've got an idea that I've read somewhere that EM returns the results of such a query in 'batch' mode - allowing you to see subsets of results as they are obtained - whereas QA executes the entire query before displaying the results. But I can't find this documented anywhere.

To test this, try putting an ORDER BY clause in your query. This should force EM to execute the entire query before displaying any results and the EM/QA apparent execution times should be much closer.

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Daksha Valued Member Points: 51 More actions · Answer 2

thanks phil

yes it got time out by doing order by in EM, do you know how can i speed up the query response.

thanks for your reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

First thing: do you really need

select * ...

or can you just select the fields you need?

Next thing to do is replace the subquery with a join and replace the IN with a BETWEEN:

select a.field1, a.field2, ..., a.fieldn

from abcTable a

inner join codetable c on a.code = c.code

where a.postedby 'System'

and (a.FId between 1 and 5) and

(c.grouping = 'abc' or c.code = 'efg')

and finally ensure appropriate indexes in place.

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Daksha Valued Member Points: 51 More actions · Answer 4

Thanks Phil, query run time in QA got reduced to 2.5 min from 14min

Iain Parker SSC-Addicted Points: 409 More actions · Answer 5

You might be able to reduce the time a little more by using an 'exists' instead of the join on the code table.

Regards,Iain

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

Hi,

I checked my Query Analyzers. SQL Server 2000 waits till all results are returned. SQL Server 2005 Beta posts results while they are being obtained.

I know there are 2 server options - see advanced configuration options for sp_configure

cost threshold for parallelism - this one sets how long the query should run before the second processor takes over, can speed up your query

cursor threshold - sets how many rows of cursor are processed before the result is returned to the calling program or user

I know we are not talking about the cursor here, but both Query Analyzer and EM are front-ends written in some languages: SQL_DMO or whatever. They can use ADO. IN ADO there is an option adExecuteFetchNonBlockig of Execute command that specifies who rows are returned asynchronously while the command still executes

Yelena

Regards,Yelena Varsha