June 2, 2005 at 3:51 pm
I have something very simple but causes too much trouble:
Select Value from tbl_value where id in(b1,b2,b3,b4,b5);
And it causes too many physical reads - it's true there are a lot of data, but it still need to process from the buffer cash... Even when executed 100 time all the time reads from the disk.
How can I tune it up? What params I could look at?
Thanks a lot.
June 2, 2005 at 4:46 pm
Is there an index on tbl_value?
If there's no index, and it's doing a table scan, the cache is probably being re-used for pages that don't fit the criteria, so it can't satisfy the query from cache on the second execution.
Are the values contiguous, so that you could say
where tbl_value >= 'b1' and tbl_value <= 'b5' ?
June 2, 2005 at 4:59 pm
There is an index+PK on this field, of course.
The values are selected by the previous query and could vary every time.
The second similar problem is:
select value from tbl_value where test_id in(c1,c2,c,c3); Test_id is FK and PK in table_test - there are a bunch of physical reads as well.
Thanks,
June 2, 2005 at 5:02 pm
Ok, so much for guessing....
Could you post the query execution plan? That should point out the rough spot or spots.
June 3, 2005 at 12:57 pm
declare @var table (var1 char(2) Primary Key)
Insert @var values ('b1')
Insert @var values ('b2')
Insert @var values ('b3')
Insert @var values ('b4')
Insert @var values ('b5')
Select Value
from @var v
JOIN tbl_value t on v.var1 = t.id
Signature is NULL
June 3, 2005 at 1:53 pm
There's nothing wrong with the execution plan. it shows that the indexes are in use and the cost is pretty good.
But when you monitor with the monitoring toll - even in Perf Monitor, there's a huge physical reads - SQL Profiler shows exactly the same for this query called by the program.
So I was thinking that may I need to change some physical parameters like raid, partititioning on datafile, etc...
Thanks,
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply