SQL interraction with VS

[Tsotne]

Hello!

I'm interested in common use of SQL in calculations handled in VS. I know SQL (still studding hough). How does it help decrease processing time in VS. For example when having millions of data stored in a static table in database and you need to perform any calculation on it like finding exponents of each number, how do you do it? Import the data into VS and allocate array and then manipulate the data in a way you want, or calculate the exponents in SQL and then import the answer? And such kind of things... I really wonder the use of SQL in a MATHEMATICAL WAY. Please atate your experiences, opinions, suggestions, intentions regarding the use of databases in a quantitative side. Thanks

 

[kirylm]

Hello!

I'm interested in common use of SQL in calculations handled in VS.

VS = Visual Studio?

I know SQL (still studding hough). How does it help decrease processing time in VS.

It depends on what system you are using, row or column store or even some specialized database for storing tick data. I never worked with MS database (SQL Server), therefore can't comment on it. I doubt it will decrease your processing time though, more likely to increase. ... again, it depends on what you are doing and how.

For example when having millions of data stored in a static table in database and you need to perform any calculation on it like finding exponents of each number, how do you do it? Import the data into VS and allocate array and then manipulate the data in a way you want, or calculate the exponents in SQL and then import the answer?

I would do

select log(number) from table

or something of that nature

And such kind of things... I really wonder the use of SQL in a MATHEMATICAL WAY. Please atate your experiences, opinions, suggestions, intentions regarding the use of databases in a quantitative side. Thanks

this is too broad, you need to ask more specific questions

 

[Tsotne]

Thanks for your reply. Yes VS = visual studio. I need broad opinions not specific ones. In what sense MS SQL is used. How for example statisticians use them. For instance, while having a numerical data and want to fit a distribution, do you just go to excel and start fitting with chi square method or in some kind use SQL?!

 

[alain]

How for example statisticians use them. For instance, while having a numerical data and want to fit a distribution, do you just go to excel and start fitting with chi square method or in some kind use SQL?!

Use R and MySQL. There is a ODBC package that could interface to ODBC from R. That's how statisticians that use R, use SQL Server.

 

[Tsotne]

Thanks alain. I'm not R user. I'm more with C#/C++

 

[alain]

Statisticians don't use C#/C++. Actually, it's hard to find scientists coding in low level languages now-a-days. Even more, it's really hard to find scientists coding in "paid for" systems.

This is my anecdotal evidence, I might be completely wrong. The problem is that budgets are better spent in anything but software so PhD (of all fields) code a lot in Linux using open source tools (C++ as in gcc in linux too but no MSFT).

 

[Tsotne]

Yes but R is focused in statistics, C based languages offer better flexibility. I'm writing a copula functions in C# right now. Custom made everything is always good.

 

[atreides]

Yes but R is focused in statistics, C based languages offer better flexibility. I'm writing a copula functions in C# right now. Custom made everything is always good.

If you are familiar with python, there is a mysql db interface that gives you access to mysql. Also you connect to C/C++/R from python

 

[alain]

Yes but R is focused in statistics, C based languages offer better flexibility. I'm writing a copula functions in C# right now. Custom made everything is always good.

I have to disappoint you. Yes C is a more flexible language but for rapid prototyping and stats/math related problems, you won't beat R with C#... and don't tell is because C# is faster. BTW, there is already a copula package in R:

http://cran.r-project.org/web/packages/copula/index.html

Also, the Rmetrics package has a set of copula implementations directly related to R:

http://cran.r-project.org/web/packages/fCopulae/index.html

 

[Tsotne]

I have to disappoint you. Yes C is a more flexible language but for rapid prototyping and stats/math related problems, you won't beat R with C#... and don't tell is because C# is faster. BTW, there is already a copula package in R:

http://cran.r-project.org/web/packages/copula/index.html

Also, the Rmetrics package has a set of copula implementations directly related to R:

http://cran.r-project.org/web/packages/fCopulae/index.html

Thanks a lot alain for the resources. You are right, but I can myself construct my custom made copula package in C++/C# and I'm not saying it'll be faster than already utilized package, but I'll do it and it'l be very interesting. I have already done one dimensional distributions in C#.

 

[Tsotne]

If you are familiar with python, there is a mysql db interface that gives you access to mysql. Also you connect to C/C++/R from python

I know it yes. I'm familiar with the Data namespace as a whole.

 

[Tsotne]

Statisticians don't use C#/C++. Actually, it's hard to find scientists coding in low level languages now-a-days. Even more, it's really hard to find scientists coding in "paid for" systems.

This is my anecdotal evidence, I might be completely wrong. The problem is that budgets are better spent in anything but software so PhD (of all fields) code a lot in Linux using open source tools (C++ as in gcc in linux too but no MSFT).

Statisticians don't use C#/C++ means that they already have optimized packages focused on statistics alone and they find it easy to "live in their own environment" rather than having to engage in very general languages. But again, for custom programming concept, I personally prefer having made my own unique software and do whatever I want there rather than havin standardized one. Flexibility is most precious thing. I have had experience with many standardized softwares and while I'm working in my own made one, I find it much comfortable. All in all, I occationally encounter errors shich I'm not sure in those packages how to handle. In my programs I know what the cause might be. I'll finish that statistical program including 1-2-3 dimensional distribution functions and upload and let's see if it'll be likable.

 

[nielsb]

Hello!

I'm interested in common use of SQL in calculations handled in VS. I know SQL (still studding hough). How does it help decrease processing time in VS. For example when having millions of data stored in a static table in database and you need to perform any calculation on it like finding exponents of each number, how do you do it? Import the data into VS and allocate array and then manipulate the data in a way you want, or calculate the exponents in SQL and then import the answer? And such kind of things... I really wonder the use of SQL in a MATHEMATICAL WAY. Please atate your experiences, opinions, suggestions, intentions regarding the use of databases in a quantitative side. Thanks

As with everything when it comes to databases; the answer is: "it depends"
But, generally speaking:
1. SQL (MSSQL, MySQL, Oracle, Sybase, etc) is good at set based operations; finding, updating data. It is not optimised for maths.
2. SQL (the language - T-SQL, PL/SQL, etc) is an interpreted language, i.e. it is not compiled to machine code.
3. So if you need to loop data to do calc's on individual records in a result-set, then it is probably preferred to do it somewhere else.
3.1. However, MSSQL (and I believe Oracle and Sybase), allows you to create extended stored procedures that are written in C++ (i.e. machine compiled code) that runs inside the database engine. This would allow you to process the data inside the database engine and still gain the benefit of compiled code.
3.2. MSSQL (since SQL Server 2005) allows you to - in addition to extended proc's - also write .NET code running inside the engine. As .NET code is compiled, you will be getting the perf benefits of machine compiled code.

I guess what this leads up to is that in most cases, it is deferrable to do heavy-duty math calculations in something else than SQL (the language). The decision if you should do it inside the db (through extended proc's or .NET) or take the data out, is more a question of how heavy load the db is under, how much data you are shipping around, etc.

Hope this helps.

Niels

 

[Tsotne]

Thanks a lot nieslb. That's really an useful and interesting information.

 

[euroazn]

Agree with nieslb.
In fact, my last internship precisely required SQL calls from VS (I used C#, but obviously it doesn't really matter.)
I found that it was optimal to calculate stuff in the C# program and let the SQL calls just find data.

 

[alain]

IMHO, Stored procedures are one of the worst things that ever happened to application development. Pushing business logic to the database layer is wrong wrong wrong.

 

[nielsb]

IMHO, Stored procedures are one of the worst things that ever happened to application development. Pushing business logic to the database layer is wrong wrong wrong.

Stored procedures != business logic!! Yes, it can be; but categorically stating that it is, is wrong, wrong, wrong

Anyway, I have done db / app dev for too long to get into "religious" debates about sp's vs statments, unix vs. linux vs. mac vs. windows etc. We chose whatever tool suits the requirement the best.

Niels

 

[kirylm]

IMHO, Stored procedures are one of the worst things that ever happened to application development. Pushing business logic to the database layer is wrong wrong wrong.

I would disagree. It may be true to some extent for OLTP databases, but for the row-oriented data warehouses stored procedures are still the fastest way to code the ETLs. It really depends what your needs are.

 

[Tsotne]

IMHO, Stored procedures are one of the worst things that ever happened to application development. Pushing business logic to the database layer is wrong wrong wrong.

This is not true. Storing business logic into database layer is sometimes useful if not always but depends on developer preferences. I sometimes simply wish not to have a PL overwhelmed by business logic and want to have stored them in a static memory holding like database. It is an useful feature.

 

[alain]

Storing business logic into database layer is sometimes useful...

IMHO, IT IS NEVER USEFUL!!! It will lead to headaches down the path.