Slow Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a select query that is providing the desired results. The only problem is that it is very slow when using it with other queries or reports… it takes up to an hour to process. My actual data table contains about 1,700 records. If I run the query by itself, it takes less than a minute. I have also tried to run it as a “make a table queryâ€, but that takes over an hour as well. Any ideas on why it is so slow

My select query

SELECT T.ID, T.DownCode, (SELECT DownCode FROM DownCode WHERE ID = (SELECT Max(ID) FROM DownCode T1 WHERE T1.ID < T.ID)) AS PrevDownCod
FROM DownCode AS T

Nic
 
Assuming ID is the primary key of DownCode, you might try something like
this:

SELECT
DownCode.ID,
DownCode.DownCode,
(SELECT TOP 1
Self.DownCode
FROM
DownCode AS Self
WHERE
Self.ID < DownCode.ID
ORDER BY
Self.ID DESC)

If ID is an AutoNumber field, be aware that AutoNumber fields are not
guaranteed to be increasing. If you are trying to order things
chronologically, you might considering adding a Date/Time field (say, that
you populate with a default value of Now()) and using this field as the
basis for finding the "previous" record.

Nick said:
I have a select query that is providing the desired results. The only
Click to expand...
problem is that it is very slow when using it with other queries or reports.
it takes up to an hour to process. My actual data table contains about
1,700 records. If I run the query by itself, it takes less than a minute.
I have also tried to run it as a "make a table query", but that takes over
an hour as well. Any ideas on why it is so slow?
My select query:

SELECT T.ID, T.DownCode, (SELECT DownCode FROM DownCode WHERE ID =
Click to expand...
(SELECT Max(ID) FROM DownCode T1 WHERE T1.ID < T.ID)) AS
PrevDownCode
 
Nick said:
I have a select query that is providing the desired results. The only
Click to expand...
problem is that it is very slow when using it with other queries or reports.
it takes up to an hour to process. My actual data table contains about
1,700 records. If I run the query by itself, it takes less than a minute.
I have also tried to run it as a "make a table query", but that takes over
an hour as well. Any ideas on why it is so slow?
My select query:

SELECT T.ID, T.DownCode, (SELECT DownCode FROM DownCode WHERE ID =
Click to expand...
(SELECT Max(ID) FROM DownCode T1 WHERE T1.ID < T.ID)) AS
PrevDownCode
FROM DownCode AS T;
Click to expand...

I guess you didn't consider RAC/MSDE:).

Your taking a big performance hit because of the number of comparisons
required by the predicate T1.ID < T.ID in the subquery.This is further
compounded
when you use this query embedded with other queries.You can only push a
rdbms
so far with this type of logic.If it makes you feel any better you will find
the same
situation on Sql Server.
Hope this answers your question:).

Happy Holidays:)

Tools for Sql Server @
www.rac4sql.net
 
Nick,

Send me your Access db/sample data,explanation(s) and
and example of the exact result your looking for.
At the very most maybe I can give you an alternative
Access method, at the very least a footnote in RAC
examples:)
Send to (e-mail address removed)

Happy Holidays,
Steve aka RTF,OBD
www.rac4sql.net
 
OK... I will clean-up the db to keep the size down and then send it to you.

Thanks for the help!

Happy Holidays!!!

----- Otis B. Driftwood wrote: -----

Nick,

Send me your Access db/sample data,explanation(s) and
and example of the exact result your looking for.
At the very most maybe I can give you an alternative
Access method, at the very least a footnote in RAC
examples:)
Send to (e-mail address removed)

Happy Holidays,
Steve aka RTF,OBD
www.rac4sql.net
 
Back
Top