473,888 Members | 1,324 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Searching question....

aaj
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

Jul 20 '05 #1
3 1489
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)
Jul 20 '05 #2

"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
Jul 20 '05 #3
aaj
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

Jul 20 '05 #4

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

Similar topics

1
10084
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.
1
1254
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...
3
1195
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.
8
2372
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
2
1647
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"
4
2743
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
4
1441
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...
5
2411
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...
2
1436
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...
0
9800
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
11186
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
10778
jinu1996
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...
1
10887
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,...
0
10439
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...
0
9597
agi2029
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...
0
7148
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();...
0
6015
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3252
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.