December 14, 2013 at 1:30 pm
I took over a Powershell-script from a collegue who left the company. This script makes a snapshot from a database, reads some text-files containing queries (special users are allowed to modify those queries) and writes the results to several csv-files by using Export-CSV in Powershell. This worked fine for a few weeks: 6GB is written within 90 minutes (not that fast, but fast enough), but since a week the performance is getting worse everyday and yesterday it did not even finish with 24 hours !? We checked index-fragmentation, but that is not too bad.
What I am looking for: what is a good method to give users freedom to make their own queries and dump the result by a scheduled task (or something else).
December 14, 2013 at 2:28 pm
pven (12/14/2013)
...what is a good method to give users freedom to make their own queries...
To be absolutely honest, that statement is a bit of an oxymoron because most users are ill equiped to write queries. They don't know about proper indexing, SARGability, Divide'n'Conquer, etc, etc, ad infinitum.
That, notwithstanding, the key here is that the method you're using is probably just fine for allowing users to "make their own queries". You need to go see what those queries actually are and find out why they're taking so long.
You also talk about them making CSVs from large tables. Hopefully, the CSVs aren't that large but you stated they're writing 6GB in about 90 minutes. The question now becomes, what are they doing with all of that data and why aren't they writing queries to summarize the data? It could be they're doing things with PowerPivot and the like to aggregate data and it would probably suit them better if you did review their queries, what their goals actually are, and provide better queries that take parameters instead of letting them write their own.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply