Post reply

Query Analyzer Problem

  • 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

  • November 15, 2004 at 11:37 pm

    #530224

    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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 15, 2004 at 11:43 pm

    #530226

    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

    #530229

    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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 16, 2004 at 5:18 pm

    #530382

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

  • November 17, 2004 at 2:32 am

    #530432

    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

    #530536

    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