473,735 Members | 8,735 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cursor, Query, View and Recordset

sci
Could someone help me by answering the questions below?

What's a cursor?
What's difference between Query and View?
Is a RecordSet just part of a table? Can it be part of a query of view?
If the content in a table changed, is it necessary for a old recordset to
renew itself by do "Requery()" ?

Thanks for your help!
Jul 20 '05 #1
4 11260
sci (sc****@yahoo.c om) writes:
What's a cursor?
As long as we are talking pure SQL only, a cursor is a means to traverse
a result set one row at a time. This is usually a much slower means of
operation, than to process all rows at once in a set-based statement.
But occasionally logic is such that writing a set-based statement is
very complex.

But when you involve clients, the concept of a "cursor" gets a new
meaning, and it confused me for a long time too.

In a client language like Visual Basic, you must process rows (or records
as they usually once they've reach the client) one by one. But this alright,
because at this point all data is in memory.

With a client-side cursor, ADO gets all data to the client, and all
iteration is done there. A server-side part of the iteration is
carried out on the server. This is usually slower and takes up more
resources, but it depends on the kind of cursor. A forward-only
read-only server-side cursor does not really have a cursor on the SQL
side, whereas a updatable keyset cursor has.
What's difference between Query and View?
You could say that a view is a pre-packaged query. A query is something
like SELECT * FROM x WHERE col = 3. "x" here could be a plain table, or
a view.

In fact what the query returns is yet another table, although it is not
materialized as such. Normally though, you call the result of a query
for a result set.
Is a RecordSet just part of a table? Can it be part of a query of view?
The recordset is just the result-set that has traversed to the client
and ADO. The recordset is the result of a query, and it can be a one-to-one
mapping to a table or a view, but it could be any query.
If the content in a table changed, is it necessary for a old recordset to
renew itself by do "Requery()" ?


It might be a good idea, but it depends on the logic of the application.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
sci
Erland Sommarskog <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
sci (sc****@yahoo.c om) writes:
What's a cursor?
As long as we are talking pure SQL only, a cursor is a means to traverse
a result set one row at a time. This is usually a much slower means of
operation, than to process all rows at once in a set-based statement.


Thanks for your explanation! This is very helpful for my learning on this
subject.

While reading your post I still have some other questions. Sorry for my
ignorance.

What's "result set"?
What's "set-based statement"?
Why "set-based statement" is faster than cursor?
What's difference between Query and View?


You could say that a view is a pre-packaged query. A query is something
like SELECT * FROM x WHERE col = 3. "x" here could be a plain table, or
a view.

Since we call a view a pre-packaged query, does this mean that we can
constructed a view and save it in a database for it to be used in a query?
Can a view be either constructed from one table or multiple tables? Is a
view always to be used in a query, or can it be used somewhere else?
Jul 20 '05 #3
On Tue, 30 Sep 2003 05:13:22 GMT, "sci" <sc****@yahoo.c om> wrote:
Erland Sommarskog <so****@algonet .se> wrote in message
news:Xn******* *************** @127.0.0.1...
sci (sc****@yahoo.c om) writes:
> What's a cursor?
As long as we are talking pure SQL only, a cursor is a means to traverse
a result set one row at a time. This is usually a much slower means of
operation, than to process all rows at once in a set-based statement.


Thanks for your explanation! This is very helpful for my learning on this
subject.

While reading your post I still have some other questions. Sorry for my
ignorance.

What's "result set"?


Its how we refer to the results of a SELECT statement. eg. SELECT name
FROM employee WHERE name like 'A%';

this will give up all the rows in the table with names starting with
A. This collection of rows is the result set.What's "set-based statement"?
Essentially what I showed above. The table called employees has many
names in it. My statement works on only those starting with A, or the
SET of rows meeting this condition.Why "set-based statement" is faster than cursor?
Because that's what a relational database is designed for.

Tables are indexed, which makes finding records fast. Lets assume that
there are 1000 employees of whom 15 start with A. A cursor is going to
go through the whole table reading every row, while the SQL way is to
use a WHERE clause. Assuming that the name column is indexed SQL
server will be able to find all the names starting with A very
quickly. Assume there is a salary column we want to update by a %age.

UPDATE employee SET salary = salary *1.15 WHERE name LIKE 'A%'

The server uses the index to find the bunch of rows who start with A
and performs the update to only those rows.
> What's difference between Query and View?


You could say that a view is a pre-packaged query. A query is something
like SELECT * FROM x WHERE col = 3. "x" here could be a plain table, or
a view.

Since we call a view a pre-packaged query, does this mean that we can
constructed a view and save it in a database for it to be used in a query?
Can a view be either constructed from one table or multiple tables? Is a
view always to be used in a query, or can it be used somewhere else?


Jul 20 '05 #4
sci (sc****@yahoo.c om) writes:
What's "result set"?
Lyndon Hills has already explained this, but permit me to elaborate.
The theory behind relational databases are based on set theory. A table
is a unordered set of data. Thus, the result of a query is also a
table in the logical sense, or a set if you like.
What's "set-based statement"?
A statement which operates on many rows at a time.
Why "set-based statement" is faster than cursor?
I like to supplement Lyndon's answer. He said that tables are fast
because they are indexed, and that is true. However, set-based
statements are faster even if there is no useful index. Say that
we want increaese the salary for all employees with 2%. This can
be done with:

UPDATE employees SET salary = salary * 1.02

The alternative to a set-based statement would be to set up a cursor
and update one row at a time. This would be a lot slower, because there
is a certain overhead to locate a row.

And, as Lyndon so well said: because set-based is what relational
engines are designed for.
Since we call a view a pre-packaged query, does this mean that we can
constructed a view and save it in a database for it to be used in a query?
Can a view be either constructed from one table or multiple tables? A view can be a query that includes many tables - or other views for
that matter. And, yes, a view can be saved in the database for later
queries.
Is a view always to be used in a query, or can it be used somewhere else?


About anything in a database is being used in a query one way or another.
You don't put data in a database, unless you intended to query it in
some way or another.

Normally, you construct views to give users easier access to data. Not
all systems have views, though. The system I work with does not, for
instance. This is because our users access the database from a GUI,
and do not access the database directly.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
7685
by: Jim | last post by:
I having a difficult time here trying to figure out what to do here. I need a way to scroll through a recordset and display the results with both forward and backward movement on a web page(PHP using ADO/COM).. I know that if I use a client side cursor all the records get shoved to the client everytime that stored procedure is executed..if this database grows big wont that be an issue?.. I know that I can set up a server side cursor...
15
3826
by: Philip Mette | last post by:
I am begginner at best so I hope someone that is better can help. I have a stored procedure that updates a view that I wrote using 2 cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't do it using reqular transact SQL. The problem is that this procedure is taking longer and longer to run. Up to 5 hours now! It is anaylizing about 30,000 records. I think partly because we add new records every month. The procedure...
3
23177
by: Ryan Hubbard | last post by:
I would like to run a query from VBA. The query will be one with parameters. Is there a way to run the query and have Access prompt for the values like it does if I where to execute it through the database window?
0
3025
by: totierne | last post by:
comp.databases.ms-access, I want to know how to use Oracle views with session variables in Access. The parameterised views in access, are migrated to views with per session variables. The open questions: How to display a resultset
2
3008
by: P B via AccessMonster.com | last post by:
I have a list of 160,000 records with these fields: fname, lname, address, city, state, zip, dob I need to generate a list with all fields where the first initial of lname and the dob are equal. How? I can't seem to get Left() to work in a query. Could I put this in a VBscript and generate a recordset? What's the easiest way to generate this list? This seems so simple.
6
2243
by: sghi | last post by:
Hi All, I'm new to this group and quite new to access/vba. So, shortly after beginning to write a simple application for my wife, I came across a blocking problem: I need to intercept the sql statement that stay behind a current, but not yet saved query. When I work on saved queries I use: strCurrentName = CurrentObjectName Dim dbsCurrent As Database Set dbsCurrent = CurrentDb
6
3280
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server table, 200K rows, yearMonth is an indexed long integer the primary key is t_orno, t_pono
6
17456
by: A.M | last post by:
Hi I use a code similar to this to retrieve data from Oracle database: import cx_Oracle
36
3060
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I have set up a Query to show only records that meet a certain criteria...therefore excluding all of the records that do not meet this criteria (just for the record the criteria is any record within my database that falls within two months of its "Due...
0
8962
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8785
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9463
Oralloy
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...
0
9200
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4559
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...
0
4822
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2739
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2188
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.