count (*) timing out

[aat]

Hi,



I have a dataset of 750+ records. Each record contains 6 particular numerical information that I need to rank: num1, num2, num3, num4, num5 and num6. After I know the rank each record obtains in each num, I average these 6 rankings, which gives an average rank (avgrk). So far, so good. I have been working with this for more than a year and never had a time out problem.

Now I posted a separated thread yesterday where I explained I wanted to rank each record on its avgrk; that, ultimate, rank (ovrlrk) would have ideally been calculated as a subquery within my same Q1 but a forum user explained me no way, get it done in a q2. My problem here is that my q2 does not respond; my access freeze and I have to crash the process after more than 10min.

Here are my codes:

Q1 called pctlrk

SELECT Q1.ID, Q1.[name], Q1.[Class], Q1.[num1], Q1.[num2], Q1.[num3], Q1.[num4], Q1.[num5], Q1.[num6], (select count(*) FROM tbl1 WHERE [num1] < [Q1].[num1];)+1 AS [num1rk], (select count(*) from tbl1 where [num2] > [Q1].[num2];)+1 AS [num2rk], (select count(*) from tbl1 where [num3] > [Q1].[num3];)+1 AS [num3rk], (select count(*) from tbl1 where [num4] > [Q1].[num4];)+1 AS [num4rk], (select count(*) from tbl1 where [num5] > [Q1].[num5];)+1 AS [num5rk], (select count(*) from tbl1 where [num6] > [Q1].[num6];)+1 AS [num6rk], ([num1rk]+[num2rk]+[num3rk]+[num4rk]+[num5rk]+[num6rk])/6 AS [avgrk]
FROM tbl1 AS Q1;

Q2 called ovrlrk

SELECT [Q2].ID, [Q2].[avgrk], (select count(*) from [ptclrk] where [avgrk] < [Q2].[avgrk];)+1 AS [Overall rk]
FROM pctlrk AS Q2;


For those who successfully pass this test, there is a subsidiary question: how can I have [overall rk] re-inserted back into my Q1 as the last field without creating a circular reference?

Thanks for all comments & suggestions!

Aat

[aat]

Hi again - I have been thinking further about this and want to check if one idea could give the direction to that problem.

With 750+ records I end up with single set of rankings looking pretty much like that: num1rk = 278, num2rk = 79, num3rk = 451, num4rk = 612, num5rk = 213, num6rk = 303. Which gives an average rk of 272.1666666666666...

Does the time-out come from the fact that most avgrk have an indefinite number of digits after the comma? If so, what could I used in my Q2 to query avgrk at a rounded figure to the two decimals?

Thank you again for any help.

Aat

[aat]

OK - a good old rounding works guys. I finally have my Q2 working.