- This topic has 3 replies, 3 voices, and was last updated 2 weeks, 2 days ago by
June 28, 2020 at 6:44 pm #238775ParticipantTopics: 1Replies: 1Points: 17Rank: Member
Hi there, I’m new to this forum. I’m somewhat of a novice when it comes to Powershell but have been really getting into it lately. I’ve been wanting to execute some sql commands via powershell, however I’ve had some performance issues with Invoke-SqlCmd and getting the results saved to a file.
Here are my 2 lines:
Invoke-SqlCmd -ServerInstance "Server" -Database "Database" -Username "User" -Password "Password" -Query $Query | Export-Csv "Output.csv" -NoTypeInformation
SqlCmd -S "Server" -d "Database" -U "User" -P "Password" -Q $Query -o "Output.csv" -W -w 2048 -s","
Server info has been replaced with generic info for example purposes. The query and output location are exactly the same for both pieces of code.
The query returns about 12,000 rows. Here are the duration tests:
Type Duration Invoke-SqlCmd select only 00:00:17.47 Invoke-SqlCmd + export 00:11:56:21 SqlCmd select only 00:00:15.89 SqlCmd + export 00:00:29.61
As you can see, exporting to csv takes a very long time for just 12,000 rows at about 25x longer then SqlCmd.Am I doing something wrong? Is it because of the pipe? Is there any way to get around this?
Kind Regards, Bailey.
June 29, 2020 at 7:48 am #238880ParticipantTopics: 5Replies: 2384Points: 6,066Rank: Community MVP
I don’t have any experience with SQL but if you just wnat to avoid the pipeline you could try this:
$QueryResult = Invoke-SqlCmd -ServerInstance "Server" -Database "Database" -Username "User" -Password "Password" -Query $Query Export-Csv -Path "Output.csv" -NoTypeInformation -InputObject $QueryResult
June 29, 2020 at 8:52 am #238919ParticipantTopics: 1Replies: 1Points: 17Rank: Member
Unfortunately this does not work, the object that is stored is only the information about the query, not the query itself. The resulting csv looks like this:
Count Length LongLength Rank SyncRoot IsReadOnly IsFixedLength IsSynchronized 12810 12810 12810 1 System.Object FALSE TRUE FALSE
I’ve read that Export-Csv only works with pipes or single objects (like a query with 1 row for example). I’ve also tried using
| Out-Filebut this is just as slow as piping into Export-Csv and is not in the correct format.
June 29, 2020 at 8:23 pm #239129ParticipantTopics: 3Replies: 431Points: 1,533Rank: Community Hero
I would try it in powershell 7, it fixes several of the windows powershell speed issues.
- You must be logged in to reply to this topic.