September 26, 2003 at 4:06 am
Hi Experts,
Sql server 2000 sp3a on Win 2k
I have two Master-detail tables named
Header (10000 rows) and Detail (1,20000 rows)
Having clustered index on their Primary key named OID,
Also key Detail.HeaderOID is having non clustered index on it.
To optimize query (A) below i change it to (B) since I am not selecting any row from Header table so I can remove it from the query. But while running query (B) my response time increases to 30 times. I can not understand if I removed a table from the query it should take less but its taking drastically high time ?
(A)
SELECT *
FROM Header ,Detail
WHERE Header.OID = ‘143’
Header.OID = Detail.HeaderOID
22 rows in 100 ms
(B)
SELECT *
FROM Detail
WHERE Detail.HeaderOID = ‘143’
Same 22 rows in 3000 ms
I reindex all tables in the db but no joy. I tried the same query in another db there it is fine i.e. query (B) is taking some where 90 ms. Can anybody shed light on this. Also I have already setted db parameter Auto create statistics and auto update statistics to on in both he databases.
TIA,
Sheilesh
September 26, 2003 at 4:49 am
I had not mentioned for simplycity in above query (A) and (B) a view is alslo joined with proper joins and indexes on view base tables. While I removed view from above queries It tooks almost same time for (A) and (B) case.
So join with view is a probem in sql server ?
Pl. let me any issue involved with view joined in a query performance.
In another db where it is give good response time there table rec volumes are very less.
TIA,
Sheilesh
September 26, 2003 at 5:49 am
Non intuitive I'd agree. Must be generating a bad query plan for the single table case. You might have to use an index hint to get it to match the results of the first query.
I haven't seen a definitive answer on views. Sometimes they work fine, sometimes they seem to outsmart the optimizer.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply