 | A community of more than 1,600,000 database professionals and growing |
| | | Array Element by Agonizing Array Element Today we have a guest editorial from Phil Factor as Steve is out of the office. If you are using PowerShell, you’ll often wonder why it is that one uses strange devices such as pipes to process arrays. Anyone who has been weaned on procedural languages such as Java, Basic, C or C# will reach for an iterative loop without thinking too hard. This is because a compiled language is extraordinarily fast at iterating. Anyone who knows what RBAR is will know that SQL being interpreted, is pretty poor at anything that requires iteration. PowerShell, being essentially an interpreted scripting language, is very poor at the iterative loop as well. By ‘very poor’, we are talking about ten minutes in PowerShell as opposed to six seconds in C#. Why is this? If a loop iterates more than sixteen times, the code of the loop then is compiled dynamically as .NET code and the dynamic method is then invoked inside the loop. Unfortunately, .NET needs to run a security check on the stack, which slows the loop down. There are four ways I know of by which you can cope with this. You can sulk, and go back to using C# or Python, you can create compiled PowerShell functions that use C#, you can use LINQ functions in PowerShell, or you can use the native language constructs that PowerShell provides, such as the pipe. The pipe is not entirely pain-free. It is always best to filter as much of the data as you can at the start of the pipeline. However, it is an obvious idiom that is familiar to ops guys who are used to DOS and Bash scripting. LINQ functions can also be very handy for array-handling tasks. PowerShell and SQL are similar in that, if you use them the way that they are intended, they are fast and efficient, but if you can’t change the coding patterns that you learned from BASIC, Java or C, you will wonder at how slow they are. It is always possible to improve the performance of iterative code: Firstly, use the measure-command cmdlet to get a feel for what works best. Then make sure that there isn’t a better way that is provided to do the task. For example, initialising an array to a zero, you can try … $a = ,0 * $length ..or… $a = @(); for ($i = 0; $i -lt $length; $i++) { $a += 0 } .. and you will be amazed by the difference in execution time simply by avoiding having to iterate in PowerShell. Phil Factor from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
|
| | ADVERTISEMENT |  | | The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial. |
|  | | State of Database DevOps Report Learn how teams are extending DevOps practices to SQL Server databases. Get the report. |
|
|
|
| | |  | Steve Thompson from SQLServerCentral.com One firm's journey on the road to mastering (or at least attempting) continuous database integration. More » |
 | Press Release from Redgate When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. More » |
 | Additional Articles from SQLPerformance.com Paul White takes you on an optimizer journey, exploring how SQL Server comes up with cardinality estimates for COUNT queries. More » |
 | Additional Articles from Redgate Visual Studio Code is rapidly gaining in popularity, but is it all it could be, or is there room for improvement? Redgate is embarking on some research to better understand how you are using this lightweight editor, and where it can be improved. Have your say by filling in this short survey! More » |
 | Kenneth Fisher from SQLServerCentral Blogs tl;dr; If you are SWITCHing data into a table and the partitioning column is nullable you will need to add... More » |
