To avoid Table locking - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

To avoid Table locking

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 00:54:53

I have a table 'Sales_Details' having 5m records.Insertion to the 'Sales_Details' table should be a very fast process.If a run a report(done using Crystal Report),in which data is fetching from 'Sales_Details' for one year(it may take one minute),insertion will become very slow.Sometimes it will result in 'ServerTimeOut'.How can I overcome this issue?.Please help me.

Thanks & Regards
Binto Thomas

Sachin.Nand

2937 Posts

Posted - 2010-02-04 : 02:21:02

So what is ur requierment?
That report should wait for the insertion to finish or the insertion should wait till the report gets executed?

PBUH

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 03:02:33

SELECT * FROM TABLE_NAME WITH(NOLOCK)

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:16:35

quote:
Originally posted by divyaram

SELECT * FROM TABLE_NAME WITH(NOLOCK)

Regards,
Divya

but this will return uncommited data so there can be dirty reads

Kristen
Test

22859 Posts

Posted - 2010-02-04 : 03:23:25

The underlying problem needs fixing (which is probably poor quality query in Crystal Reports)

At the very least, if SQL 2005 or later, READ_COMMITTED_SNAPSHOT should be used, and not NOLOCK which is the quick and dirty solution developers use and not worry about the consequences of dirty reads.

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 03:29:51

quote:
Originally posted by Idera

So what is ur requierment?
That report should wait for the insertion to finish or the insertion should wait till the report gets executed?

PBUH

Insertion and data reading should go simultaneously without having any waiting.Is it possible..?

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:31:26

quote:
Originally posted by binto

quote:
Originally posted by Idera

So what is ur requierment?
That report should wait for the insertion to finish or the insertion should wait till the report gets executed?

PBUH

Insertion and data reading should go simultaneously without having any waiting.Is it possible..?

Thanks & Regards
Binto Thomas

but at any time you need to see only commited data right?

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 03:34:27

quote:
Originally posted by Kristen

The underlying problem needs fixing (which is probably poor quality query in Crystal Reports)

At the very least, if SQL 2005 or later, READ_COMMITTED_SNAPSHOT should be used, and not NOLOCK which is the quick and dirty solution developers use and not worry about the consequences of dirty reads.

I am using SQL Server 2008.How can I use READ_COMMITTED_SNAPSHOT.Is there any performance problem for using the same.How READ_COMMITTED_SNAPSHOT work in procedure?

Thanks & Regards
Binto Thomas

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 03:37:47

quote:
Originally posted by visakh16

quote:
Originally posted by binto

quote:
Originally posted by Idera

So what is ur requierment?
That report should wait for the insertion to finish or the insertion should wait till the report gets executed?

PBUH

Insertion and data reading should go simultaneously without having any waiting.Is it possible..?

Thanks & Regards
Binto Thomas

but at any time you need to see only commited data right?

Yes visakh.wats the solution..?

Thanks & Regards
Binto Thomas

Kristen
Test

22859 Posts

Posted - 2010-02-04 : 09:48:06

"How can I use READ_COMMITTED_SNAPSHOT"

Please read SQL documentation

"Is there any performance problem for using the same"

Yes, but probably "not significant"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources