extract all data with week

Question

Post reply

extract all data with week

Viewing 5 posts - 31 through 34 (of 34 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Open Windows Powershell ISE - this is the development tool for Powershell included with Windows 10.

You will need to include 2 variables that I excluded from the post. Those are:

$SQLServer = "your SQL instance name";

$DBName = "your database name";

You can then run the code directly in the ISE.

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

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

frederico_fonseca wrote:

thanks Jeff.
so a bit slower on my pc - I7 4 core , 16GB ram, SSD drive - 29 seconds for same criteria.
on this type of multiple files output, and where possible, I have experience with some teams using AWK to split the files - beating up using SQL for big number of files (had 1 process outputting 800 files from same table - diff criteria- AWK would take a few seconds compared to a loop similar to the one we did which would take 20 min+)

I ran this on my desktop in the office - the server is in our DC across the street with a very large pipe between our building and the DC. From my desktop it ran in 15-16 seconds.

Ran it directly on the server - that one ran in 28 seconds.

I then ran it from my laptop over a VPN (Texas to Las Vegas to Texas DC and back). That took almost 2 minutes to complete.

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: 1004324 More actions · Answer 3

Jeffrey Williams wrote:

Open Windows Powershell ISE - this is the development tool for Powershell included with Windows 10.
You will need to include 2 variables that I excluded from the post. Those are:
$SQLServer = "your SQL instance name";
$DBName = "your database name";
You can then run the code directly in the ISE.

Cool. Thanks, Jeffrey... I got it working. I was really concerned that my system had a problem because here's the timing I got on my first run (I left the server and database names off in the graphic)...

The Delta-T there is 6 minutes and 5 seconds and I'm on a laptop with a 6 core (threaded to 12) 8th Gen i7 running up to 4GHz, 32 GB Ram, 2TB of NVME SSD, and SQL Server 2017 DE. It just shouldn't have been running so slow.

I did a check on the table and it turns out the table currently had 100 million rows (for a different experiment)... ten times the amount you good folks have been kind enough to test with. When I run it with "only" 10 million rows, here's what I get...

That's only 21 seconds and I feel MUCH better now! 😀

I also did a run where the CI was on a different column than the SomeDateTime column. It only added 2 seconds.

So thanks for the code and the testing you've done. I now have something to compare to and learned a little about PoSh in the process.

--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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

I don't think the difference in the systems is the determining factor in how fast this runs - it appears to me to be network related. As shown above, running this against the same server and table across the VPN took almost 2 minutes - but only 15 seconds from my desktop.

Not real sure why it runs that much faster on my desktop though - guessing it could be related to WAN optimization.

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: 1004324 More actions · Answer 5

For me, it was the initial run of having an extra 90 million rows. 😀

--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)