| | Ed Leighton-Dick from SQLServerCentral Blogs So you’re using encryption in SQL Server, but you’ve discovered that the expiration date of a certificate is expiring. What... More » |
|
|
| | | Today's Question (by Steve Jones): I'm working in R, but I can't remember how a function works. How can I get help quickly within a language editor for a function such as sqrt? |
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: R Language. We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center. |
|
|
| |
| ADVERTISEMENT | | Protect your data from attack by using SQL Server technologies to implement a defense-in-depth strategy, performing threat analysis, and encrypting sensitive data as a last line of defense against compromise. The multi-layered approach in this book helps ensure that a single breach doesn't lead to loss or compromise of your data that is confidential and important to the business. Get your copy from Amazon today. |  |
|
|
|
|
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (by Henrico Bekker): Editor: I'm not really sure how to reword this. The question and structure doesn't really make sense. What will the outcome be when executing the below batch? USE [myDB] GO CREATE PROC [dbo].[usp_my_test_proc] as EXEC master..xp_CMDShell '"F:\Folder\Scripts\clearfoldercontent.bat"' GO exec [dbo].[usp_delete_validation] exec [dbo].[usp_export_Data] GO
Answer: The stored procedure "[dbo].[usp_my_test_proc]" will be created with only the xp_cmdshell command execution, and both "usp_delete_validation" and "usp_export_data" procedures will run individually outside [dbo].[usp_my_test_proc]. Explanation: GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities, and as a result the script will create the [dbo].[usp_my_test_proc] with only the XPCMDShell command. The GO separator sets both "usp_delete_validation" & "usp_export_data" outside the top procedure creation batch and will execute as individual procedures outside the [dbo].[usp_my_test_proc] creation.
» Discuss this question and answer on the forums |
|
|
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums.
Incremental load from on-prem OLTP to Azure Data Warehouse - Hello Folks, I was looking for an option to load tables(100+) from a busy OLTP instance to Azure Data Warehouse. The...
Advice on creating unique clustered index - I've been studying the possibility of indexing a particular view that takes 26 minutes to run, trying to optimize it. ...
DB size grown from 180 GB to 340GB in 3 months. Wants to bring back to 200GB DB size. - Hi, The SQL Server version 2012. It's Live Server with 50+ users and other process. Issue: DB size grown from 180 GB to... To Delete / Truncate / Drop & Create - Good Morning All, Can I Have some opinions please. Have 2 large tables I need to 'clean', 1 with 265263333 rows and one... SSIS Flat File Mapping - DATETIME Conversion Error - Hi I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00). I have defined this column "database timestamp... SSIS ForEach Loop Container recursion question - I have a weird one today. Because my destination system can only accept files of a certain size, I need... Why would SQL throw a Severity 016 alert?? following a successful db restore? - Hi all, Does anyone know why would SQL throwing a Severity 016 alert following a successful db restore, and what I...
DATEDIFF using integer keys - I've always wondered why you can't use datediff with two dates that are in int format. When I run select datediff(day,... Heap vs Clustered Wildcard Search - I am working on converting a heap table to a clustered table by adding a primary key to the identity... Sql Header and Then record output I need customer header line and then the record line can I do this with just SQL??? - I have two queries the first one is for the header information and the second is for the record information. I... Help On Calculating Aging - create
Unexpected result with Absolute Values (Updated with code to create test data) - ***EDIT: Updated with sample data and code to create the sample tables.*** I have a CASE statement that returns a different...
SQL QUERY Rows where TotalScore=@TotalScore in the range of value by using group by Date and Time - Dear Expert, I am seeking your help . here is the scenario , please see the Figure -1 I have 3 parameters .1....
Perfmon instance of the selected object - Hello, We have a 2 node SQL 2008 Cluster environment. I'm trying to firm up resource requirements for each instance (4)... Memory consumption- Windows or SQL Server - Good morning Experts, How to find whether Windows is consuming high memory or SQL Server is consuming high memory
SSIS 2012 .ispac deploy fails - Error load from XML - I must be missing something obvious. In SSIS 2012, I've got a new SSIS project with ProtectionLevel property set to DontSaveSensitive... Grief importing date from Excel 2010 to SQL Server 2012, SSIS, Derived column task. - Hello, First of all, I'm an SSIS Noob, and not great with Excel either, someone else created and imported the data...
Basic question about SQL Server permissions for users - Hello, I am on a bit of a learning curver and started looking at the security issues in my databases.... Database Health Report - I would like to generate a database health report that includes a dashboard summary and then some spreadsheets of performance...
How does one select EVERY Nth row from a table? - Hi Friends, I want to retrive each nth row from a table in sql server 2000. Example: if i give n as 5 then 5,10,15,20,.............. |
|
| This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
| This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
| This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com |
|
|