November 15, 2004 at 8:49 pm
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
November 15, 2004 at 11:37 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 15, 2004 at 11:43 pm
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
November 16, 2004 at 12:02 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2004 at 5:18 pm
Thanks Phil, query run time in QA got reduced to 2.5 min from 14min
November 17, 2004 at 2:32 am
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
November 17, 2004 at 12:22 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply