We are bringing in constituent data from an SQL database using the following query. With a stock list of approximately 3,000, a simple exploration (just indGlobal = 1) using this takes around 15 minutes. The constituent database only contains entries for each stock on a monthly basis. May this be hurting performance? Would it be better if the database had entries for each day?
Are there any other suggestions you might have for speeding up performance? Note: prices are being read from a local database.
indGlobal =odbcGetArraySQL("Select [Global], [Date] FROM Constituents.dbo.v_Global Inner Join StockInfo.dbo.StockInfo ON v_Global.BBID = StockInfo.BBID WHERE StockInfo.Symbol ='"+Name()+"' ORDER BY Date ASC");
You probably need to generate at least one index on the database.
try script it from the sql server side - much faster.
SQL performance is limited by SQL server and nothing else.
For amazing performance, throw away your SQL and use only AmiBroker native database which is faster than anything else.
Index constituents should be placed in watch lists and then you can use hyper-fast InWatchList functions.
No need for external SQL servers.
Historical index constituents require time series data on a daily time frame.
The universe of historical index constituents is best implemented by a watchlist.
Yes that is what I meant - a set of watch lists representing index constituents on for example monthly basis (depending on how frequently given index is reconstructed) and using InWatchList / InWatchListName together with Year()/Month() to get constituents corresponding to given month/year.