This is a simple question compared to some of the stuff that gets asked (and
answered) here, but sometimes its easy to over look the simpler things.
I've been working with databases for a few years now, but have no formal
training, so some times you just get on and if it works dont worry about it.
But sometimes I wonder just how it works underneath the skin e.g.
If I have 100000 records in a complicated view and say each record, amongst
other stuff, contains a name and an archived flag represented by a bit,
which are pulled together from individual tables.
Of the 100000 records say there are 25 that have the bit set to 0 and
remainder have the bit set to 1 (for instance '0' this may mean data that is
in the current month and so not archived)
If I'm looking for the name 'fred' which is I know amongst the 25 (current
data), I would use some thing like ' select name from view where name =
'fred' and bit = 0'
So in my own mind I'm thinking well the sensible thing for the database to
do is to discard the mass of records where the archieved bit = 1 and then
only search the remaining 25.
But what I then think is there has to be a time overhead in disgarding the
many thousands I'm not interested in. Does sql have to generate the view for
all the data in the view, process each record in turn, saying I'm not
interested in this one, or this one etc and then just pulling out the ones
it is interested in to a second list , then processing these in the second
list.
Or does it process the relevant sub tables first i.e finding the matching
record for fred and the matching ones for bit = 0 and then applying the
rules for the view afterwards.
Or ( as I suspect) does it do it a much cleverer way ??
thanks for any help
Andy 3 1475
On Wed, 15 Sep 2004 08:37:54 +0100, aaj wrote: This is a simple question compared to some of the stuff that gets asked (and answered) here, but sometimes its easy to over look the simpler things.
I've been working with databases for a few years now, but have no formal training, so some times you just get on and if it works dont worry about it. But sometimes I wonder just how it works underneath the skin e.g.
If I have 100000 records in a complicated view and say each record, amongst other stuff, contains a name and an archived flag represented by a bit, which are pulled together from individual tables.
Of the 100000 records say there are 25 that have the bit set to 0 and remainder have the bit set to 1 (for instance '0' this may mean data that is in the current month and so not archived)
If I'm looking for the name 'fred' which is I know amongst the 25 (current data), I would use some thing like ' select name from view where name = 'fred' and bit = 0'
So in my own mind I'm thinking well the sensible thing for the database to do is to discard the mass of records where the archieved bit = 1 and then only search the remaining 25.
But what I then think is there has to be a time overhead in disgarding the many thousands I'm not interested in. Does sql have to generate the view for all the data in the view, process each record in turn, saying I'm not interested in this one, or this one etc and then just pulling out the ones it is interested in to a second list , then processing these in the second list.
Or does it process the relevant sub tables first i.e finding the matching record for fred and the matching ones for bit = 0 and then applying the rules for the view afterwards.
Or ( as I suspect) does it do it a much cleverer way ??
thanks for any help
Andy
Hi Andy,
This question is not as simple as it may seem.
SQL Server has many tricks up it's sleeve to help improve the performance
of a query. So the optimizer *might* choose (as you put it) "a much
cleverer way".
A very important thing to consider here is the use of indexes. If there is
an index defined for the bit column, it will be considered for use in this
query. Normally, indexes on bit columns have little use as they'll match
about 50% of all rows. In your case, the number matched is considerably
less. If the index statistics reflect that almost all values in your bit
column are 1 and if thhe text of your SQL clearly shows you only want the
rows with a value of 0, then the optimizer might decide that using this
index will prove cheaper than using another index or no index at all.
If you run with query in Query Analyzer with the option to show the
execution plan turned on (optien Query / Shoe Execution Plan), you can see
how the query was carried out. You'll also see that the optimizer will
sometimes shuffle elements from your view definition(s) and the select
where the view is used to the point where you wouldn't even recognise your
own query - though this extensive reshuffling will only occur if you have
large tables (for small sets of data, the optimizer doesn't keep searching
for the optimal plan - when the expected execution time is less than the
time it would take to optimize further, the optimizer stops and decides on
the best plan so far. Good is good enough).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"aaj" <a.*@c.com> wrote in message
news:41******** *************** @news.easynet.c o.uk... This is a simple question compared to some of the stuff that gets asked (and answered) here, but sometimes its easy to over look the simpler things.
I've been working with databases for a few years now, but have no formal training, so some times you just get on and if it works dont worry about it. But sometimes I wonder just how it works underneath the skin e.g.
If I have 100000 records in a complicated view and say each record, amongst other stuff, contains a name and an archived flag represented by a bit, which are pulled together from individual tables.
Of the 100000 records say there are 25 that have the bit set to 0 and remainder have the bit set to 1 (for instance '0' this may mean data that is in the current month and so not archived)
If I'm looking for the name 'fred' which is I know amongst the 25 (current data), I would use some thing like ' select name from view where name = 'fred' and bit = 0'
So in my own mind I'm thinking well the sensible thing for the database to do is to discard the mass of records where the archieved bit = 1 and then only search the remaining 25.
But what I then think is there has to be a time overhead in disgarding the many thousands I'm not interested in. Does sql have to generate the view for all the data in the view, process each record in turn, saying I'm not interested in this one, or this one etc and then just pulling out the ones it is interested in to a second list , then processing these in the second list.
Or does it process the relevant sub tables first i.e finding the matching record for fred and the matching ones for bit = 0 and then applying the rules for the view afterwards.
Or ( as I suspect) does it do it a much cleverer way ??
thanks for any help
Andy
Assuming your view is not indexed, then the view name is replaced with the
view definition before the query is executed, so MSSQL is only looking at
the base tables. The easiest way to see this is to look at the query plan in
Query Analyzer, which will show you exactly how MSSQL processes your query.
Exactly how the query is optimized and executed depends on the indexes and
statistics which MSSQL can use to find a good plan. Since the optimizer is
cost-based, it will not always find a 'perfect' plan, as the time required
to find the plan might be greater than the time required to execute the
query with a less than optimal plan.
If the view is indexed, then MSSQL can read the data directly from the view,
without going to the base tables. This can make queries much faster, but any
modifications to data in the underlying tables will be much slower, so it's
probably best to use indexed views mainly for reporting, where the data is
usually static.
I'd suggest you get a copy of "Inside SQL Server 2000" by Kalen Delaney if
you're interested in MSSQL internals.
Simon
Thanks for the info guys
I had a tinker with the analyser and it all looks extremely interesting.
Andy
"aaj" <a.*@c.com> wrote in message
news:41******** *************** @news.easynet.c o.uk... This is a simple question compared to some of the stuff that gets asked
(and answered) here, but sometimes its easy to over look the simpler things.
I've been working with databases for a few years now, but have no formal training, so some times you just get on and if it works dont worry about
it. But sometimes I wonder just how it works underneath the skin e.g.
If I have 100000 records in a complicated view and say each record,
amongst other stuff, contains a name and an archived flag represented by a bit, which are pulled together from individual tables.
Of the 100000 records say there are 25 that have the bit set to 0 and remainder have the bit set to 1 (for instance '0' this may mean data that
is in the current month and so not archived)
If I'm looking for the name 'fred' which is I know amongst the 25 (current data), I would use some thing like ' select name from view where name = 'fred' and bit = 0'
So in my own mind I'm thinking well the sensible thing for the database to do is to discard the mass of records where the archieved bit = 1 and then only search the remaining 25.
But what I then think is there has to be a time overhead in disgarding the many thousands I'm not interested in. Does sql have to generate the view
for all the data in the view, process each record in turn, saying I'm not interested in this one, or this one etc and then just pulling out the ones it is interested in to a second list , then processing these in the second list.
Or does it process the relevant sub tables first i.e finding the matching record for fred and the matching ones for bit = 0 and then applying the rules for the view afterwards.
Or ( as I suspect) does it do it a much cleverer way ??
thanks for any help
Andy This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: joe |
last post by:
I need to replace some question marks in a number of records, but the
find/replace tool uses that character as a wildcard.
How can I search and replace question marks?
Thanks in advance.
|
by: Pacher R. Dragos |
last post by:
I have recently developed an application in c witch acts like a
system logger for windows 2003 server domain controllers and my problem
is that of the size.
In a normal day my program produces more than 3-4 Mb of data in plain
text, and you can imagine that interpreting or getting information from
such a big file is time consuming, that's why I tryed to implement some
archiving functionality.
My question is if someone knows a fast string...
|
by: Ma Xiaoming |
last post by:
Dear ladies and gentlemen,
As you know, by building a Smart Device Application in Microsoft Visual
Studio .NET 2003, we could create a project for Pocket PC.
My question is: How to search files of specific type (such as .bmp) with
C# code?
Help me, please. Thank you very much.
|
by: Gordon Knote |
last post by:
Hi
can anyone tell me what's the best way to search in binary content? Best if someone could post or link me to some source code (in C/C++).
The search should be as fast as possible and it would be great if the engine (or so) would accept multiple parameters (like a search offset, a max number of bytes to search in etc.)
Any ideas
Thanks a lot again
Gordon
|
by: Carlos K |
last post by:
Hello
I'm having some difficulty searching a set of rows in a DataRow collection..
This is my question
What would be an efficient way to search any column of an DataRow array
Let me try to explain the context of this question, that will help you understand what I'm looking for..
Assume that I have a collection of rows that came out of a statement like this
dim myRows() as DataRow = myTable.Select("any query"
| |
by: Kuku |
last post by:
Hi,
Can anyone please tell me good books/sites for sorting and searching.
Finding it a little hard to understand
|
by: Jordan S. |
last post by:
Using .NET 2.0 (C#) I'm writing a small app that will have a "Person" class
that exposes FirstName and LastName properties. At runtime, a "People"
collection will be populated with a few thousand "Person" objects.
I want to provide users with the ability to search for people by FirstName
and/or LastName.
My question: How can I enable users to search for people with the last name
of "De Leon". I cannot assume that my users know how to...
|
by: justobservant |
last post by:
When more than one keyword is typed into a search-query, most of the
search-results displayed indicate specified keywords scattered
throughout an entire website of content i.e., this is shown as three
bolded periods '...' in search-result listings.
Additionally, most content is outdated; as many users need up-to-date
content. Hence, filtering-through search-results becomes quite
cumbersome.
The newsgroup listings allow detailed...
|
by: =?Utf-8?B?VGVycnk=?= |
last post by:
I am implementing an 'incremental search', where as the user types into a
textbox, a datagridview's current position is updated to match the first
record that contains what the user has typed so far. In this particular
case, I am searching the 'CompanyName' column of the underlying dataview,
which of course is sorted by this column. I have noticed that company names
like "U-Freight" and "U-Haul" appear after company names like "UBS" and...
|
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,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
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...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |