473,549 Members | 2,663 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question : How SQL chooses an index for a process

I have just tested 3 queries using QA. The complete
test information :

------
CREATE TABLE agls1
( fyear char(4) NULL ,
fprefix char(3) NULL ,
fvcno char(20) NULL ,
fdate datetime NULL ,
fid char(15) NULL ,
fiddate datetime NULL ,
fdesc char(60) NULL ,
facc char(12) NULL ,
fval decimal(18, 2) NULL ,
fcrc char(5) NULL ,
fsub char(1) NULL ,
fmaster char(9) NULL ,
fcode char(15) NULL )
CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
fdate, fvcno)
CREATE INDEX b ON agls1 (fyear, facc, fdate,
fprefix, fvcno)
CREATE INDEX c ON agls1(fyear,fsu b, fmaster, fcode)

insert into agls1
( fyear,fsub,fmas ter,fcode,fpref ix,fdate,fvcno, facc )
values
( '2004','A','B', '123','inv','20 040101','01','1 11' )

--query-1
select * from agls1
where fyear = '2004' and fprefix = 'inv' and
fdate = '20040101' and fvcno = '01'

--query-2
select * from agls1
where fyear = '2004' and facc = '111' and
fdate = '20040101' and fprefix = 'inv' and fvcno = '01'

--query-3
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'
------

The execution plan shows that the index a
is always used for all 3 select queries above.

I have 3 questions for you :
a. Why does SQL not choose index b for query-2 ?
Why does SQL not choose index c for query-3 ?
b. Is it right that query-2 does not benefit from
index b and query-3 does not benefit from index c ?
c. How does SQL choose an index for a process ?

Could anyone help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
6 4448
Anita (an*******@devd ex.com) writes:
The execution plan shows that the index a
is always used for all 3 select queries above.

I have 3 questions for you :
a. Why does SQL not choose index b for query-2 ?
Why does SQL not choose index c for query-3 ?
There is only one row in the table, that makes the test somewhat
meaningless.

But the general is that there is always a tradeoff whether to use a
non-clustered index or not. When SQL Server finds row through a non-
clustered index, it has to go to the data page and get data requested
in the query which is not present in the index. This means that it
can be more expensive to use the index than to scan table, if the
optimizer estimates that the index will find many rows.
b. Is it right that query-2 does not benefit from
index b and query-3 does not benefit from index c ?
No, that depends on how the data looks like. Let's first take query/index
c. Say that there is over a million rows with year = 2004. In this case,
without the index, query c would have to scan all those rows in the
clustered index, whereas with the non-clustered index can find the
matching rows faster. But if there are say, 10000 rows that matches
query c, I would execpt SQL Server to use the clustered index.

As for index b, there are situations where this index could help, but
in this case, there must be many duplicates in the clustered index,
so that you actually make the query significantly more precise by adding
that extra column.
c. How does SQL choose an index for a process ?


SQL Server uses a cost-based optimizer which makes its decisions from
statistics about the table column. Therefore the same query can get
different query plans with different data.

There is material in Books Online you can study. I can also recommend
Kalen Delaney's "Inside SQL Server 2000", which covers this topic
in detail.

--
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
Hi Erland,

Thanks a lot for the lucid explanation.
Very helpful.

I will continue testing using minimum amount of rows
to see that SQL Server uses index c for query-3.
The data must easily force SQL Server to use
index c. If you do not mind, could you advice me
how data looks like that I should create.

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3


Anita wrote:
I have just tested 3 queries using QA. The complete
test information :

------
CREATE TABLE agls1
( fyear char(4) NULL ,
fprefix char(3) NULL ,
fvcno char(20) NULL ,
fdate datetime NULL ,
fid char(15) NULL ,
fiddate datetime NULL ,
fdesc char(60) NULL ,
facc char(12) NULL ,
fval decimal(18, 2) NULL ,
fcrc char(5) NULL ,
fsub char(1) NULL ,
fmaster char(9) NULL ,
fcode char(15) NULL )
CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
fdate, fvcno)
CREATE INDEX b ON agls1 (fyear, facc, fdate,
fprefix, fvcno)
CREATE INDEX c ON agls1(fyear,fsu b, fmaster, fcode)

insert into agls1
( fyear,fsub,fmas ter,fcode,fpref ix,fdate,fvcno, facc )
values
( '2004','A','B', '123','inv','20 040101','01','1 11' )

--query-1
select * from agls1
where fyear = '2004' and fprefix = 'inv' and
fdate = '20040101' and fvcno = '01'

--query-2
select * from agls1
where fyear = '2004' and facc = '111' and
fdate = '20040101' and fprefix = 'inv' and fvcno = '01'

--query-3
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'
------

The execution plan shows that the index a
is always used for all 3 select queries above.

