February 5, 2022 at 1:39 am
I hope this isn't too long, but I wanted you to understand why I am doing what I am doing/
I have a group of oil tanks numbered 1 through 30 that are linked to a master tank via hoses. When 10 tanks fill up, their valves open and send the oil to the master tank. Then the valves close and the empty tanks start to fill up again. The oil in the master tank gets loaded into a tanker and waits for the next 10 tanks to fill it up. The tanks fill at different rates so the tanks fill and empty at a different pattern. Each record contains columns for tank number, date, the number of each master tank drain and the last time a tank has been drained. I also subtract each time tank drained into the master tank from the master drain number. This tells me what tanks fill up the least and the most. The following query works well but with over 150,000 rows (and growing), it takes too long to run. What I want to do is to exit the while loop after it finds the top record for each tank. I'm not sure how the while statement works, but I am guessing that the while loops through all 150,000 records for each tank number. Is there a way to run the query any quicker? Thanks
[Code]
Select Top 1 @AlphaCounter as Tank,@seqnbr as CurSeqNbr,DrainSeq as Last_Seq_Nbr,@seqnbr-DrainSeq as Varcount
from Tankcount
Where @alphacounter in (Tank01, Tank02, Tank03, Tank04, Tank05, Tank06, Tank07, Tank08, Tank09, Tank10)
and @seqnbr-DrainSeq > 0
order by DrainSeq desc
[/code]
[Code]
Drop table if exists LastTimeTankDrained
Create Table LastTimeTankDrained
(Tank Varchar(2),LastDrain int ,LastTimeTankDrained int ,TimesBetweenTankDrain int )
Drop Table if exists Tankcount
create table Tankcount (
TankDate varchar(10),
DrainSeq int,
Tank01 varchar(2),
Tank02 varchar(2),
Tank03 varchar(2),
Tank04 varchar(2),
Tank05 varchar(2),
Tank06 varchar(2),
Tank07 varchar(2),
Tank08 varchar(2),
Tank09 varchar(2),
Tank10 varchar(2))
Insert into Tankcount(TankDate, DrainSeq, Tank01,Tank02,Tank03,Tank04,Tank05,Tank06,Tank07,Tank08,Tank09,Tank10)
Values ('2021-01-01',1,'04','08','09','11','14','16','22','23','25','28'),
('2021-01-01',2,'01','06','08','10','15','17','18','19','23','30'),
('2021-01-01',3,'04','05','18','20','22','23','25','26','27','28'),
('2021-01-01',4,'06','08','10','12','15','17','18','24','28','30'),
('2021-01-01',5,'06','09','14','15','16','17','23','25','27','29'),
('2021-01-01',6,'02','05','10','12','13','15','22','23','24','27'),
('2021-01-02',7,'04','06','08','09','13','19','21','24','29','30'),
('2021-01-02',8,'01','05','07','08','12','13','17','20','24','25'),
('2021-01-02',9,'08','12','14','17','18','19','20','21','24','25'),
('2021-01-02',10,'01','09','10','15','19','20','22','23','24','30'),
('2021-01-02',11,'08','16','18','21','22','23','25','27','28','30'),
('2021-01-02',12,'01','03','04','08','09','10','12','21','23','30'),
('2021-01-03',13,'03','04','05','07','09','10','11','14','15','25'),
('2021-01-03',14,'01','03','04','06','08','16','19','22','26','28'),
('2021-01-03',15,'02','03','05','08','11','12','14','15','20','26'),
('2021-01-03',16,'01','02','04','05','08','17','20','22','27','30'),
('2021-01-03',17,'01','14','16','17','19','23','25','26','28','30'),
('2021-01-03',18,'01','04','05','08','14','16','18','25','27','29'),
('2021-01-04',19,'05','08','11','14','15','18','19','22','27','29'),
('2021-01-04',20,'01','02','07','10','23','24','27','28','29','30'),
('2021-01-04',21,'01','04','05','06','07','11','20','21','25','27'),
('2021-01-04',22,'02','07','12','14','15','17','18','19','26','27'),
('2021-01-04',23,'01','05','06','12','13','14','19','23','28','29'),
('2021-01-04',24,'03','04','06','08','14','15','19','23','26','30'),
('2021-01-05',25,'04','05','09','10','20','21','22','26','29','30'),
('2021-01-05',26,'01','02','05','07','08','10','11','13','14','22'),
('2021-01-05',27,'04','06','10','12','13','19','22','27','28','29'),
('2021-01-05',28,'04','05','06','10','13','14','16','27','28','29'),
('2021-01-05',29,'02','03','08','11','17','23','25','28','29','30'),
('2021-01-05',30,'05','07','09','10','14','16','17','20','25','29'),
('2021-01-06',31,'03','04','07','09','10','24','25','28','29','30'),
('2021-01-06',32,'06','07','13','16','19','20','22','25','26','28'),
('2021-01-06',33,'02','04','06','07','08','15','18','20','25','27')
DECLARE @Counter INT
Declare @SeqNbr as Int
Declare @Alphacounter as varchar(2)
SET @SeqNbr = (SELECT Max(DrainSeq) FROM Tankcount)
SET @Counter=0
WHILE ( @Counter <= 30)
Begin
SET @Counter = @Counter + 1
Set @AlphaCounter = RIGHT('00' + CONVERT(VARCHAR(2),@Counter),2)
Insert into LastTimeTankDrained
(Tank ,LastDrain ,LastTimeTankDrained ,TimesBetweenTankDrain )
Select Top 1 @AlphaCounter as Tank,@seqnbr as CurSeqNbr,DrainSeq as Last_Seq_Nbr,@seqnbr-DrainSeq as Varcount
from Tankcount
Where @alphacounter in (Tank01, Tank02, Tank03, Tank04, Tank05, Tank06, Tank07, Tank08, Tank09, Tank10)
and @seqnbr-DrainSeq > 0
order by DrainSeq desc
END
select * from LastTimeTankDrained [/Code
February 5, 2022 at 4:03 am
What I want to do is to exit the while loop after it finds the top record for each tank.
How would one determine that programmatically? TOP doesn't mean anything without some kind of sort order.
February 5, 2022 at 4:46 am
Select Top 1 @AlphaCounter as Tank,@seqnbr as CurSeqNbr,DrainSeq as Last_Seq_Nbr,@seqnbr-DrainSeq as Varcount
from Tankcount
Where @alphacounter in (Tank01, Tank02, Tank03, Tank04, Tank05, Tank06, Tank07, Tank08, Tank09, Tank10)
and @seqnbr-DrainSeq > 0
order by DrainSeq desc
END
select * from LastTimeTankDrained
Doesn't the line "Order by DrainSeq Desc" count for order?
February 5, 2022 at 12:01 pm
What the data represents is confusing me a little, can you expand on it a little, please?
Take TankDate 1/1/2021 as an example. There are 6 rows, with DrainSeq ranging from 1 to 6. Does that mean that the master tank was drained 6 times on 1/1/2021?
For Tank1 to Tank10, I assume that the number they contain represents one of the 30 feeder tanks, is that correct?
Why is LastDrain 33 for all of the different tanks?
Without fully understanding the data, I would expect to be able to make things run much faster by
a) Normalising the source table
Even if you can't do this to the permanent data, you can create a temp table in your query as step 1 and then query that
b) Using TINYINT across the board as your data type, to avoid all of those nasty conversions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 5, 2022 at 1:58 pm
>Take TankDate 1/1/2021 as an example. There are 6 rows, with DrainSeq ranging from 1 to 6. Does that mean that the master tank was drained 6 times on 1/1/2021?
Yes Here is a table of days and the count of each time the master tank was drained. The data is an example and just happens to have 6 drains a day except for the last day which was 3.
TankDateDailycount
2021-01-016
2021-01-026
2021-01-036
2021-01-046
2021-01-056
2021-01-063
>For Tank1 to Tank10, I assume that the number they contain represents one of the 30 feeder tanks, is that correct?
Yes
>Why is LastDrain 33 for all of the different tanks? Lastdrain is an index of the number of times the master tank has been drained. It is an integer and increases by one each time the master tank is drained. I subtract the last time that a tank was drained from the last time the master tank was drained and this becomes the number I am looking for. For example, the master tank was drained 11 times since the last time tank 18 was drained.
>> a) Normalising the source table. Pardon me, I don't understand what you mean by normalizing. My understanding is normalizing is between two or more tables.
> b) Are you talking about using tinyints instead of varchar(2)? I actually use the tank number in other places and varchar works better for me.
Thanks for your response.
February 5, 2022 at 4:45 pm
Please take a look at this and see what you think
DROP TABLE IF EXISTS #LastTimeTankDrained;
CREATE TABLE #LastTimeTankDrained
(
Tank CHAR(2)
,LastDrain INT
,LastTimeTankDrained INT
,TimesBetweenTankDrain INT
);
DROP TABLE IF EXISTS #Tankcount;
CREATE TABLE #Tankcount
(
TankDate VARCHAR(10) NOT NULL
,DrainSeq INT NOT NULL
,Tank01 CHAR(2) NOT NULL
,Tank02 CHAR(2) NOT NULL
,Tank03 CHAR(2) NOT NULL
,Tank04 CHAR(2) NOT NULL
,Tank05 CHAR(2) NOT NULL
,Tank06 CHAR(2) NOT NULL
,Tank07 CHAR(2) NOT NULL
,Tank08 CHAR(2) NOT NULL
,Tank09 CHAR(2) NOT NULL
,Tank10 CHAR(2) NOT NULL
);
INSERT #Tankcount
(
TankDate
,DrainSeq
,Tank01
,Tank02
,Tank03
,Tank04
,Tank05
,Tank06
,Tank07
,Tank08
,Tank09
,Tank10
)
VALUES
('2021-01-01', 1, '04', '08', '09', '11', '14', '16', '22', '23', '25', '28')
,('2021-01-01', 2, '01', '06', '08', '10', '15', '17', '18', '19', '23', '30')
,('2021-01-01', 3, '04', '05', '18', '20', '22', '23', '25', '26', '27', '28')
,('2021-01-01', 4, '06', '08', '10', '12', '15', '17', '18', '24', '28', '30')
,('2021-01-01', 5, '06', '09', '14', '15', '16', '17', '23', '25', '27', '29')
,('2021-01-01', 6, '02', '05', '10', '12', '13', '15', '22', '23', '24', '27')
,('2021-01-02', 7, '04', '06', '08', '09', '13', '19', '21', '24', '29', '30')
,('2021-01-02', 8, '01', '05', '07', '08', '12', '13', '17', '20', '24', '25')
,('2021-01-02', 9, '08', '12', '14', '17', '18', '19', '20', '21', '24', '25')
,('2021-01-02', 10, '01', '09', '10', '15', '19', '20', '22', '23', '24', '30')
,('2021-01-02', 11, '08', '16', '18', '21', '22', '23', '25', '27', '28', '30')
,('2021-01-02', 12, '01', '03', '04', '08', '09', '10', '12', '21', '23', '30')
,('2021-01-03', 13, '03', '04', '05', '07', '09', '10', '11', '14', '15', '25')
,('2021-01-03', 14, '01', '03', '04', '06', '08', '16', '19', '22', '26', '28')
,('2021-01-03', 15, '02', '03', '05', '08', '11', '12', '14', '15', '20', '26')
,('2021-01-03', 16, '01', '02', '04', '05', '08', '17', '20', '22', '27', '30')
,('2021-01-03', 17, '01', '14', '16', '17', '19', '23', '25', '26', '28', '30')
,('2021-01-03', 18, '01', '04', '05', '08', '14', '16', '18', '25', '27', '29')
,('2021-01-04', 19, '05', '08', '11', '14', '15', '18', '19', '22', '27', '29')
,('2021-01-04', 20, '01', '02', '07', '10', '23', '24', '27', '28', '29', '30')
,('2021-01-04', 21, '01', '04', '05', '06', '07', '11', '20', '21', '25', '27')
,('2021-01-04', 22, '02', '07', '12', '14', '15', '17', '18', '19', '26', '27')
,('2021-01-04', 23, '01', '05', '06', '12', '13', '14', '19', '23', '28', '29')
,('2021-01-04', 24, '03', '04', '06', '08', '14', '15', '19', '23', '26', '30')
,('2021-01-05', 25, '04', '05', '09', '10', '20', '21', '22', '26', '29', '30')
,('2021-01-05', 26, '01', '02', '05', '07', '08', '10', '11', '13', '14', '22')
,('2021-01-05', 27, '04', '06', '10', '12', '13', '19', '22', '27', '28', '29')
,('2021-01-05', 28, '04', '05', '06', '10', '13', '14', '16', '27', '28', '29')
,('2021-01-05', 29, '02', '03', '08', '11', '17', '23', '25', '28', '29', '30')
,('2021-01-05', 30, '05', '07', '09', '10', '14', '16', '17', '20', '25', '29')
,('2021-01-06', 31, '03', '04', '07', '09', '10', '24', '25', '28', '29', '30')
,('2021-01-06', 32, '06', '07', '13', '16', '19', '20', '22', '25', '26', '28')
,('2021-01-06', 33, '02', '04', '06', '07', '08', '15', '18', '20', '25', '27');
DECLARE @SeqNbr AS INT;
SELECT @SeqNbr = MAX(DrainSeq)
FROM #Tankcount;
WITH Ordered
AS (SELECT t.DrainSeq
,x.FeederTank
,rn = ROW_NUMBER() OVER (PARTITION BY x.FeederTank ORDER BY t.DrainSeq DESC)
FROM #Tankcount t
CROSS APPLY
(
VALUES
(t.Tank01)
,(t.Tank02)
,(t.Tank03)
,(t.Tank04)
,(t.Tank05)
,(t.Tank06)
,(t.Tank07)
,(t.Tank08)
,(t.Tank09)
,(t.Tank10)
) x (FeederTank) )
SELECT o.FeederTank
,LastDrain = @SeqNbr
,LastTimeTankDrained = o.DrainSeq
,TimeBetweenDrains = @SeqNbr - o.DrainSeq
FROM Ordered o
WHERE o.rn = 1
AND @SeqNbr - o.DrainSeq > 0
ORDER BY o.FeederTank DESC;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 5, 2022 at 7:49 pm
I'll be out of town this weekend, but it looks promising. Thanks
February 8, 2022 at 4:43 pm
Thank you for your help. I tweaked it a bit and it's working fine.
February 8, 2022 at 4:54 pm
Great. How long does it take on your live table?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 8, 2022 at 7:20 pm
Just a second or two. One of the changes I made was instead of pulling all records, I just pull the top 50. Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply