Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LuckyItGuy
Starting Member
3 Posts |
 Posted - 2008-01-21 : 16:31:29
|
| I have inherited a half-finished sql-server based project from a recently departed coworker. The critial point of this project is one app thread that reads barcodes, queries a single table in the database for the one record with that code as its primary key, and makes desisions based on that record. The faster that I can make that go, the better the process will run, up to a max rate as high as 20 queries per second if that were possible. I have a limited general knowledge of sql, but very little of sql-server express. My question is what is the best way with sql-server to maximize my single-table request rate?? On some other databases I could create an in-memory temp copy of the table with trigger events on the main table to keep the copy in sync, or I could do an initial select on the entire table to hopefully get the table into cache memory, or I could use some kind of ado-like table on the app side (but do I really gain much of anything doing this??)With SQL server, what is my best approach to maximize my throughput under these conditions??FYI..The c++ app uses direct odbc calls to a localhost database. Table theoretically could have 75000 ever-changing records in it. There are 5 or 6 other processes also hitting on this table, but at a far more lakadaisical (say once every 10 seconds level) rate. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-21 : 16:38:25
|
| Sql server puts data in cache too, and will stay there as long as sql has enough memory to work with. |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2008-01-22 : 09:05:17
|
| Make sure that your indices are optimised. Some versions of SQL have a wizard that can help with this, though I am not sure if SQL Server Express does. Check if there is a clustered index on the primary key though that could impact the other query response times. It may also be worth investigating whether a stored procedure with the record id as a parameter gives you better performance.steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
newsqlguy
Starting Member
13 Posts |
Posted - 2008-01-25 : 15:09:50
|
| how frequently is the data in your table changes? |
|
|
|
|
|
|
Highest speed data access