Running the following command took 10.922 seconds:
bcp "select * from PRMTAJ_Reporting..PINVDET
where pnd_date between 77500 and 77600" queryout Test.dat -S . -T -n
However, running the same exact select query in SSMS took 28 seconds. Also, I ran the query in SSMS after the bcp command so it's not like bcp could have benefitted from me having running the query in SSMS first... if anything it would have been the other way around.
I did this for several ranges of pnd_date values and every time was the same. I ran the bcp command first and then ran the same select query in SSMS and each time the SSMS query took at least twice as long to complete.
SSMS is only retrieving the results whereas bcp has the added overhead of having to write the results to a new file on disk. This makes absolutely zero sense to me... but I'm hoping I can learn something that will make my SSMS queries as fast as bcp here. Can anyone explain?