mysql database code sample

[ctrlbrk]

decus, a very creative example!

Ok all.. another update.

I am now successfully storing and retrieving info about my trades, along with the tick data. On this other thread you can see my "big picture/plan":

http://www.ninjatrader-support2.com/vb/showthread.php?p=87127

I've attached my version of the Indicator provided by auspiv. Basically the only changes are insert DELAYED to increase performance (imo), and the parameter for the name of the table (instrument) so you can use one single indicator.cs file and just change the value if you want to record multiple instruments, like I am.

Check out the "big picture" thread when you can...

Once again, thanks auspiv! I've stored 8 million ticks and counting lol

Mike

[NinjaTrader_Dierk]

Guys, since you're struggling with selecting the "right" DB technology here is more info about NT7: NT7 no longer will use the jet engine (MS Access DB) but the MS SQL Server Compact edition.

We will provide details as we roll first beta of NT7.

[roonius]

Well been working on this off/on tonight. First, I am playing with changing the INSERT to INSERT DELAYED. With my config it seemed to have helped smooth the burden/load. Since we don't care about immediate access, delayed is fine. Still playing with this, as if delayed isn't required then it is actually more costly in the long run.

I also modified the .cs code a bit to add a Parameter so when you add the indicator to the list on the chart, you can specify the ticker symbol for the mysql table. This is better than one indicator per instrument, especially when making code changes

I think the next step is optimizing the connections. Opening a new connection ever OnMarketDepth is expensive I think. Should be able to open a connection, specify a timeout period, and leave it open unless there is a timeout (like during overnight). My first stab at this failed and ended up opening a few hundred connections on mysql box. I will need to try again

I've got about 1 million ticks in the db so far, and it's averaging almost 100 per second on the db. phpmyadmin is complaining about one thing which I need to research further. Each table as a PRIMARY auto_increment already:

Handler_read_rnd_next 9,131

The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Mike

Mike,

I would sugest to play with DataSet.
Maybe write the row into DataSet instead directly to DB and update DataSet to DB when it reaches 1000 rows? 2000 rows?

[ctrlbrk]

Mike,

I would sugest to play with DataSet.
Maybe write the row into DataSet instead directly to DB and update DataSet to DB when it reaches 1000 rows? 2000 rows?

roonius,

Sounds like a good idea, but I have no clue how to do it. I am a "self taught" programmer, so I've only learned as much as I've needed too I will check into it.

Mike

[ctrlbrk]

Guys, since you're struggling with selecting the "right" DB technology here is more info about NT7: NT7 no longer will use the jet engine (MS Access DB) but the MS SQL Server Compact edition.

We will provide details as we roll first beta of NT7.

Great news.

[kuhasu]

Guys, since you're struggling with selecting the "right" DB technology here is more info about NT7: NT7 no longer will use the jet engine (MS Access DB) but the MS SQL Server Compact edition.

We will provide details as we roll first beta of NT7.

Better.Thank you for your efforts.

[ctrlbrk]

I've had some issues. It seems occasionally my sql server will time out due to load, and the script just fails. It doesn't just drop that tick, or a few ticks, but it stops working until it is reloaded.

I'm trying to figure out how to get past this... it only has happened twice in the last two weeks.

Mike

[javaj]

I've had some issues. It seems occasionally my sql server will time out due to load, and the script just fails. It doesn't just drop that tick, or a few ticks, but it stops working until it is reloaded.

I'm trying to figure out how to get past this... it only has happened twice in the last two weeks.

Mike

I'm sure your probably aware of this ctrlbrk, but I thought I would post the link if it would help...

http://dev.mysql.com/doc/refman/6.0/en/optimization.html

On another note, does anyone know if the MS SQL Server Compact edition have the same ability to be connected by a third party app like SQL express? Or is it only usable by the application its packaged with? I was thinking I would like to build a web app but not sure if it would be as accessible like a standard SQL server. Guess I need to do some searching.

Thanks...

[ctrlbrk]

javaj,

Thanks for the link, some good reading there.

However, I fixed this issue within the NT code itself by doing two things:

a) Setting chart size to 100 ticks
b) Appending the insert string to one another (insertstring += insertquery)

Then I just moved the connection code to OnBarUpdate, so connections only take place once per 100 ticks, and it writes 100 "INSERT"'s at once.

However..

There is still a bug in the code somewhere, I do occasionally get OnMarketDepth index cannot be 0 or negative number errors. I don't have it right in front of me, but it sucks because NT doesn't just auto-resume the strategy, it waits for a manual restart.

Mike

[sefstrat]

b) Appending the insert string to one another (insertstring += insertquery)

Another optimization you may consider is using StringBuilder class (part of .NET framework) to build your string rather than concatenating directly.

Here is a blog post which describes StringBuilder and why its performance is much better than regular string operations.

http://www.switchonthecode.com/tutorials/csharp-snippet-tutorial-using-stringbuilder

[kuhasu]

Mike,

I would sugest to play with DataSet.
Maybe write the row into DataSet instead directly to DB and update DataSet to DB when it reaches 1000 rows? 2000 rows?

I agree with you,'cause lots of traders are using Ramdisk too.
But I think there should be some settings can let us configure the Size of the DataSet or the intervals to update the ms sql database or the database which supports SQL.

Anyway,very good news! Cheers!

[ryker]

I'm not sure if this could help but would it not be better to open the mysql connection once and close it when you're done (so open on bar 0 when you load the indicator and close it in the dispose method?

[iwannatoscript]

Hi guys,

i'd like understand if by NT7 do we need a own made database?

NT7, could i connect in a easy way db of ninja to excel,

or ninja db to (base or access) to (calc or excell)?

I'd like have both hystorical and real time data.

Now i have a excel db, the dde give me real time data,

is it possible to take it directly from ninja?

Why market replay to record data must stay on? it could not take from

database on ninja and replay it?

About backtesting

Could be by NT7 the possibility to backtest the data by market replay?
Should it be like in we are in a real market, real trading?

Market replay store the data in another db? does ninja has 2 db?

:-)

[NinjaTrader_JoshP]

iwannatoscript,

NinjaTrader 7 comes with its own database. If you want extensions to your own databases you can do so through more advanced C# programming. Unfortunately that is beyond the level of support we can offer though. Thank you for understanding.

Market Replay is saved in its own individual files for each day. You can run forward tests on the Market Replay and it will provide results you can look at after you have run through the day you want.

[iwannatoscript]

iwannatoscript,

NinjaTrader 7 comes with its own database. If you want extensions to your own databases you can do so through more advanced C# programming. Unfortunately that is beyond the level of support we can offer though. Thank you for understanding.

Market Replay is saved in its own individual files for each day. You can run forward tests on the Market Replay and it will provide results you can look at after you have run through the day you want.

thanks by quicly answer

So i am beginner to ninja.
So the more easy and good thing to archive all tick data and after export them what is?

Only day for one day market replay and no backtest?

Is there a solution to export in real time data from ninja to excel?