Press "Enter" to skip to content

Day: April 20, 2018

TensorFlow Lite

Published 2018-04-20 by Kevin Feasel

Laurence Maroney explains TensorFlow Lite:

TensorFlow Lite is TensorFlow’s lightweight solution for mobile and embedded devices. It enables on-device machine learning inference with low latency and a small binary size. TensorFlow Lite also supports hardware acceleration with the Android Neural Networks API.

It’s designed to be low-latency, with optimized kernels for mobile apps, pre-fused activations and much more. It’s also *really* easy to use, and there’s a great demo app that will get you up and running with image classification from the device camera on both Android and iOS.

It comes in two parts:

  • A set of tools that you can use to prepare your models for use on mobile. These let you freeze your model to make it smaller, and then optimize and convert it in a process also called flattening the model, so that it will run happily on mobile

  • A mobile runtime with an easy API that lets you pass data to the model and get classifications back.

You don’t build the neural network on a phone, but the fact that you can run one on your phone is pretty crazy.

Comments closed

Issues Starting ML Services

Published 2018-04-20 by Kevin Feasel

Jen Stirrup has a quick rundown of some reasons why Machine Learning Services might give you an error when you try to start it up:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

Msg 11536, Level 16, State 1, Line 4

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Grr! What’s happened here? We had installed R as part of the SQL installation, and we had run the command to enable it, too.

Click through for reasons.  One thing which might affect a small percentage of people is that ML Services creates a folder each time you run an R query.  Those folders are easy to clean up, and each time the Launchpad service starts up, it deletes the old folders as a step.  The problem is that if you have a huge number (tens or hundreds of thousands), it might not get finished deleting in time and the service will fail.  Deleting the folders manually does the trick and the service can start up once more.

Comments closed

Window Functions In WHERE Clauses

Published 2018-04-20 by Kevin Feasel

Shane O’Neill covers an annoying but necessary thing to remember around window functions:

Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId.

They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves

Wow! This is great! I can do this with Windows Functions!

They also work for a company that has invested in RedGate’s SQL Prompt so they know that they can rely on SQL Prompt to help iron out any inconsistencies in their script.

So they take the SELECT script above and type in WHERE… and the auto complete pops up

And that popup isn’t quite accurate…  Shane covers this in the guise of a SQL Prompt bug, but it’s a good thing to remember regardless of which tooling you use.

Comments closed

Recently Added String Functions

Published 2018-04-20 by Kevin Feasel

Lori Brown covers a few string functions added to SQL Server in the past two versions:

STRING_ESCAPE (https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql)

This function is available starting with SQL 2016 and is currently only able to escape JSON characters. To me it’s not super useful just yet but hopefully they will add more types soon.

I haven’t had the need to use STRING_ESCAPE yet, but one additional function I’d add is CONCAT_WS.

Comments closed

Nested Loops And Implicit Reordering

Published 2018-04-20 by Kevin Feasel

Dmitry Piliugin shows how the SQL Server optimizer can end up reordering data in a nested loops join to improve performance:

The purpose is to minimize random access impact. If we perform an Index Seek (with a partial scan, probably) we read the entries in the index order, in our case, in the order of CustomerID, which is clearly seen on the first result set. The index on CustomerID does not cover our query, so we have to ask the clustered index for the column SomeData, and actually, we perform one another seek, seeking by the SalesOrderID column. This is a random seek, so what if, before searching by the SalesOrderID we will sort by that key, and then issue an ordered sequence of Index Seeks, turning the random acces into the sequential one, wouldn’t it be more effective?

Yes, it would in some cases, and that is what “optimized” property tells us about. However, we remember, that it is not necessarily leads to the real reordering. As for comparing the real impact, I will refer you to the actual Craig’s post or leave it as a homework.

Read the whole thing.  This is one reason why it’s important to emphasize that in SQL, you can only assume order if you have an explicit ORDER BY clause.

Comments closed

Finding Overlapping Data Ranges

Published 2018-04-20 by Kevin Feasel

Louis Davidson shows how to find groups of data which overlap:

This week, I had a problem where I needed to find and eliminate from the results of my query, data with overlapping ranges. I have written about this topic before, in my database design book book, in regards to building a trigger to avoid overlapping ranges. But even though I have written on the topic there, I still use Google just like you to get quick help (I use books when I want to learn, or expand my knowledge on a topic in depth, blogs when I need a simple answer to a simple or complex question.)

The problem at hand is most often associated with date based data, such as effective dates for a row in a temporal/type 2 dimension table, or other cases like appointment times, etc. But the algorithm is the same with numbers and is a bit easier to read since we don’t have the same issues with roundoff and decimal places (the query is complex enough on its own to show in a blog post). From a progression of start and end values in each row, we are going to look at how to check to make sure that there are no two rows that are in conflict (no range should contain another ranges value at all).

This feels like the type of thing which could be rewritten with window function to be a little smoother, but I’d have to think about it more.  Louis does provide a good solution and explanation to a fairly common but tricky problem in T-SQL.

Comments closed

Upgrading SQL Server 2017 Standard Edition

Published 2018-04-20 by Kevin Feasel

Jo Douglass hits an error when upgrading to SQL Server 2017 on Standard Edition:

A quick one to signal boost this issue and its solution, as I’m sure other people will run into it. If you’re on Standard Edition of SQL Server and upgrading to 2017, you might run into an issue where the database services portion of the upgrade fails. This seems to be related to SSIS.

If you experience this problem, mid-way through the upgrade you’ll receive this error in a pop-up:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

At the end of the upgrade, it will show that the database services section has failed. Checking the error log will show this:

Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 917, state 1, severity 15.

Read on for the answer and a workaround.

Comments closed