SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Neural Network in SQL Server


A Neural Network in SQL Server

Author
Message
g.ciccotti
g.ciccotti
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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
dalewilbanks
dalewilbanks
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 16
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.



Phil Paxton
Phil Paxton
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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.
timo-947179
timo-947179
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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)
dray
dray
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 BigGrin
Interesting to see others messing around with NN in SQL.

Thanks,
Dave
Silvia Cobialca
Silvia Cobialca
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 112
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search