While i feel like the generic answer is “consultants” I wonder, are there any services for refining your sql/sas script to run faster?
Say i create a script and because of urgency i don’t have the time to make it properly elegant, but enough time to get what i want in the format i want.
Are there services where you can say, sign a NDA, send your completed script as is, have them clean it up , have them send it back.
To me this could be great, because they wouldn’t necessarily need actual access to your databases, if they could just polish the script as is. I feel like that would be a value creating service. Could charge by the line of code or character count or something + a minimum fee.
Why not just hire someone with that skill set to begin with?
I’ve found that there is more value in developing such code having an understanding of the underlying database and what the fields are intended to capture, and what they actually contain.
Part of the issue with optimizing SQL is the performance depends on the data as well as the code. Just from the brief searching I have done (sometimes in response to your questions - thanks!) the answer to what “code” is best is often “it depends”. I don’t think you will be able to get a turnkey “if these are the tables, and this is what I want, what is the fastest code” answer.
How to hire a SQL consultant? Not sure. There is a bit of gulf at my workplace between the dedicated SQL resources, who do stuff for production operations, and the actuarial teams, who generally write their own stuff. I wish I could dump off the “get me this data” tasks to a pro, but it is either me, or student actuaries. There are a couple of actuaries who are quite skilled “amateurs” I hit up with questions, so it generally works out.
My general SQL solution: create the SQL to get the data into a better platform to do the rest of the data manipulation. SAS and Python are far better than “raw SQL” . . . and far, far easier to track to understand what’s going on.
Especially if you want to use a calculated field derived from other fields in the data.
I thought I was good at SQL, until I would go and ask the DBA’s a question. They were the pros, and would usually look over code for anyone they had a good relationship with. So if you’re with a carrier, I recommend making friends with the DBAs.
I don’t think it’s a good idea, given the situation. You cite urgency which indicates to me that whatever is bogging you down is the result of a long, cumulative effect of management mistakes that there might not be a quick fix for. There are many things that can slow down SQL queries, including
Poor design of tables
Server overload
Poor data center design
Poor choice of hardware
Lack of good DDL constraints, keys, etc
Actually bad code
Chronic underhiring and cumulative succession problems
Poor allocation of employee time
Not sure if hiring a consultant for $40,000 just to fix your script only to find out there wasn’t a deeper problem (there probably is) with the organization. Also there’s little chance they can help unless you give them a copy of the EER or schema diagram.