performance issue in query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

performance issue in query

V_MAP is an view created using two tables(order contains only 171822 rows/records , ordermap contains only 35 rows/records) item table have 33 records report-sche have 23403 " truckowner – 947 "
now iam using query SELECT V1.ITEMID,v1.upccode,V1.CUSTOMERCORPORATEITEMCODE,ITEM.ITEMDESCRIPTION,V1.CUSTOMERITEMCODE,
SUM(V1.CUSTOMERQUANTITY) POLLING_QUANTITY
FROM V_MAP V1, ITEM ITEM
WHERE V1.ITEMID = ITEM.ITEMID AND V1.ORDERID = 350 AND V1.ADDINFLAG = 0 AND V1.STOREID
IN (SELECT DISTINCT STOREID FROM REPORT_SCHEMATICS WHERE ORDERID = 350 AND TRUCKID IN (
SELECT TRUCKID FROM TRUCKOWNER WHERE CUSTOMERID = 1000001 AND ORDERID = 350))
GROUP BY V1.ITEMID,V1.CUSTOMERCORPORATEITEMCODE,v1.upccode,ITEM.ITEMDESCRIPTION,V1.CUSTOMERITEMCODE,V1.ADDINFLAG
to generate a report it takes 4 minute 15sec to execute the query.
if i use: that is if i replace SELECT TRUCKID FROM TRUCKOWNER WHERE CUSTOMERID = 1000001 AND ORDERID = 350
with exact value it takes only 8sec. but i need the query in the abve format is there ne other way pls help me.
pls give reason why this happen. SELECT V1.ITEMID,v1.upccode,V1.CUSTOMERCORPORATEITEMCODE,ITEM.ITEMDESCRIPTION,V1.CUSTOMERITEMCODE,
SUM(V1.CUSTOMERQUANTITY) POLLING_QUANTITY
FROM V_MAP V1, ITEM ITEM
WHERE V1.ITEMID = ITEM.ITEMID AND V1.ORDERID = 350 AND V1.ADDINFLAG = 0 AND V1.STOREID
IN (SELECT DISTINCT STOREID FROM REPORT_SCHEMATICS WHERE ORDERID = 350 AND TRUCKID IN (
4,3,2,1))
GROUP BY V1.ITEMID,V1.CUSTOMERCORPORATEITEMCODE,v1.upccode,ITEM.ITEMDESCRIPTION,V1.CUSTOMERITEMCODE,V1.ADDINFLAG
Couple of things: First, how long does this query take on its own: SELECT TRUCKID FROM TRUCKOWNER WHERE CUSTOMERID = 1000001 AND ORDERID = 350 Second, have a look at the execution plan side by side with the two different queries you have – i.e. the one with the statement above included and the one without it. What are the differences? Does one do a scan and one (the fast one) a seek? It sounds to me like the query optimiser is trying to guess the best plan to use but gets it wrong when you have the query above included and gets it right when the exact list is included. If this is the case, you can give the query a hint to tell it which index to use and you should have it down to 8s. Dave.
]]>