0

I have a script that retrieves metadata of files and uploads them to a SharePoint site. The problem is that there are around a million files and the process is really slow and I was wondering if there is a more efficient way of doing this.

The current script works like this

$ProjectName = Invoke-Sqlcmd -ServerInstance "Something" -Database "Something" -Query "SELECT * FROM something WHERE ID = 150

$Comments = Invoke-Sqlcmd -ServerInstance "Something" -Database "Something" -Query "SELECT * FROM something WHERE ID = 100

And then I manipulate the variables to upload them to SharePoint. The problem is that I have around 50 of these variables and the process is very slow, I imagine that the process can be faster by invoking/connecting to the database only once and iterating over the SQL queries but I am not too sure about how to do this.

I am a bit new to PowerShell so any help on this would be greatly appreciated, I just want to run SQL queries in bulk in one Invoke-Sqlcmd statement, is this possible?

Thank you in advance!

Luis
  • 1
  • 1
  • Why are you making the exact same DB query 50 times? Why not just reuse the first result set? – Mathias R. Jessen Sep 01 '22 at 14:59
  • Sorry yes, I should have specified, each of the variables store different metadata for each file. So each query retrieves one field of metadata. I will edit the question to reflect this. – Luis Sep 01 '22 at 15:02
  • And this metadata is stored in different tables, or in the same table? Might as well request _all the metadata_ up front in a single query if it's the latter – Mathias R. Jessen Sep 01 '22 at 15:04
  • Yes, all of the metadata is in the same table. So if I were to request all of the metadata in one go, how would I assign it to different variables? I need them separated so I can uploaded it against the right file in SharePoint. Thank you for your answers. – Luis Sep 01 '22 at 15:07
  • You can always sort and manipulate the data once its returned from the db (`SELECT *` will include the `ID` field). My point is that doing a single query `"SELECT * FROM table WHERE ID IN (150,100,25,40)"` is definitely going to be faster than 4 individual queries for a single row – Mathias R. Jessen Sep 01 '22 at 15:09

0 Answers0