Trying to cut down query times

External Since: Mar 25, 2004 Posts: 6

I have a query that is based on two other queries that are based on tables
in a database.

All of the queries that are used for this final query use grouping and
totals.

In the application I am making, I need to choose a date range to select the
subset of the data I need. This is done in the final query (it isn't known
until just before I run the query)

In the tests I have run, with the base queries selecting all the data for
all the dates, the whole thing takes more than 50 minutes to run. If I
interject the date range into the base query the whole thing takes les than
5. The only problem is that I cannot edit the base queries as this report
may be run at the same time by more than one user for more than one date
range.

I have looked in the bol and have been trying things with subqueries, but
from waht I can see, subqueries can only be used to parse data out of
another table, not merge data between two tables into one final recordset...

Help, someone has to have done this somewhere before, if you have a better
idea, or know how to do it with subqueries and know where i can find an
example, I would really appreciate it, I have been working on this for a
couple of days now.

Thanks

External Since: May 25, 2004 Posts: 67

Can you post some actual code?

--
Adam Machanic
SQL Server MVP
<a style='text-decoration: underline;' href="http://www.sqljunkies.com/weblog/amachanic" target="_blank">http://www.sqljunkies.com/weblog/amachanic</a>
--

"Atley" <atley_1.RemoveThis@homtmail.com> wrote in message
news:uooENiFFFHA.3888@TK2MSFTNGP12.phx.gbl...
> I have a query that is based on two other queries that are based on tables
> in a database.
>
> All of the queries that are used for this final query use grouping and
> totals.
>
> In the application I am making, I need to choose a date range to select
the
> subset of the data I need. This is done in the final query (it isn't
known
> until just before I run the query)
>
> In the tests I have run, with the base queries selecting all the data for
> all the dates, the whole thing takes more than 50 minutes to run. If I
> interject the date range into the base query the whole thing takes les
than
> 5. The only problem is that I cannot edit the base queries as this report
> may be run at the same time by more than one user for more than one date
> range.
>
> I have looked in the bol and have been trying things with subqueries, but
> from waht I can see, subqueries can only be used to parse data out of
> another table, not merge data between two tables into one final
recordset...
>
> Help, someone has to have done this somewhere before, if you have a better
> idea, or know how to do it with subqueries and know where i can find an
> example, I would really appreciate it, I have been working on this for a
> couple of days now.
>
> Thanks
>
>