March 10, 2009 at 5:47 am
I made an index on a table named stock_items that has a field named batch_file_id , this is the new index code :
CREATE NONCLUSTERED INDEX [IX_BatchFileID] ON [dbo].[stock_items]
(
[BATCH_FILE_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
but i cant find any improvement in response time while executing this query :
SELECT * from stock_items where BATCH_FILE_ID=@batchFileID
anyone can help me plz and tell me why this index dont work properly ?
thx in advance .
March 10, 2009 at 5:56 am
I'm assuming that you getting an Index seek, with a Key look up or you got a table scan.
The reason for this is because you are using a select *
The index can be used for the where clause but it doesn't contain all the data that you wish to return, so the optimizer is either going to decide to simply scan the whole table or it is going to use the index but then have to look up the other data seperatly, and this is expensive.
Try to only return the data you need!!!
Does this help?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 10, 2009 at 6:13 am
i tried what u say but still no improvement in query response time 😉 .
March 10, 2009 at 6:23 am
By simply changing the query you may not get an improvement, you would need to change the index again.
If you Could you send me the actual query (e.g what changes you made to it), then I could suggest an index for you.
Also could you save the execution plan as a .sqlPlan and attach it here please for me to look at.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 3:08 am
has this problem been solved?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 12, 2009 at 1:27 pm
yea its solved , thx very much for ur aid .
the problem was that the percentage of data selected is to big, so indexing does not improve the query response time .
March 13, 2009 at 3:12 am
are you sure?
I still think it was because you used a select *
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 13, 2009 at 5:15 am
ahmad.kheir (3/12/2009)
yea its solved , thx very much for ur aid .the problem was that the percentage of data selected is to big, so indexing does not improve the query response time .
How many records does the table have?
How many records matched for the given id?
karthik
March 13, 2009 at 5:16 am
And...
is there any other indexes created to the table?
karthik
March 13, 2009 at 4:37 pm
well , the entire table rows count was approximatively 1,050,000 records and the selected count was 1,000,000 records and no other indexes exist .
I tried first to select all columns then I tried to select only one column ( the key ) so response time was improved for sure but still the same with and without indexing, and i found the difference when the total rows count was increased to 5,000,000 records.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply