query grindingly slow!

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

Guest

I've created a simple query which numbers a list with repeated items based on their date
ie., three fields
- creation_dat
- issue_req_no_
- ordernumber count (based on date

I have this

SELECT T.creation_date, T.issue_req_no_s, (SELECT COUNT(issue_req_no_s) FROM Table1 T
WHERE T1.issue_req_no_s = T.issue_req_no_
AND T1.creation_date <= T.creation_date) AS Expr
FROM Table1 AS
ORDER BY T.issue_req_no_s

Its seems to be extremely slow and cause my computer to crash. Its not the computers fault, so whats wrong with my query

thanks

marcus
 
marcus said:
I've created a simple query which numbers a list with repeated items based on their date.
ie., three fields:
- creation_date
- issue_req_no_s
- ordernumber count (based on date)

I have this:

SELECT T.creation_date, T.issue_req_no_s, (SELECT COUNT(issue_req_no_s) FROM Table1 T1
WHERE T1.issue_req_no_s = T.issue_req_no_s
AND T1.creation_date <= T.creation_date) AS Expr1
FROM Table1 AS T
ORDER BY T.issue_req_no_s;


Its seems to be extremely slow and cause my computer to crash. Its not
Click to expand...
the computers fault, so whats wrong with my query?
thanks,

marcus
Click to expand...


SELECT T.creation_date
,T.issue_req_no_s
,(SELECT COUNT(issue_req_no_s)
FROM Table1 T1
WHERE T1.issue_req_no_s = T.issue_req_no_s
AND T1.creation_date <= T.creation_date) AS Expr1
FROM Table1 AS T
ORDER BY T.issue_req_no_s;


Without knowing the structure of the tables (and datatypes), the number of
rows, the indexing invovled, the versions of Access, Jet, and MDAC, plus the
OS, the RAM, and the CPU, it's hard to tell for sure.

However, I notice that there is no alias on "issue_req_no_s" in the COUNT
scalar function.

Try:

SELECT T.creation_date
,T.issue_req_no_s
,(SELECT COUNT(T1.issue_req_no_s)
FROM Table1 T1
WHERE T1.issue_req_no_s = T.issue_req_no_s
AND T1.creation_date <= T.creation_date) AS Expr1
FROM Table1 AS T
ORDER BY T.issue_req_no_s;
 
Hi

Microsoft doesn't handle aggregate queries very well. I suspect, in queries
like yours, or like SELECT * FROM a WHERE f1 IN (SELECT f1 FROM b), the
subquery is running anew for every row in master query (but I'm not very
sure about this).

Anyway, you beter create a saved query, like
YourSubquery, with SQL-string
SELECT issue_req_no_s, COUNT(issue_reg_no_s) AS issue_count FROM Table1
GROUP BY issue_regno_s

and then your query will be
SELECT T.creation_date, T.issue_req_no_s, T1.issue_count FROM Table1 T,
YourSubquery T1 WHERE T1.issue-reg_no_s= T.issue-reg_no_s ORDER BY
T.issue-reg_no_s


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


marcus said:
I've created a simple query which numbers a list with repeated items based on their date.
ie., three fields:
- creation_date
- issue_req_no_s
- ordernumber count (based on date)

I have this:

SELECT T.creation_date, T.issue_req_no_s, (SELECT COUNT(issue_req_no_s) FROM Table1 T1
WHERE T1.issue_req_no_s = T.issue_req_no_s
AND T1.creation_date <= T.creation_date) AS Expr1
FROM Table1 AS T
ORDER BY T.issue_req_no_s;


Its seems to be extremely slow and cause my computer to crash. Its not
Click to expand...
the computers fault, so whats wrong with my query?
 
Marcus

In addition to the other suggestions, I'll point out that you get better
query performance when the selection & sort fields you are using are indexed
in the underlying table. If you are basing a query on joined tables, the
join fields need to be indexed for best performance.

You didn't mention if the tables were local or linked. If linked and stored
in SQL Server or some other enterprise-strength back-end, have you
considered writing a procedure that runs on that backend, rather than as a
query in the front-end?
 
I've tried Chris2 suggestion and it seems to work a little better, but certainly hasn't solved it. Everything is local, they are all large downloads from the server, and i'm currently running of my hard drive. using access 2000 on Windows XP Pro, with Pentium 4 2.6 Mhz and 2000 mb RAM

Woops, the new improved query just went into 'not responding' again when i tried to go to the last record.

Table1 is actually a subquery.. it does this
SELECT DataExtract01.creation_date, DataExtract01.issue_req_no_
FROM DataExtract0
GROUP BY DataExtract01.creation_date, DataExtract01.issue_req_no_
ORDER BY DataExtract01.issue_req_no_s

(returns 13,735 records
(original table (DataExtract01) and extract from server is 172,091 records

I think i've answered all your questions... let me know if i have missed something out

thanks again

marcus
 
1) What Service Release of Access 2000?
2) What Service Pack of JET?
3) What version of MDAC?
4) What are the structure of the tables (their DDL would be best)?

5) What is the size of the Swap file? What are it's minimum and maximum
sizes? (I still haven't looked at XP, so I don't know if they're accessible
the same way as there are in Win2k and Win9x, or if the same issues apply.)

6) What is the fragmentation level of the hard drive?
7) When was it last defragmented?
8) How much total space is left on the hard drive where the .mdb file is?



marcus said:
I've tried Chris2 suggestion and it seems to work a little better, but
Click to expand...
certainly hasn't solved it. Everything is local, they are all large
downloads from the server, and i'm currently running of my hard drive.
using access 2000 on Windows XP Pro, with Pentium 4 2.6 Mhz and 2000 mb RAM.
Woops, the new improved query just went into 'not responding' again when i
Click to expand...
tried to go to the last record.
 
Hi

Mybe you start from anfang an. You have a table DataExtract01, and you want
to get from this table fields creation_date and issue_reg_no_s and to do
something with those 2 fields? What exactly?

Your query below feels a bit strange for me. GROUP BY clause is usually used
in summary queries, but you have here only 2 groping fields there. Or you
omitted there something, or the GROUP BY clause is abundant. Again - try to
describe, what you want exactly to do with data from table DataExtract01.
Form what you have said to us, I did at last get impression, that no
subqueries are needed at all - probably you can do all with one go.


--

Arvi Laanemets
(When sending e-mail, use address (e-mail address removed))



marcus said:
I've tried Chris2 suggestion and it seems to work a little better, but
Click to expand...
certainly hasn't solved it. Everything is local, they are all large
downloads from the server, and i'm currently running of my hard drive.
using access 2000 on Windows XP Pro, with Pentium 4 2.6 Mhz and 2000 mb RAM.
Woops, the new improved query just went into 'not responding' again when i
Click to expand...
tried to go to the last record.
 
Back
Top