record loading

Question

record loading

Jeff Moden

SSC Guru

Points: 1004432
More actions
January 5, 2023 at 1:22 am
Answer
#4133771

Bruin wrote:
Since there are millions of records for each day..
On Local server: Won't I have to find the max(t_stamp) for date 2/10/2022 and then Min(t_stamp) for 2/17/2022 then pass that into query(remote) so I get my 2/11 - 2/16 information?
Thanks for comments
You asked for 2/11 - 2/15. If you do as I recommend and ask for >= '2/11/2022' and < 2/16/2022, you will get ALL of 2/15.
And, of course, those have to be converted to UNIX timestamps first.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 11 posts - 46 through 55 (of 55 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

you have already been given the answer to your last (and prior questions) - if they are above your head then consider hiring a professional (as you have been advised multiple times)

Jeffrey Williams SSC Guru Points: 90312 More actions · Answer 2

As stated before - convert the dates to UNIX timestamps and use those values in your query. I would not try to figure out the end time unless the time was passed in exactly as expected.

If the end date/time is set to '2022-12-31' - when converted to a datetime it becomes '2022-12-31 00:00:00.000' and if the intention is to get everything on that day then it will not work because the time portion is not the end of the day. In that case, you add 1 day - get the UNIX timestamp and change the query to use less than instead of less than or equal.

With all that said - you had one example where you already did that but it had a few problems. These values are not strings - so don't need to quote them, and you need to know which version to use, so you convert the date/time to the correct version of UNIX timestamp.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Bruin wrote:

I ended up with this as my query...
Would there be any way to get the range of t_stamp values for 01-21-2022 thru 01-25-2022 and send that into query string rather than doing the date convert?
So midnight 01-21-2022 00:00:00 thru 01-25-2022 23:59:59 how can I get that starting number(t_stamp) and ending(t_stamp) and pass to query.
example:
starting 15250 ending 18200
Then I could replace the convert date piece and use (T-stamp >= xxx and T-stamp <= xxx)
If so, can you send example ..
Thanks.
select * 
from OPENQUERY(xxxx, 'select tagid,floatvalue,t_stamp from ignition.dbo.sqlt_data_1_2022_01
where 
(tagid not like ''7%'' and floatvalue is not null)
and
convert(date,DATEADD(s,t_stamp/1000,''1969-12-31 20:00:00'')) >= ''01-21-2022'' and convert(date,DATEADD(s,t_stamp/1000,''1969-12-31 20:00:00'')) <= ''01-25-2022''');

Ok, Bruin... look at that code and look at what we've previously stated.

The stuff that looks like this is to convert UNIX timestamps on the linked server to regular date times.

convert(date,DATEADD(s,t_stamp/1000,''1969-12-31 20:00:00'')

Just change both instances of that non-SARGable train wreck to just the following...

t_stamp

Calculate the UNIX timestamps for 2/11/2022 and 2/16/2022 AFTER you' ve converted those to UTC. Then use a comparison in the form of t_stamp >= (the timestamp for 2/11/2022) and t_stamp < (the timestamp for 2/26/2022).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2085 More actions · Answer 4

Since there are millions of records for each day..

On Local server:

Won't I have to find the max(t_stamp) for date 2/10/2022 and then Min(t_stamp) for 2/17/2022 then pass that into query(remote)

so I get my 2/11 - 2/16 information?

Thanks for comments

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

p.s. If there are millions of rows each day, this is the slow method. It would be better to have the remote machine export the data in the "native" format using BCP and then you import them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2085 More actions · Answer 6

Yes I changed the process to extract from remote machine first ..

Thanks for all comments and suggestions.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Bruin wrote:

Yes I changed the process to extract from remote machine first ..
Thanks for all comments and suggestions.

You're still using the linked server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2085 More actions · Answer 8

No I created a staging table on remote server and doing the ETL there before moving dataset back to Local server,,

Thanks.

Jeffrey Williams SSC Guru Points: 90312 More actions · Answer 9

Bruin wrote:

No I created a staging table on remote server and doing the ETL there before moving dataset back to Local server,,
Thanks.

But you are still using the linked server to 'move' the dataset to the local server. Any other process is going to be much more efficient than using a linked server - and will provide more flexibility in managing transaction log usage and performance.

@JeffModen's suggestion of using BCP and native format probably won't work - I am assuming the source system is not SQL Server based solely on the fact that a UNIX timestamp is used.

With that said - exporting to a file and using BCP or BULK INSERT and using the parameters available for batch/commit sizes would definitely have less impact on the log. SSIS might be faster since it can stream the data through the pipeline - extracting and loading at the same time and also has the capability of controlling the batch/commit sizes.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2085 More actions · Answer 10

Both systems involved are sql

remote:2012

Local:2016

I'm removing the Linked server doing all of the ETL on remote than going to use BCP to transfer to Local..