how to increase the speed for large datasets?

[pemt]

Dear all,

Karl Dewey helped me to write a nice code to deal with combining records
into range (below), however, it run really slow when there are over 1000
ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
finally to raise the speed? If yes, how to do that?

Thank you very much for your help.

pemt


Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

 

[Jerry Whittle]

Make sure that the Rank and Value fields are indexed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Dear all,

Karl Dewey helped me to write a nice code to deal with combining records
into range (below), however, it run really slow when there are over 1000
ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
finally to raise the speed? If yes, how to do that?

Thank you very much for your help.

pemt


Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
. .
. .
. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
...

thanks,

pemt

 

[pemt]

Jerry,

Thanks. They are indexed. Is it possible to set up certain number of ranges
as one bin and only calculate in each bin, and finally combine results from
all bins? how?

pemt

Make sure that the Rank and Value fields are indexed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Dear all,

Karl Dewey helped me to write a nice code to deal with combining records
into range (below), however, it run really slow when there are over 1000
ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
finally to raise the speed? If yes, how to do that?

Thank you very much for your help.

pemt


Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
. .
. .
. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
...

thanks,

pemt

 

[Steve]

Hello Pemt,

The two queries are very complex and it is not surprising that they run
slow. Karl may not have done you any favor recommending those queries.
Please describe your real data and what you want to do and perhaps a
different solution is possible that will be much faster. If you posted that
somewhere in the past, where and when did you post it?

Steve
(e-mail address removed)

Dear all,

Karl Dewey helped me to write a nice code to deal with combining records
into range (below), however, it run really slow when there are over 1000
ranges. Is it possible to run every 20 or 100 ranges and combine all
ranges
finally to raise the speed? If yes, how to do that?

Thank you very much for your help.

pemt


Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd,
pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
. .
. .
. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
...

thanks,

pemt

 

[John... Visio MVP]

Hello Pemt,

The two queries are very complex and it is not surprising that they run
slow. Karl may not have done you any favor recommending those queries.
Please describe your real data and what you want to do and perhaps a
different solution is possible that will be much faster. If you posted
that somewhere in the past, where and when did you post it?

Steve

You forgot your pimping line.

John...

 

[pemt]

Hi Jerry,

Sorry for the mistake in last reply.
Actaully I only can index Value. How to index "Rank" without running the 1st
query first or how to index "Rank" with just running the 2nd query?
Thanks,

pemt

Make sure that the Rank and Value fields are indexed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Dear all,

Karl Dewey helped me to write a nice code to deal with combining records
into range (below), however, it run really slow when there are over 1000
ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
finally to raise the speed? If yes, how to do that?

Thank you very much for your help.

pemt


Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
. .
. .
. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
...

thanks,

pemt