high-speed insert

3 Feb 2006 5:13 PM

Larry

I have been using an OODB as the repository for a stock-quote ticker plant.
I would love to be able to convert this to SQL Server.

Quotes come in at a nominal rate of 10K per second with peaks up to 60K.
The quote is fairly basic data: time, price, volume, ExchangeID - leaving out
the minor variations. I already have this data in a write cache in C#, so
the data could be cached per stock symbol. While this data is flying in, I
also will be querying a bit heavily also.

1) Is this even thinkable with SQL Server 2005 Express, Standard or
Enterprise?

2) I haven't had much luck with good ole Google in finding some direction.
I'm obviously not a SQLServer expert, but given a pointer towards the write
technology and methods, I might have hope.

3) There is a major Stock Trading Technology company that says they use SQL
Server 2005 for this purpose. Of course, they don't describe the methods.
This is at
http://members.microsoft.com/CustomerEvidence/Search/EvidenceDetails.aspx?EvidenceID=4641&LanguageID=1.

Thanks for any help you might provide

3 Feb 2006 11:29 PM

David Gugick

Larry wrote:
Show quote

> I have been using an OODB as the repository for a stock-quote ticker
> plant.
> I would love to be able to convert this to SQL Server.
>
> Quotes come in at a nominal rate of 10K per second with peaks up to
> 60K.
> The quote is fairly basic data: time, price, volume, ExchangeID -
> leaving out the minor variations. I already have this data in a
> write cache in C#, so the data could be cached per stock symbol.
> While this data is flying in, I also will be querying a bit heavily
> also.
>
> 1) Is this even thinkable with SQL Server 2005 Express, Standard or
> Enterprise?
>
> 2) I haven't had much luck with good ole Google in finding some
> direction. I'm obviously not a SQLServer expert, but given a pointer
> towards the write technology and methods, I might have hope.
>
> 3) There is a major Stock Trading Technology company that says they
> use SQL Server 2005 for this purpose. Of course, they don't describe
> the methods. This is at
> http://members.microsoft.com/CustomerEvidence/Search/EvidenceDetails.aspx?EvidenceID=4641&LanguageID=1.
>
> Thanks for any help you might provide

With the right hardware, the right multi-threaded application, and the
right database design, you could do it. I don't know that C# would be my
first choice for that type of application performance. C/C++ will likely
yield better performance. You'll likely need a multi-threaded app,
persistent connections, stored procedures, fast hardware and a properly
designed disk-subsystem. What is the querying for?

This type of project will require careful planning and design. You
should definitely enlist resources who have done this before, since even
the smallest mistake can cause problems.

http://www.microsoft.com/sql/prodinfo/previousversions/scalability.mspx

--
David Gugick - SQL Server MVP
Quest Software

5 Feb 2006 12:23 AM

Dan Guzman

If your requirement is to insert the raw quote data, I suggest you look into
the SqlBulkCopy class, introduced in .NET 2.0. This ought to be able to
insert at a rate of tens-of-thousands of rows per second.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Larry" <La***@discussions.microsoft.com> wrote in message
news:9F9E926F-74A4-4038-B4F5-CFC55488CD19@microsoft.com...
>I have been using an OODB as the repository for a stock-quote ticker plant.
> I would love to be able to convert this to SQL Server.
>
> Quotes come in at a nominal rate of 10K per second with peaks up to 60K.
> The quote is fairly basic data: time, price, volume, ExchangeID - leaving
> out
> the minor variations. I already have this data in a write cache in C#, so
> the data could be cached per stock symbol. While this data is flying in,
> I
> also will be querying a bit heavily also.
>
> 1) Is this even thinkable with SQL Server 2005 Express, Standard or
> Enterprise?
>
> 2) I haven't had much luck with good ole Google in finding some direction.
> I'm obviously not a SQLServer expert, but given a pointer towards the
> write
> technology and methods, I might have hope.
>
> 3) There is a major Stock Trading Technology company that says they use
> SQL
> Server 2005 for this purpose. Of course, they don't describe the methods.
> This is at
> http://members.microsoft.com/CustomerEvidence/Search/EvidenceDetails.aspx?EvidenceID=4641&LanguageID=1.
>
> Thanks for any help you might provide