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 |
|
ijprasad
Starting Member
29 Posts |
 Posted - 2005-04-01 : 06:07:10
|
| We are facing the problem while reading from the table. We have real-time transaction processing system for one of our client. One database contains two tables(Table1 and Table2) of Raw transaction. Table1 and Table2 has identity column SerialNo(increment is 1). Data to these tables are appended by the third party application in random fashion real-time (as soon as they receive transaction and we do not have control over this application). Our Server Application reads the above transactions as soon as they inserted into the table.Currently we are reading transactions from only one table(Table1) based on Serial No Identity Column.At the first time Server fires the query on the database, filtering Serial No greater than zero if it fetches the X records than it would process the those X records than fires the second query filtering Serial No greater than X and so on. If the query fetches zero record than It would wait for 10 seconds than fires it again, this process will be continuous till we get records. For ExampleFirst QuerySELECT * FROM Table1(NOLOCK) WHERE SerialNo > 0 if above query fetches the X Reocords and Last Serial No is N1 than Second Query would be SELECT * FROM Table1(NOLOCK) WHERE SerialNo > N1 if above query fetches the Y records and last serial number is N2 than Third Query would be SELECT * FROM Table1(NOLOCK) WHERE SerialNo > N2 As the third party application insertion is continuous in random fashion, the X and Y Values will varies from point to point. Average record insertion speed is 500 transaction per second. Total records for the given day is about 1500000. During this process we are missing the 3 to 4 transactions on the given day. This happens once in a week or two weeks. Please suggest us what could be wrong? We have done several test and arrives to the conclusion that there is problem while reading from the database. Is Simultaneous Write and Read can't be done? Note: This is inform you that the processing for the above transactions take place within a second. we can't delay the transaction processing from our side. We are using SQL Server 7.0 and Borland Delphi 5.0. It is Borland Database Engine(BDE) connects to the database.Inderjeet |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-04-01 : 07:15:34
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47833DamianIta erat quando hic adveni. |
 |
|
|
|
|
|
Sql Table Read problem