how to increase the speed for large datasets?

  • Thread starter Thread starter pemt
  • Start date Start date
P

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);
 
Make sure that the Rank and Value fields are indexed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


pemt said:
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.


pemt said:
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
Click to expand...
Click to expand...
 
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

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


pemt said:
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.


pemt said:
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
Click to expand...
Click to expand...
Click to expand...
 
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)


pemt said:
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.


pemt said:
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
Click to expand...
Click to expand...
 
Steve said:
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
Click to expand...


You forgot your pimping line.

John...
 
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

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


pemt said:
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.


pemt said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top