0

I want that as a user is inserting data into a table, he is also viewing it simultaneously.

For this if I use a single table, then will it take more time & processing load because the 2 processes: INSERT and SELECT, both would occur on the same table?

So I want that both the processes are executed separately on two different database objects.

Like INSERT should be done on one database object and SELECT should be done from the other but the data which in inserted, would be seen by the Select query.

What's the solution for this? Should I use a temporary table, a view, or two separate tables, but if I use two separate tables then there would be replication of data.

so what shall i do? please help.

My Winform Application would run on 3 PCs on LAN and SQL Server only on one PC.

In this case, every minute all 3 users would insert data . So I would display data immediately to the user at pc1 if data is updated by another user on pc2

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
sqlchild
  • 8,754
  • 28
  • 105
  • 167

1 Answers1

2

Have you actually tried just reading and writing on the same table? RDBMS are designed for concurrency. We have millions of rows written per minute and dozens of complex aggregates on the same data in the same minute. We havn't noticed any issue.

Data has to get from tablewrite to tableread: this means a read on tablewrite at some point. Now, you can maintain this in a trigger (or some such means) which means extra processing for every write.

Note: you can use the OUTPUT clause to read data that has just been inserted.

You can also try snapshot isolation but given you don't have a problem why not keep it simple and just use the one table

gbn
  • 422,506
  • 82
  • 585
  • 676
  • @gbn: sir , you mean to say that it would not give any load or speed slowing if i do both operations on the same single table , and don't use any other table, view or else – sqlchild Mar 10 '11 at 06:47
  • 2
    for 99.99% of cases, correct. it doesn't matter. You aren't in the 0.01% of cases. – gbn Mar 10 '11 at 06:49
  • 1
    Don't forget the fun & games of keeping your data consistent. Until your insert has been committed it's not even visible to other processes. Commit too early and you can have consistency issues. But basically what @gbn says. You're unlikely to be doing anything too different. – lll Mar 10 '11 at 06:53
  • 1
    @lll: unless, of course, those queries use `READUNCOMMITTED`..... then they see data that's not been committed yet, too.... – marc_s Mar 10 '11 at 07:03
  • @gbn: my appplication would run on 3 pcs with lan and sql server on one. in this case, every minute all 3 users would insert data . so i would display data immediately to the user at pc1 if data is updated by another user on pc2. – sqlchild Mar 10 '11 at 07:11
  • In which case you may need service broker to notify your other clients that data has been written. I suggest that ask a question about your problem instead asking very specific questions about the solution you chose If you mentioned this use case first then the answer would have been different – gbn Mar 10 '11 at 07:30
  • @gbn: sir, which will take more time, i am using a view to view whole data of a table & a simple select query to view the whole data. 1.>select * from vw_items (view) 2.>select * from items (table) – sqlchild Mar 10 '11 at 09:20
  • you have been told this already: a view has no performance benefit – gbn Mar 10 '11 at 10:52
  • @gbn: but if the views are indexed views , then do they effect performance? – sqlchild Mar 10 '11 at 11:12
  • @gbn: sir , does concurrency exist in Foxpro? can we insert and select both at the same time from a table in foxpro database? – sqlchild Mar 10 '11 at 11:28
  • @gbn : http://stackoverflow.com/questions/5216899/displaying-updation-of-data-to-multiple-users-which-is-inserted-by-another-user , sir please have a look at this post. – sqlchild Mar 10 '11 at 11:30