Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

A Neural Network in SQL Server Expand / Collapse
Author
Message
Posted Wednesday, October 28, 2009 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 5, 2009 10:04 AM
Points: 2, Visits: 6
Silvia,
perhaps could be more simple to predice the mm of rain really fallen in the next day.
And set Pop=1 if the mm was grater than 0.

Wait to read more of your work.

Giuseppe
Post #809871
Posted Thursday, November 5, 2009 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 5:00 AM
Points: 2, Visits: 12
I Love C, C#, but often write > 50% of application code in SQL, because it is the fastest way to manipulate data, period. If the data can be calculated within the database using SQL, rather than having to retrieve it, you have a magnitude less of network I/O, which is typically a large bottleneck for an application.

'If' you are going to persist your Neural Net state in the database with every calculation, using SQL might be the fastest NeuralNet implementation, due to minimal network I/O. Not saying that is the best approach, just saying it should be considered.



Post #814240
Posted Thursday, November 5, 2009 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 5, 2012 12:42 PM
Points: 8, Visits: 17
Excellent article. It's a nice break from "standard" languages, even though that's how I get the most money.

There's a quote from Celko which (paraphrased) to be good with SQL is to forget how to program.

I figure that's almost like what XSLT 1.0 (which I miss because it would separate the men from the boys) was for a lot of people: you can't look at it procedurally.

Is there a better version of the "Analysis Services" diagram?

I cranked the resolution to practically nothing and used my reading glasses to no joy.

Post #814279
Posted Thursday, December 3, 2009 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 2, 2012 1:18 AM
Points: 32, Visits: 89
Very good article, thank you!

About the classification matrix: am I reading it wrong or is there an error in the explanation?

Also, there were 243 cases when the network predicted that it was going to rain and it didn't rain (a false negative)


Shouldn't it be:
Also, there were 243 cases when the network predicted that it was NOT going to rain and it DID rain (a false negative)

Post #828111
Posted Thursday, November 10, 2011 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 9:49 AM
Points: 5, Visits: 91
I've lurked too much - gleaned much from SQLCentral but pretty much keep quite. I was searching for Neural Nets (NN) using a SQL system for main processing and was pleasantly surprised.

Loved the article, sorry to resurrect a thread here, but I've been working on a neural net in sql, but using a simple feed forward where layer 0 is the input and layer n+1 is the output. The whole network could be expressed in 2 tables, one for the neurons, the other for the links between neurons. The Neuron table keeps state from the last feed-forward iteration, a sigmoid shape bias, and a threshold. The NeuralLink table links the neurons and holds a weight per neuron.

I was trying to think in sets when constructing the model. Each neuron will know the layer it is in and by anchoring on the neurons from N1 to Nx, each set of connections are called in and the connected neuron weights are sum'd and saved per neuron.

The problem is - I've so far been unable to unravel the loop to support a recursive CTE because a summation will need to occur before moving onto the next layer. I have to use a while loop to iterate through the layers. (from 1 to x - forward fashion). Essentially, sequential processing must occur at the time being.

The back propagation routine for learning is just done in reverse, Nx to N1

The training data and corresponding expected correct responses can be added into 2 tables and the relational nature immediately allows a pathway for easy training using sets.

This system may work surprisingly well. Usually the layers aren't the issue, its the sheer number of interconnections. If I can take advantage of proper indexing and set based procedures, the SQL solution with large numbers of neurons may end up working because SQL is tuned to working with large amounts of relational data better than my C# code. The Network and the processing code looks a lot less complex, since most of the programming is spent re-building RI into the objects needed to support NN (Neural Nets). I've attempted to make the Neural Net a data processing set system.

To make use of it, update and populate the input layer and call the stored procedure telling it what neural net you want to process. Read output from the last layer in the network for your answers.

Due to the rational layout, several neural networks could be created sharing the sample inputs and outputs. Support to deposit the output data per network with epoch encoding would allow for several networks to process the same input data, potentially allowing for some interesting parallelism.

A Neural Net on SQL would still be quite intensive - albeit predicatively spikey. Its been a fun project. Getting the back-prob to work without a functional calculus background has been tricky.

I work for a soil and water testing lab, and develop databases, web services, intra-business programs (LIMS , analysis, reporting, etc) and other software development. I've made for the company a multiple pH probe system that collects acquired data and determines when the probes are stable and controls other auto-processing aspects.

I have 120 million or so datapoints collected (ph behaviors) and would like to see if I can unleash a neural net on this data and see what kind of stuff would come up.

Some thoughts would be see if there could be a minimal number of samples to predict the end point pH, or determine when something is sampled wrong etc.

Mainly its to expand more useless knowledge :D
Interesting to see others messing around with NN in SQL.

Thanks,
Dave

Post #1203789
Posted Monday, November 14, 2011 12:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:11 AM
Points: 6, Visits: 109
Thanks Dave for the ellaboration of all your experiences with a subject similar to mine. I also did it just for fun and let me tell you it certainly was a lot of fun and experience with coding.
I do have still some pending stuff left on this one and that is a generalization of the model I did. Have it on standby due to time constraints but will revisit this one in the near future
Post #1205509
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse