Re: Weird speed problem

On Oct 25, 3:13 pm, DBMonitor <spamawa...@xxxxxxxxxxxx> wrote:
I have a table on a database that contains 18million records. I need
to design a system that queries this table to produce fast counts.

I have got counts for multiple criteria down to only a few seconds.
Most take under a second however I have a few queries that seam to
take longer which I am working on reducing the time.

I have found some strange behavour in the way SQL Server works.

Take the following two queries which produce exactly the same result:

---------------------------------
select count(*)
from dbo.table
where column1='value1'
and column2='value2'
--------------------------------
and

--------------------------------
select count(*)
from (select id from table where column1 = 'value1') as value1
join (select id from table where column2 = 'value2') as value2
on value1.id = value2.id
---------------------------------

I would assume that the first query should run faster then the second
query. When I look at the query plans, they are almost identical cost
wise. The first takes about 53% of the cost and the second takes 47%.

Yet, the first query takes about 25 seconds to run and the second
takes only 5 seconds.

Does anyone know of a reason why there would be such a difference in
query speed?

BTW: Cubes are not an option in this senario.

.

Relevant Pages

  • Re: Simply confused - Query stopped working
    ... Here is my sql for the 2 queries (and yes I know there are ... Is Not Null) part of the query. ... Another approach would be to (first, make a backup!) use Compact & Repair to ... first Query and since I can use it in a iifexpression? ...
    (microsoft.public.access.queries)
  • Weird speed problem
    ... to design a system that queries this table to produce fast counts. ... I would assume that the first query should run faster then the second ... When I look at the query plans, they are almost identical cost ...
    (comp.databases.ms-sqlserver)
  • Re: Comparing weeks with highest and lowest sales Query--HELP
    ... I think you will have to use nested queries to get the result you want. ... SELECT TOP 3 DatePart("ww", saledate) As WeekNum ... Second query saved as QueryTwo: ... Only those fields requested in the first query can be included in an ORDER BY expression. ...
    (microsoft.public.access.queries)
  • Re: Need advice on speeding query up
    ... Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • Re: Weird speed problem
    ... the query the optimizer is using both indexes and then matching up the ... to design a system that queries this table to produce fast counts. ... I would assume that the first query should run faster then the second ... When I look at the query plans, they are almost identical cost ...
    (comp.databases.ms-sqlserver)