Select on large table. - Microsoft SQL Server

LineVoltageHalogen

Greetings All, I was wondering what would happen if I were to do a
"select * from table" on a table that has about 5 million rows. Would
my read block other writers to the same table? Would it block other
readers? I know SQL uses optimistic lockign by default but I am not
sure what this means to other users trying to access the same table?
Any advise would be greatly appreciated.

TFD

Jul 23 '05 #1

Subscribe Reply

2169

Erland Sommarskog

LineVoltageHalo gen (tr************ ****@yahoo.com) writes:

Greetings All, I was wondering what would happen if I were to do a
"select * from table" on a table that has about 5 million rows. Would
my read block other writers to the same table? Would it block other
readers? I know SQL uses optimistic lockign by default but I am not
sure what this means to other users trying to access the same table?
Any advise would be greatly appreciated.

Other readers would not be blocked.

Writers would probably be blocked for the duration of the query, since
I would expect SQL Server take out a table lock. That would mean that
the table would be locked until your client has gotten all rows from
SQL Server.

With row locks, locks would be released when the client has received
the rows, as SQL Servers default isolation level is READ COMMITTED.

In any case, getting five millions is a hefty operation and not really
recommendable.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #2

David Housman

If you're using access, you might have timeout problems. You can alter
that setting in the Enterprise manager.
I suggest multipart queries if you're selecting from multiple tables or
cursing through the rows if your doing data processing.

-DJ

Jul 23 '05 #3

Gert-Jan Strik

It will not block readers, since the statement will use read locks, and
read locks are shared.

When I run "SELECT * FROM ..." on a 18 million row table (2.5GB), and
then run sp_lock on a seperate connection, I only see intend shared page
locks (different pages, but just one at a time), and an intend shared
table lock (and a shared database lock).

So this basically means writers are not blocked, apart from the
occasional page lock (which are so short that they didn't show in my
sp_lock experiment).

If I run "SELECT * FROM .. (TABLOCK)", then it is a different story.
Then there are no page locks but the (requested) shared table lock. From
that moment on the transactions on that table are piling up, because
then writers are locked.

Hope this helps,
Gert-Jan

LineVoltageHalo gen wrote:

Greetings All, I was wondering what would happen if I were to do a
"select * from table" on a table that has about 5 million rows. Would
my read block other writers to the same table? Would it block other
readers? I know SQL uses optimistic lockign by default but I am not
sure what this means to other users trying to access the same table?
Any advise would be greatly appreciated.

TFD

Jul 23 '05 #4

Similar topics

3322

Help with trick MySQL select problem

by: Funnyweb | last post by:

I have a database table, which has field that could contain a single integer or a list of comma separated integers. Is it possible to match each row of that field against an array of integers and return those rows where any of the integers in that field are in my array? For example suppose I did the following: arInts = array(2,4,6,8,10);

PHP

2061

Strange SELECT speed problem using mysql 4.0.xx

by: Sven Reifegerste | last post by:

Hi, i have a table with INT columns id,key,b1,b2,c1,c2, having 1.500.000 rows. 'key' and 'id' are indexed (Kardinality 385381) and id (Kardinality 1541525). Performing a SELECT * FROM table WHERE key IN (10,11,12) OR key BETWEEN 20000 AND 28000 AND b1<4500000 AND b2>3954545 AND c1<4543554 AND c2>4400000

MySQL Database

5637

Using "SELECT * " is a bad practice even when using a VIEW instead of a table?

by: serge | last post by:

Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures changed to be like:

Microsoft SQL Server

2679

Will splitting up a table improve our select performance?

by: James Conrad St.John Foreman | last post by:

One of the larger tables in our database is now 6.8 million rows (1 per financial transaction since 2000). Every time an amendment is made to a booking, new rows are added to the table for each transaction, so in general we never have any call to update old rows. Usually, we only deal with analysis on transactions in the current financial year or the previous one, but *occasionally* we'll want to go back further. So I'm thinking as...

DB2 Database

4090

select for update & lock contention

by: Ed L. | last post by:

I think I'm seeing table-level lock contention in the following function when I have many different concurrent callers, each with mutually distinct values for $1. Is there a way to reimplement this function using select-for-update (or equivalent) in order to get a row-level lock (and thus less contention) while maintaining the function interface? The docs seem to suggest so, but it's not clear how to return the SETOF queued_item and also...

PostgreSQL Database

2361

select ... where <column>='' does a seqscan [auf Viren geprueft]

by: Silvio Matthes | last post by:

Hello, I'm new to the list and did not find a suitable answer to my question so here it is: I try to select the rows of a table where the content of a varchar-column is empty ('') and PostgresQL is doing a seqscan. I've tried this on a PostgresQL-Serverversion 7.3.4 and 8.0 beta1.

PostgreSQL Database

27607

Order by in a INSERT INTO..SELECT

by: pb648174 | last post by:

I have the following basic statements being executed: Create a temp table, #TempPaging Insert Into #TempPaging (Col1, Col2) Select Col1, Col2 From SomeOtherTable Order By Col2, Col1 Select * from #TempPaging I can't provide a reproduceable scenario right now without making this

Microsoft SQL Server

3408

which data type to select

by: rAinDeEr | last post by:

Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character text field. I want to know which Data type I need to use so that it doesnt waste memory. thanks in advance, rAinDeEr

DB2 Database

2927

Select from dropdown element in iframe

by: matwilko | last post by:

hi, i am trying to create a simple version of itunes...and i am using iframes to do this. I have already set up the iframes using dreamweaver and used a drop-down menu to select the genre. When selecting the genre the artists appears in iframe1...and i want to be able to select the artist so that the songs appear. I am unsure of how to do this, could anybody help? Thanks Here is my current code... <html> <head> <title>MiTunes</title>...

Javascript

10308

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10143

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8964

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6729

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5375

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5507

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4040

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3633

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2870

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General