• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

SQL interraction with VS

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
 
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
C++:
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
 
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

Older and Wiser
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.
 

alain

Older and Wiser
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).
 

alain

Older and Wiser
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
 
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#.
 
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.
 
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
 
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.
 
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
 
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.
 
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.
 
Top