Solved

create index to c1, c2 and c3

Posted on 2016-08-18
9
65 Views
Last Modified: 2016-08-22
hi have this dinamic query

depending on the parameters selected the query could be
--query 1
select .. from .. where c1=1

--query 2
select .. from .. where c1=1 and c2='lop'

--query 3
select .. from .. where c1=1 and c3=10

--query 4
select .. from .. where c2='pol' and c3=90

as I create the index? to column c1, c2 and c3
0
Comment
Question by:enrique_aeo
9 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41761898
is that your question asking whether or not should we create the indexes for column c1, c2 and c3?

if this is the question, then the answer probably is yes. you can do that.

or if this is not the question you asking for, pls elaborate further for your question.
0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41761964
Well in this case you will have to create NON Clustered Index on all the three columns as you are searching at least on 2 parameters.

You can also create combination of 2 - 2 Nonclustered indexes but too many indexes will hamper the performance of the system.

Also, When you write this of kind query consider parameter sniffing in mind. Always use below approach.


IF @CustomerID IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[CustomerID] = @CustID';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[CustomerID] = CONVERT(BIGINT, ' + CONVERT(NVARCHAR(30),@CustomerID) + N')';
END

http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/

Enjoy !

Pawan Kumar Khowal
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 41762042
queries 1,2 and 3 may all 3 profit from an index on c1, but it mainly depends on the cardinality of the values of c1 if it's really profitable.

from what you wrote, c1 actually does not "look" like a good candidate, so I would create (at least9 the following indexes:
index1: C3  + C2
index2: C1  + C2
that will support with a minimal number of indexes most cases.

note: the order of the fields in the index is important!
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41762125
Since your query is very dynamic I would create 3 indexes, each one for each field.
So:
Index on C1
Index on C2
Index on C3

NOTE: By your sample C1 might be a bit field? If so, then don't create any index for C1 but for C2 and C3 only.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41762686
In general terms:

What percentage of the time do you query on c1?  If you query on c1 most of the time, then cluster the table first on c1, assuming it is at all reasonably selective.

If you do that, then you can create a nonclustered index on:
(c2, c3) or
(c3, c2)
Index first on whichever one you search on most often.

Of course to be sure about clustering you'd need to look at index stats, including index missing, index usage and index operational stats.
0
 

Author Comment

by:enrique_aeo
ID: 41763968
c1, c2 and c3 are nonclustered index

the user enters the search parameters from webform.
The parameter are: firstname, lastname, nationalidnumber are varchar
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41764104
what then is c1, c2 and c3?
0
 

Author Comment

by:enrique_aeo
ID: 41765878
c1= firstname
c2= lastname
c3= nationalidnumber
0
 

Author Closing Comment

by:enrique_aeo
ID: 41765982
ok
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now