I have 3 questions for you :
a. Why does SQL not choose index b for query-2 ?
Why does SQL not choose index c for query-3 ?
b. Is it right that query-2 does not benefit from
index b and query-3 does not benefit from index c ?
c. How does SQL choose an index for a process ?
The last question in particular has lots of book chapters on it.
The fact is that the table is so small that no index can really
help much. A blind table-scan is fastest with a one-row table.
To see more intuitive index use, you should probably test with
a table having thousands of well-distributed rows.
Joe Weinstein at BEA
Could anyone help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Jul 20 '05 #4
Anita (an*******@devd ex.com) writes:
I will continue testing using minimum amount of rows
to see that SQL Server uses index c for query-3.
The data must easily force SQL Server to use
index c. If you do not mind, could you advice me
how data looks like that I should create.


You can always use an index hint to convince SQL Server to use an index:

SELECT * FROM tbl WITH (INDEX = c)

To make it simple you should have fyear = 2004 in all rows you create.
But the values in fsub, fmastser and fcode should vary.
--
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

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Anita (an*******@devd ex.com) writes:
I will continue testing using minimum amount of rows
to see that SQL Server uses index c for query-3.
The data must easily force SQL Server to use
index c. If you do not mind, could you advice me
how data looks like that I should create.
You can always use an index hint to convince SQL Server to use an index:

SELECT * FROM tbl WITH (INDEX = c)


Just to jump in, you can of course do that. However (and this is to Anita,
not Erland since I know he's aware of this), it's generally a fairly bad
idea to force an index hint, since your data may later change in such a way
to make the index less useful.

I'd recommend finding some of the papers Kalen Delany has written on this
subject as it may help.

To make it simple you should have fyear = 2004 in all rows you create.
But the values in fsub, fmastser and fcode should vary.
--
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 #6
I have inserted 810 rows by following Erland's advice.
With these rows, SQL Server uses index c when executes
query :
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'

Thanks again to all of you that sent the replies

Anita Hery

Note :
Below is my insert test :

declare @sub as int, @master as int, @code as int
set @sub = 0 --max 15
set @master = 0 --max 6
set @code = 0 --max 15 digit

lsub:
set @sub = @sub + 1
set @master = 0
set @code = 0
lmaster:
set @master = @master + 1
set @code = 0
lcode:
set @code = @code + 1
insert into agls1
(fyear,fsub,fma ster,fcode,fpre fix,fdate,fvcno ,facc)
values
('2004',str(@su b,1),str(@maste r,9),
str(@code,15),' inv','20040101' ,'01','111')

if @code < 15 goto lcode
if @master < 6 goto lmaster
if @sub < 9 goto lsub

select * from agls1
where fyear = '2004' and fsub = 'A' and
fmaster = 'B' and fcode = '123'

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #7

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

Similar topics

0
2116
by: claudel | last post by:
Hi I have a newb PHP/Javascript question regarding checkbox processing I'm not sure which area it falls into so I crossposted to comp.lang.php and comp.lang.javascript. I'm trying to construct a checkbox array in a survey form where one of the choices is "No Preference" which is checked by default. If the victim chooses other than "No...
3
1472
by: aaj | last post by:
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...
5
276
by: Timur | last post by:
Hi gurus, I have a problem to convert MS SQL Server application to DB2. I have a view which combines 7 tables ( table sizes 60millions rows, 3 mill, 1 mill, other small ones) I use this view to populate OLAP cube and in SQL Server it takes abut 1 hour.. In DB2 it takes forever. Execution plan looks ugly - DB2 sorts !!!!! 50 mil table by...
58
30185
by: Larry David | last post by:
Ok, first of all, let's get the obvious stuff out of the way. I'm an idiot. So please indulge me for a moment. Consider it an act of "community service".... What does "64bit" mean to your friendly neighborhood C# programmer? The standard answer I get from computer sales people is: "It means that the CPU can process 64 bits of data at a time...
0
1611
by: Dave Coate | last post by:
I am working on a generic way to launch multiple similar processes (threads) at once, but limit the number of threads running at any one time to a number I set. As I understand it the following line makes a Queue "thread safe", so I do not need to explicitly lock and unlock it when multiple threads are working with it. 'Thread safe queue...
10
3687
by: Rider | last post by:
Hi, simple(?) question about asp.net configuration.. I've installed ASP.NET 2.0 QuickStart Sample successfully. But, When I'm first start application the follow message shown. ========= Server Error in '/QuickStartv20' Application. -------------------------------------------------------------------------------- Configuration Error...
23
1467
by: Anunay | last post by:
Hi all, Suppose a text file is given and we have to write a program that will return a random line from the file. This can be easily done. But what if the text file is too big and can't fit into the main memory completely? In this case, how will we modify our code? Also, if we are given a stream, instead of a file, then what changes are...
2
3503
by: P Adhia | last post by:
Hi, I am trying to understand why following simple query is running very slow (33 hours) select ROW_ID , a.CREATED , a.CREATED_BY , a.LAST_UPD , a.LAST_UPD_BY
1
2067
by: WolfgangZ | last post by:
Hello, I'm starting some subprocesses inside a loop. The processes run independent and dont need any communication between each other. Due to memory issues I need to limit the number of running processes to around 10. How can I insert a break into my loop to wait until some processes are finished? Some minimal examplecode:
0
7521
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...
0
7451
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...
0
7720
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. ...
0
7959
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...
0
7810
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...
1
5369
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...
0
3501
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...
1
1944
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
1
1061
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.