question

cornpoppy avatar image
cornpoppy asked

Any ideas on why Query-two would run so slow ?

1-Query one--index seek SELECT P.Date_Send_Ins,C.* FROM DATABASE1..TABLE1 P WITH (FORCESEEK) JOIN DATABASE2..TABLE2 C ON P.COLUMN1 = C.COLUMN1 AND P.COLUMN2 = C.COLUMN2 WHERE LEFT(C.COLUMN3 ,7) LIKE 'YYYY/MM' and LEFT(P.COLUMN3 ,2) = 'YS' 2-Query two SELECT P.Date_Send_Ins,C.* FROM DATABASE1..TABLE1 P JOIN DATABASE2..TABLE2 C ON P.COLUMN1 = C.COLUMN1 AND P.COLUMN2 = C.COLUMN2 WHERE LEFT(C.COLUMN3 ,7) LIKE 'YYYY/MM' and LEFT(P.COLUMN3 ,2) = 'YS' 3-Query three--index seek SELECT P.Date_Send_Ins,C.* FROM DATABASE1..TABLE1 P JOIN DATABASE2..TABLE2 C ON P.COLUMN1 = C.COLUMN1 AND P.COLUMN2 = C.COLUMN2 WHERE LEFT(C.COLUMN3 ,7) LIKE 'YYYY/MM' and P.Date_Send_Ins LIKE 'YS%' Query one and three are fast in sql server but query two is too slow and results do not appear . In another server all of them are fast ,i run sp_updatestats, but it's not better . How to solve this problem?
dbaquery-resultsquery-plandba-developerquery-tuning
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
cornpoppy avatar image
cornpoppy answered
Bad index was slow down this query. The problem was solved when i disabled index.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Would be interesting to see the definition of this bad index.
1 Like 1 ·
Bad index, or bad statistics on the index?
0 Likes 0 ·
Bad index
0 Likes 0 ·
The index that contained all the columns
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.