Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mortalan
Starting Member
3 Posts |
 Posted - 2002-05-24 : 09:52:44
|
| I created an index on 3 coloums of a table.The time the server needed for that query before indexed it was the same it needed after indexed it.Is there any special query or what made i wrong ?greetings |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-24 : 09:57:32
|
Imagine if someone posted your question here to you. Would you know enough from what was said to offer an answer?POST YOUR DDLquote:
I created an index on 3 coloums of a table.The time the server needed for that query before indexed it was the same it needed after indexed it.Is there any special query or what made i wrong ?greetings
setBasedIsTheTruepath<O> |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-24 : 10:01:27
|
| What SetBased means to say is ....SQL Server has a very complex optimizer engine that determines how to physically execute your query, based on statistical selectivity data and available indexes. In your case, sql decided that using your newly created index wouldn't help it retrieve the data any faster. If SQL Server is wrong you can force the query plan to use a particular index with a query hint.Most likely, your index will not help your query. Post your create table statement, your create index statement and the query you are running and someone will likely help you out. This detail is very necessary for such a specific question.<O> |
|
|
|
mortalan
Starting Member
3 Posts |
Posted - 2002-05-24 : 10:06:37
|
| You're right...Here's some more information:table name: act_preisemy table structure:1 Artnr float 8 10 Artname nvarchar 255 10 Hersteller nvarchar 255 10 Hersartnr nvarchar 255 10 Preis nvarchar 255 10 Waehrung nvarchar 255 10 Verfuegbarkeit nvarchar 255 10 Produkthierarchie nvarchar 255 10 Produktfamilie nvarchar 255 10 Produktgruppe nvarchar 255 1Server is Win2000Server with MSSQL 2000i created a non clustered index on 'Artname', 'Hersteller' and 'Hersartnr'.my query:SELECT * FROM act_preise where Artname LIKE '%UserSpecific%' OR Hersteller='UserSpecific' OR Hersartnr LIKE '%UserSpecific%'Hersteller don't need a LIKE statement, because user could choose it from a drow-down menu.please be clement with me i'm quite beginning my work with that server (3 months i think).What do you mean with DDL?greetingsEdited by - mortalan on 05/24/2002 10:11:25 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-24 : 10:24:30
|
DDL means 'data definition language', DML 'data modification language'create table blah is an example of ddl . . . update blah set blah = blah is an example of DML.I am looking for you to post T-SQL that I can cut and past into my database and actually create the table and index. You see, if I have to type out the create table statement myself, I become less interested in working on your issue . . .Now, I'm not in indexing expert, but . . . the select * is going to ultimately require either a scan of your clustered index (sounds to me like you don't have one) or table scan of a book mark lookup from you non-clustered index. However, I believe somewhere between your ORs and the like with wildcards, a non-clustered index can't/won't be used across all three columns because it would require a scan of the index three times for the three possible conditions. Each scan leading to some sort of clustered index lookup . . . I optimizer probably say, it's rather just scan the heap once . . .EDIT: ok, I think I was wrong about the three scans. Take a look at the query plans generated by these queries...use pubsselect * from authors where au_lname = 'white' or au_fname = 'dean'select * from authors (index=aunmind) where au_lname = 'white' or au_fname = 'dean'select * from authors where au_lname like 'white'select * from authors where au_lname like '%white' <O>Edited by - Page47 on 05/24/2002 10:30:42 |
|
|
|
nricardo
Starting Member
17 Posts |
Posted - 2002-05-24 : 14:01:10
|
SQL Server can not use an index on a column if you have a wildcard at the beginning of the search string. Although Artname is the first column in your index, it simply cannot be used. SQL Server must do a table scan in this case. You may consider placing an index on Hersteller, despending on its selectivity (how many times it equals 'UserSpecific' vs total rows).quote:
You're right...Here's some more information:table name: act_preisemy table structure:1 Artnr float 8 10 Artname nvarchar 255 10 Hersteller nvarchar 255 10 Hersartnr nvarchar 255 10 Preis nvarchar 255 10 Waehrung nvarchar 255 10 Verfuegbarkeit nvarchar 255 10 Produkthierarchie nvarchar 255 10 Produktfamilie nvarchar 255 10 Produktgruppe nvarchar 255 1Server is Win2000Server with MSSQL 2000i created a non clustered index on 'Artname', 'Hersteller' and 'Hersartnr'.my query:SELECT * FROM act_preise where Artname LIKE '%UserSpecific%' OR Hersteller='UserSpecific' OR Hersartnr LIKE '%UserSpecific%'Hersteller don't need a LIKE statement, because user could choose it from a drow-down menu.please be clement with me i'm quite beginning my work with that server (3 months i think).What do you mean with DDL?greetingsEdited by - mortalan on 05/24/2002 10:11:25
|
|
|
|
|
|
|
|
|
How to query an indexed table